Features I wish PostgreSQL had as a developer • Hacker News Board

Re: Features I wish PostgreSQL had as a developer

#61

Post by jauntywundrkind » Fri, Apr 05, 2024, 5:06 PM UTC

lebski88 wrote: post #55

jitl wrote: post #48

For me, it’d be more control over the query planner, ideally down to the level of submitting my own physical query plan. I was looking at a query yesterday that joined 3 tables and we were unable to convince Postgres to use the optimal plan, which was to scan an index on Table1 that matched its ORDER BY order, and filter by joining against the other tables. With a CTE for Table1 that had a limit on it, Postgres would…

Yes this is completely my experience too. I spent most of yesterday battling with the query planner. Sometimes you can't express with table statistics something that you know to be true for the exact query that you're making. There are a lot of arguments about query hints etc becoming stale and the performance changing as the table grows but I'm less worried about that - it would be a gradual degradation of performan…

Very memorable post about this happening at Bluesky, about being utterly unable to trust Postgres to keep doing what it's doing.

> Bluesky scaling lesson: Postgres is not a great choice if your data can be irregular.

> The query planner will switch over to a new plan that consumes 100% CPU in the middle of the night whenever its table stats flip the heuristics the wrong way.

> Postgres badly needs query hints like MySQL has!

https://bsky.app/profile/jacob.gold/post/3kn4v67wyjt2l

Re: Features I wish PostgreSQL had as a developer

#62

Post by rand_r » Fri, Apr 05, 2024, 6:33 PM UTC

throwaway30713 wrote: post #35

rand_r wrote: post #27

Earlier quoted context omitted.

That is the way, but the UX is pretty ass because the lock ID is a 64 bit number instead of a string. How the heck are you supposed to keep track of what lock ID you should be checking in a given situation across multiple client apps?

You get the same problem for strings. How do you know that you should lock "user_update" and not "update_user" for example? And how do you avoid name collision when client A wants to check for a lock that is used by client B for other purposes? The solution to both cases is to define them as either static constants or use an Enum. Then you would not care if the end result is a string or a number. At my work place we…

Good point, that’s a better approach. I guess for a multi-repo situation at work, you would need to create a base project like “postgres-lock-ids” so you can synchronize the lock across everything.

Re: Features I wish PostgreSQL had as a developer

#63

Post by locopati » Fri, Apr 05, 2024, 11:47 PM UTC

LogicalBorg wrote: post #40

> To prevent deleting data by mistake, we invent soft-delete pattern by having a is_deleted column. However, this brings extra complexities around foreign key, unique index enforcement. Ideally, PostgreSQL could allow users to configure an archived table. The removed data is moved to the archived table first and purged after a configured retention period. This simplifies application logic and data compliance work. Yo…

if you're using an ORM that has an updated_at field, you can keep the boolean and rely on the updated date.

Re: Features I wish PostgreSQL had as a developer

#64

Post by asah » Sun, Apr 07, 2024, 1:57 PM UTC

murkt wrote: post #33

asah wrote: post #4

> Archived Table > To prevent deleting data by mistake, we invent soft-delete pattern by having a is_deleted column. However, this brings extra complexities around foreign key, unique index enforcement. Ideally, PostgreSQL could allow users to configure an archived table. The removed data is moved to the archived table first and purged after a configured retention period. This simplifies application logic and data co…

> Partitioned tables works pretty well for this. Pretty interesting idea. Do you have any stories or experience to share when going with this approach?

um, I've done it in production for years?

Re: Features I wish PostgreSQL had as a developer

#65

Post by Izkata » Mon, Apr 08, 2024, 2:23 PM UTC

jitl wrote: post #48

For me, it’d be more control over the query planner, ideally down to the level of submitting my own physical query plan. I was looking at a query yesterday that joined 3 tables and we were unable to convince Postgres to use the optimal plan, which was to scan an index on Table1 that matched its ORDER BY order, and filter by joining against the other tables. With a CTE for Table1 that had a limit on it, Postgres would…

A few days late here (joys of opening multiple tabs then only getting to them when bored), here's hoping you look back and see this:

The difference between using a LIMIT and not hints this is happening because of a low correlation statistic between that index and the order on disk. Postgres is avoiding random-access lookups in what looks to you like the optimal plan, because this blows away the disk cache (paging cache) and would result in a much slower overall time than you think it will, given other configuration values.

The CLUSTER command will reorder the table data to match a chosen index, after which postgres will use that index with an index scan the way you want it to, even if you retrieved the whole table and have a slow disk, because now with a high correlation statistic the query planner knows it'll be working with the disk cache instead of against it.

If you're on an SSD or are otherwise sure it can all be held in memory, check out the "random_page_cost" setting on https://www.postgresql.org/docs/current/runtime-config-query... which may allow the index scan without having to run CLUSTER.