3 ms·

I confess that 'type' sounds to me like the sort of thing that's going to appear in a WHERE clause often enough that I might as well make an enum for it and mat

by mst 5y ago

I confess that 'type' sounds to me like the sort of thing that's going to appear in a WHERE clause often enough that I might as well make an enum for it and materialise that out on insert whether by trigger or otherwise. Neat trick for the meantime though, I'm sure making a schema change like the one I'm thinking of would be a colossal pain in the buttocks if decided late in the run up to shipping a feature.

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 ago
  2c: 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 ago
    Yeah. 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.
kmdupree 5y ago
You're exactly right that our schema could be better and that it's non-trivial to execute on a schema change, especially because we actually run a distributed Postgres cluster via Citus AND we use a special sharding method that we manage manually. We actually just started working towards how we might do a schema change for the 1+ million shards in our cluster. Hopefully, we'll be able to write up some learnings on the schema change after its done. :)
- mst 5y ago
  Right, your current solution is what I'd classify as an "expedient field hack" if I were the one implementing it, and I do mean that positively :D I look forwards to comparing notes when that post comes out.

by mst 5y ago

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 ago
2c: 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 ago
  Yeah. 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.

kmdupree 5y ago

You're exactly right that our schema could be better and that it's non-trivial to execute on a schema change, especially because we actually run a distributed Postgres cluster via Citus AND we use a special sharding method that we manage manually. We actually just started working towards how we might do a schema change for the 1+ million shards in our cluster. Hopefully, we'll be able to write up some learnings on the schema change after its done. :)

mst 5y ago
Right, your current solution is what I'd classify as an "expedient field hack" if I were the one implementing it, and I do mean that positively :D I look forwards to comparing notes when that post comes out.