I don't think Tom's perspective has necessarily changed (and there is certainly concern from others that this could cause less reports on planner bugs), but Tom is pretty good about not standing in the way of others (i.e. Robert Haas in this case) trying to make things work, and being open to new perspectives.
I do know that one of the important criteria for getting this in was that a bad advice can't cause the planner to fail, and that's something that was explicitly included in the design of pg_plan_advice.
Also, I'll add my perspective: I think "EXPLAIN (PLAN_ADVICE)" is a key piece to making this a plan stability feature, not (just) a hinting feature. The extensibility/framework pg_plan_advice adds is a foundation, that over time will over time address the age-old "Postgres doesn't have hints" problem, even if the initial release doesn't check all the boxes yet, e.g. no way to use advice for adjusting row/join estimates.
To give an example on extensibility: Some people that I've spoken to are asking "but why is it not a comment-style hint". There are reasons why Postgres didn't go that way for this release (comment parsing in core is non-existent today, and comments don't work correctly e.g. for functions), but its easy to write an extension that sets up an advisor hook to parse comments: https://github.com/pganalyze/pg_advice_comment
That said, this post predates that LinkedIn post, so I don't think it was intended to compete with the original author's own efforts, but rather to provide continuity for PGX's customers.
And, its noted in the video/transcript, but for clarity: This is talking about new extensibility in Postgres 19 that makes it easier to do Postgres planner hints / plan management extensions.
The patch that was committed is part of a larger proposal (pg_plan_advice) which, if it ends up being committed, would add a version of planner hints to Postgres itself (in contrib). It remains to be seen where that goes for Postgres 19.
But, I'm hopeful that in 2-3 years from now, we'll see this bear fruition. The recent asynchronous read I/O improvements in Postgres 18 show that Postgres can evolve, one just needs to be patient, potentially help contribute, and find workarounds (connection pooling, in this case).
Since there seems to be some confusion in the comments about why pg_query chose Protobufs in the first place, let me add some context as the original author of pg_query (but not involved with PgDog, though Lev has shared this work by email beforehand).
The initial motivation for developing pg_query was for pganalyze, where we use it to parse queries extracted from Postgres, to find the referenced tables, and these days also rewrite and format queries. That use case runs in the background, and as such is much less performance critical.
pg_query actually initially used a JSON format for the parse output (AST), but we changed that to Protobuf a few major releases ago, because Protobuf makes it easy to have typed bindings in the different languages we support (Ruby, Go, Rust, Python, etc). Alternatives (e.g. using FFI directly) make sense for Rust, but would require a lot of maintained glue code for other languages.
All that said, I'm supportive of Lev's effort here, and we'll add some additional functions (see [0]) in the libpg_query library to make using it directly (i.e. via FFI) easier. But I don't see Protobuf going away, because in non-performance critical cases, it is more ergonomic across the different bindings.
Regarding pgstattuple specifically: If this was a 24/7/365 service and you would be concerned by the I/O impact of loading the full table or index at any time, you could run this on a replica too. For tables there is pgstattuple_approx which is much better at managing its impact, but there is no equivalent for indexes today.
The REINDEX CONCURRENTLY mentioned in OP could also be run at other times of the day - the main issue is again I/O impact (with potentially some locking concerns at the very end of the reindex concurrently to swap out the index).
There are no magic solutions here - other databases have to deal with the same practical limitations, though Postgres sometimes is a bit slow to adopt operational best practices in core (e.g. the mentioned pg_squeeze from OP may finally get an in-core "REPACK CONCURRENTLY" equivalent in Postgres 19, but its been a long time to get there)
1) When do pages get removed? (file on disk gets smaller)
Regular vacuum can truncate the tail of a table if those pages at the end are fully empty. That may or may not happen in a typical workload, and Postgres isn't particular about placing new entries in earlier pages. Otherwise you do need a VACUUM FULL/pg_squeeze.
2) Does a regular VACUUM rearrange a single page when it works on it? (i.e. remove empty pockets of data within an 8kb page, which I think the author calls compacting)
I think the answer to that is yes, e.g. when looking at the Postgres docs on page layout [0] the following sentence stands out: "Because an item identifier is never moved until it is freed, its index can be used on a long-term basis to reference an item, even when the item itself is moved around on the page to compact free space". That means things like HOT pruning can occur without breaking index references (which modify the versions of the tuple on the same page, but keep the item identifier in the same place), but (I think) during VACUUM, even breaking index references is allowed when cleaning up dead item identifiers.
The article has a section where it estimates index bloat based on comparing the number of index reltuples * 40 bytes (?), compared to the size of the file on disk.
This is problematic, first of all because I don't think the math is right (see [0] for a more comprehensive query that takes into account column sizes), and second because it ignores the effects of B-Tree index deduplication in Postgres 13+: [1]
In my experience, fast bloat estimation queries can work okay for table bloat, but for index bloat I'd recommend instead looking at the change in page density over time (i.e. track relpages divided by reltuples), or just go direct to running pgstatindex outside business hours.
At pganalyze, we're redefining how developers optimize one of the world's most popular databases, PostgreSQL. Our software gives companies like Atlassian, Robinhood, and Notion the tools to solve their most complex Postgres performance challenges, ensuring their mission-critical applications run smoothly at scale.
For our Solutions Engineering role you will directly work with our VP of Sales, as well as our Founder & CEO (me!) on strategic deals, and develop the playbook for integrating pganalyze in complex customer environments. You will also actively contribute to both our documentation and the product itself. Our team is cross-functional, and our goal is to improve the pganalyze product where we can, to allow repeated delivery of a great customer experience.
We are looking for candidates with a strong background in working on Linux VMs in the cloud or on-premise, and with Kubernetes or containers. Go experience is a big plus, since our agent (the pganalyze collector) is written in Go, and this role may involve making the collector work in different environments, and improving cloud provider API integrations.
Founder of pganalyze - Postgres performance at any scale.
Feel free to drop me an email: [email protected]
[ my public key: https://keybase.io/lukasfittl; my proof: https://keybase.io/lukasfittl/sigs/rqDeEWdp4ktJubli4QY0dNaUJ6K-pdpDTHCFOi9v7WU ]