Comment by snorkel | Hacker News Reader

Comment by snorkel | Hacker News Reader

Parent Full thread

Consider a common use case of wanting to mix SQL structured data and JSON structured data:

You want a logging service that records events in an SQL table. Each event has base attributes common to all events such as date, event name, component name, user id ... these parameters become columns in the SQL table. Some events can also include arbitrary custom data particular to that kind of event, and you want that data to also be in the database but you don't want to play whack-a-mole with the table schema to make sure every possible custom parameter has a column in the SQL table, in this case just stash the custom data as JSON, and hopefully the database server offers JSON comprehensions that let you access any fields within the JSON data via SQL.

I think the question is how does that compare to the usual trick of adding a key/value/attribute table?

  create table misc_attrs (maintable_id foreign key, name text, value text)

When reading data back, it's more efficient to query a single table than to have to join against an extra attributes table

See https://news.ycombinator.com/item?id=6574269 for anecdotal evidence.

there are so many variables in that anecdote that there is nothing that can be gleaned from it.

Alright, these were approximately the fields that were Postgre-fied.

CREATE TABLE event ( "references" text[], (was 2 tables, references and reference_on_event) "entities" text[], (ditto) "actors" text[], (ditto) "targets" text[], (ditto) "payload" json, (EAV) )

Gin index on all the text arrays. There are about 150k events saved per day and PostgreSQL is running on the same kinds of hardware as SQL Server was. The application is quite read heavy.

There are times actually I find it helpful to break 1NF in PostgreSQL. This is certainly not free but it really helps some sorts of queries, and it avoids having to do a sort of pseudo-EAV to get certain things working.

Reply on news.ycombinator.com