4 ms·
I'm a huge, huge fan of JSONB columns in Postgres, but I'd agree here. Anything that is consistent and important enough should be pulled out of JSON and put int
by fabian2k 5y ago
I'm a huge, huge fan of JSONB columns in Postgres, but I'd agree here. Anything that is consistent and important enough should be pulled out of JSON and put into a real column. Postgres has some impressive functionality to query JSON, but it's not the same as with real columns and you run into performance issues much, much quicker.
- asah 5y ago2c: in most cases, a Postgres index is (performance wise) identical to a "real column" just without the name, and indeed you have more control over the data structure for an index, and indexes can include lossy compression (e.g. partial indexes) unlike a real column... (obviously, the challenge is getting pg to use your index for a given query... for that, I like to use VIEWs to help query authors write queries that use the indexes)
- saurik 5y agoYeah. This wasn't the case a while back--PostgreSQL was missing support for what I think people call covered indexes, and so would only use the index to filter a set of candidate rows and then still had to go digging around in the real tuple to verify it matched--but I am pretty sure they changed that a while back.