I haven't used these extensions, but I like the direction of being able to extend Postgres by tailing its own internals (pg_squeeze approach) than triggers (pg_repack).
We (the SpiceDB developers) took a similar approach to solve a different problem a few years ago: implementing strict serializability in Postgres[0]. By hooking into the transactions layer themselves (pg_xact), we could layer on our own MVCC reusing the real Postgres transactions to compute visibility and total ordering. When you can just tail the Postgres internals, you don't even need to implement a Postgres extension, which can keep it portable across locked-down, managed Postgres offerings.
Do folks know of other clever implementations of behavior by "spying" on the Postgres internals?
I used to have to keep an eye on tuple table bloat for awhile on big PG databases, but with recent versions of Postgres the auto-vacuuming has mostly kept it in check for all but the most gargantuan, busy tables. I pretty much have stopped caring (and I cared a lot less when SSDs became the default as the fragmentation penalty that a full repack would fix wasn't as bad as it was during the spinning metal days).
Index bloat has been another story, though. Pruning b-trees is expensive and I've mostly just been scheduling reindexes (which have been able to be done concurrently for awhile) instead.
Isn't losing MVCC consistency a pretty big drawback? Am I misinterpreting or does that mean occasionally a query might just return nothing for possibly difficult to diagnose reasons?
They added plan hinting, but sadly not via comments. To use hinting an app needs to issue a `SET` command, which adds round trip latency for each query. Need to use quite unergonomic query batching/pipelining to negate that latency.
plan hinting is a crutch. a useful one to have, and lifesaver when you need it, but a crutch nonetheless.
what i wish postgres would do is to replan when it wildly mis-estimates row counts in deeply nested queries. just because you decided on a linear scan join when you thought that sub-query had 4 rows, doesn’t mean you need to commit to it when that sub-query turns out to actually have 4 million rows.
if you need query plan hints, it’s most likely because some aspect of postgres’ statistics about your tables is very out of line. and, given the very nature of stats collection, i.e., it being a lossy process, there are many distributions of data that it simply cannot detect & accurately account for.
> To use hinting an app needs to issue a `SET` command, which adds round trip latency for each query
You can send the SET and the actual SQL command as a single String (delimited by ; obviously), so there is no additional round trip required.
Additionally: there is the possibility to save such a plan advice to be applied without issuing a "SET pg_plan_advice.advice" before running the statement. So this can also be applied to queries that can't be changed in the source code. This is provided by the extension pg_stash_advice
But I think the approach to decouple the actual "hint" from the query still makes more sense, than requiring a change to the original query to include it into the comments. I don't know if something like pg_stash_advice would be possible without that approach.
They are orthogonal. You can still do pg_stash_advice based on a query id AND have plan hint embedded in the query for cases where stashed hint is not set.
They solve different problems:
- SET syntax, or query comment like in third-party pg_hint_plan extension, are for app developers to embed desired hint into the code
- pg_stash_advice, or 'hint_plan.hints' table from pg_hint_plan, is a tool to get out of a pickle when query suddenly starts to misbehave and rebuilding and redeploying app with a new hint is not an option or will take too much time.
It does, but then we are back to my original complaint: pipelining (or query batching) has quite less ergonomic API, at least in PostgreSQL drivers I worked with, comparing to a simple query execution.
And maybe it is time for the postgres drivers to expose better support for pipelining.
I hacked around our query builder to add support for it, and I wish I didn't have to. It feels like a problem that should have been solved 20 or 40 years ago.
> They added plan hinting, but sadly not via comments.
Comments are comments, not syntax. Plan hints have syntax. They are different concepts, and mixing syntax inside comments is a great way to break things.
i can’t remember which front end it was, but one of the places i worked with was having all the comments stripped out of the queries before they were sent over the wire.
it was a nice surprise to find out that hinting had just.. never worked.
> You need 2x the space for the table and indexes you are repacking, since for a period, you will have two copies of it.
this feels like a foot gun. While other storage formats (eg LSMs) have lots of write amplification, it's done iteratively so you don't have massive storage and IO spikes when performing maintenance.
While this is _definitely_ better, I think the long-term solution is to replace the storage engine with something more modern.
Trying to recoup space at a slow period, I remember using Repack on New Years Eve on my local computer for a multi TB production DB and waiting hours and hours hoping my connection doesn’t drop. When it dropped, I had to restart all over again.
I thought it would be a simple process but it ended up taking many hours and a few retries. Holy hell it was a miserable experience and it ruined my New Years Eve.
I feel like that whole experience deserves a blog post.
pg_repack, pg_squeeze, pg_reorg, all felt like it was "use as directed" and if you misuse them we're sorry if we corrupt data - that's a little extreme but they were still very advanced tools.
Having this in core and learning from all the previous Postgres paths is the way of Postgres. I'm with you couldn't be more excited to have this in core, to me it was always the flagship feature and I'm just glad it stayed in 19 vs. a revert compared to so many other flagship features.
Before long-running commands, open a screen [1] session. That way, if you get disconnected, the job keeps running, and you can reconnect to the screen session afterwards.
The man page is quite long, use "screen -R <appropriate name" to create the session or reconnect to it, and (IIRC) CTRL-A D to exit screen without closing the session.
You don't perform resource-intensive operations on a big database over unreliable remote connections. Find another way to be closer to the server, ideally on the same machine, or the same LAN at least.
I would have loved REPACK CONCURRENTLY when we were generating millions of dead tuples an hour on our trading database …
Sadly it doesn’t support partitions yet, which is typical when reaching sufficient scale that bloat management really matters. People often mention disk space when talking about dead tuples, but the real killer is what’s hidden under - bloat in the ctid & btree leaf linked lists make every lookup feel like table scans…!
Thankfully HOT (Heap-only Tuples) has saved us since. Virtually no dead tulles generated, and they get otherwise collected during regular ops like SELECT, amortizing cost over time, and preventing dangerous build up.
We (the SpiceDB developers) took a similar approach to solve a different problem a few years ago: implementing strict serializability in Postgres[0]. By hooking into the transactions layer themselves (pg_xact), we could layer on our own MVCC reusing the real Postgres transactions to compute visibility and total ordering. When you can just tail the Postgres internals, you don't even need to implement a Postgres extension, which can keep it portable across locked-down, managed Postgres offerings.
Do folks know of other clever implementations of behavior by "spying" on the Postgres internals?
[0]: https://m.youtube.com/watch?v=_f6fHy4DWWM
Index bloat has been another story, though. Pruning b-trees is expensive and I've mostly just been scheduling reindexes (which have been able to be done concurrently for awhile) instead.
USING INDEX is interesting, though...
what i wish postgres would do is to replan when it wildly mis-estimates row counts in deeply nested queries. just because you decided on a linear scan join when you thought that sub-query had 4 rows, doesn’t mean you need to commit to it when that sub-query turns out to actually have 4 million rows.
if you need query plan hints, it’s most likely because some aspect of postgres’ statistics about your tables is very out of line. and, given the very nature of stats collection, i.e., it being a lossy process, there are many distributions of data that it simply cannot detect & accurately account for.
You can send the SET and the actual SQL command as a single String (delimited by ; obviously), so there is no additional round trip required.
Additionally: there is the possibility to save such a plan advice to be applied without issuing a "SET pg_plan_advice.advice" before running the statement. So this can also be applied to queries that can't be changed in the source code. This is provided by the extension pg_stash_advice
This works for simple protocol only, with drawbacks like no parameter binding or re-planning of every query even if repeated.
But I think the approach to decouple the actual "hint" from the query still makes more sense, than requiring a change to the original query to include it into the comments. I don't know if something like pg_stash_advice would be possible without that approach.
They solve different problems:
- SET syntax, or query comment like in third-party pg_hint_plan extension, are for app developers to embed desired hint into the code
- pg_stash_advice, or 'hint_plan.hints' table from pg_hint_plan, is a tool to get out of a pickle when query suddenly starts to misbehave and rebuilding and redeploying app with a new hint is not an option or will take too much time.
I hacked around our query builder to add support for it, and I wish I didn't have to. It feels like a problem that should have been solved 20 or 40 years ago.
Comments are comments, not syntax. Plan hints have syntax. They are different concepts, and mixing syntax inside comments is a great way to break things.
it was a nice surprise to find out that hinting had just.. never worked.
this feels like a foot gun. While other storage formats (eg LSMs) have lots of write amplification, it's done iteratively so you don't have massive storage and IO spikes when performing maintenance.
While this is _definitely_ better, I think the long-term solution is to replace the storage engine with something more modern.
I thought it would be a simple process but it ended up taking many hours and a few retries. Holy hell it was a miserable experience and it ruined my New Years Eve.
I’m so happy this is now built in.
pg_repack, pg_squeeze, pg_reorg, all felt like it was "use as directed" and if you misuse them we're sorry if we corrupt data - that's a little extreme but they were still very advanced tools.
Having this in core and learning from all the previous Postgres paths is the way of Postgres. I'm with you couldn't be more excited to have this in core, to me it was always the flagship feature and I'm just glad it stayed in 19 vs. a revert compared to so many other flagship features.
The man page is quite long, use "screen -R <appropriate name" to create the session or reconnect to it, and (IIRC) CTRL-A D to exit screen without closing the session.
[1] https://man7.org/linux/man-pages/man1/screen.1.html
You don't perform resource-intensive operations on a big database over unreliable remote connections. Find another way to be closer to the server, ideally on the same machine, or the same LAN at least.
Sadly it doesn’t support partitions yet, which is typical when reaching sufficient scale that bloat management really matters. People often mention disk space when talking about dead tuples, but the real killer is what’s hidden under - bloat in the ctid & btree leaf linked lists make every lookup feel like table scans…!
Thankfully HOT (Heap-only Tuples) has saved us since. Virtually no dead tulles generated, and they get otherwise collected during regular ops like SELECT, amortizing cost over time, and preventing dangerous build up.