News for hackers. Stuff that matters.
Re:Working around a case where the Postgres planner is “not ... by mst on Monday August 02, 2021 @03:10PM (#28041269)
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.

Re:Working around a case where the Postgres planner is “not ... by fabian2k on Monday August 02, 2021 @03:17PM (#28041339)
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.
Re:Working around a case where the Postgres planner is “not ... by asah on Monday August 02, 2021 @04:28PM (#28042195)
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)

Re:Working around a case where the Postgres planner is “not ... by saurik on Tuesday August 03, 2021 @12:12AM (#28045641)
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.
Re:Working around a case where the Postgres planner is “not ... by kmdupree on Monday August 02, 2021 @03:20PM (#28041369)
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. :)

Re:Working around a case where the Postgres planner is “not ... by mst on Tuesday August 03, 2021 @04:23PM (#28053686)
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.