21–30 of 115 posts
Can't this be solved by introducing an expression index[1] for "record ->> 'value_2'"?
This would add a specialized index that will be used of all queries that have a filter like "WHERE record ->> 'value_2' = 0 AND ...".
[1] https://www.postgresql.org/docs/current/static/indexes-expre...
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
Taking the well-defined subset of searchable properties and making them columns, as described in the article, is the really the best solution.
> It has no way of knowing, for example, that record ->> 'value_2' = 0 will be true about 50% of the time Can't this be solved by introducing an expression index[1] for "record ->> 'value_2'"? This would add a specialized index that will be used of all queries that have a filter like "WHERE record ->> 'value_2' = 0 AND ...". [1] https://www.postgresql.org/docs/current/static/indexes-expre...
In fact, this might make the query slower. If postgres thinks it is selecting a very small number of rows, it will prefer an index scan of some kind, but a full table scan will be faster if it's retrieving 1/8th of the table (at least, for small rows like these). So, you might get a slower row retrieval and the same explosively slow join.
Earlier quoted context omitted.
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
That's perfectly reasonable. But then you also let them query on those arbitrary custom properties and that's where the performance issues are? If so, that's a fairly hard problem to solve. Taking the well-defined subset of searchable properties and making them columns, as described in the article, is the really the best solution.
user_id | event_id | time | data
We are planning on pulling out the most common properties that we store in the data column, which will give us proper statistics on all of those fields. I am currently experimenting with what new indexing strategies we will be able to use thanks to better statistics.
Earlier quoted context omitted.
I was also wondering about this one. This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing. Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need. I would even go one step further and say: It is a common anti-pattern to introduc…
By the way, another related anti-pattern is the introduction of an additional table that has a 1:1 relationship to the original table. Because the original table was "full" of columns, or something, so a second table had to be opened to contain the remaining columns. This is an anti-pattern because you can't model "1:1" that way. Instead, it will be "1:0..1", and now you have some nasty corner cases when the first ta…
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column." Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
Traditional SQL database have column names that are stored separately from the table data, but in JSON, the keys are in the data.
I've worked with MongoDB systems that held 100 terabytes of data. At that scale, we had to re-write all of the keys so they were only a single character. When JSON is small, it is pleasant to have keys that make sense, such as:
username : mary
but as JSON gets big, this needs to change to:
u : mary
If you have 10 million records, the difference (in this trivial example) is 70 million characters, and if you have 100 different collections with an average of 20 keys each, then you are talking about 140 billion characters saved.
Most of the companies that I know, if they work with JSON document stores at scale (such as MongoDB) eventually take steps to make the keys smaller.
Earlier quoted context omitted.
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
Maybe I'm missing something, but I think of optional columns as nullable (but declared) values. It sounds like you use JSONB to store arbitrarily declared values. If so, then I'm still confused then by how you're able to hoist values from JSONB data to save on perf and space. That implies these values weren't that arbitrary to begin with.
Earlier quoted context omitted.
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
Maybe I'm missing something, but I think of optional columns as nullable (but declared) values. It sounds like you use JSONB to store arbitrarily declared values. If so, then I'm still confused then by how you're able to hoist values from JSONB data to save on perf and space. That implies these values weren't that arbitrary to begin with.
We are currently planning on moving all of the builtin fields into true postgres columns instead of keeping them in jsonb. All of the custom properties will remain in jsonb.