lfittl

5 mins of Postgres!

I still do them occasionally, but its hard to find the time currently. Glad to hear you liked them :)

pganalyze | Marketing Manager | REMOTE (US) | Full-time | base $130-160k + annual bonus + equity | https://pganalyze.com

At pganalyze, we build observability and automation tools for optimizing the performance of Postgres databases. Our product helps customers such as Auto Trader, Notion and Robinhood to understand complex Postgres problems and solve performance issues. We are bootstrapped, profitable, with over 500 customers, small and large.

We're looking for the second hire on our marketing team! You might wonder, why post about it on HN? If you are the kind of tech-savvy marketer that has worked on developer tools or infrastructure software before, and reads HN to keep up with the latest, this might be a fit for you. Our marketing stack utilizes Claude Code for some automation (summaries, posting, etc), but we keep the actual writing human authored - if that appeals to you, reach out!

Email me directly (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

All details: https://pganalyze.com/careers

My assumption is that its based on that repo but with the "ee/" folder removed, per https://github.com/PostHog/posthog#open-source-vs-paid

Presumably so folks can be sure they're not accidentally pulling in proprietary code.

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.

Its also worth reading the original post by Robert Haas (the author of pg_plan_advice) on motivation/design: https://rhaas.blogspot.com/2026/03/pgplanadvice-plan-stabili...

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

The original author of pgBackRest posted on LinkedIn today that he will very likely revive the project, given the interest and new sponsorship opportunities: https://www.linkedin.com/feed/update/urn:li:activity:7457070...

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.

Its worth reading this follow-up LKML post by Andres Freund (who works on Postgres): https://lore.kernel.org/lkml/yr3inlzesdb45n6i6lpbimwr7b25kqk...

Thanks for posting! There is a hand-edited transcript here as well, for those who prefer text: https://pganalyze.com/blog/5mins-postgres-19-better-planner-...

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.

Specifically on the cost of forking a process for each connection (vs using threads), there are active efforts to make Postgres multi-threaded.

Since Postgres is a mature project, this is a non-trivial effort. See the Postgres wiki for some context: https://wiki.postgresql.org/wiki/Multithreading

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.

[0]: https://github.com/pganalyze/libpg_query/pull/321

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)

I think there are two aspects to that:

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.

[0]: https://www.postgresql.org/docs/current/storage-page-layout....

Edit: And of course you should trust the parallel comment by anarazel to be the correct answer to this :)

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.

[0]: https://github.com/pgexperts/pgx_scripts/blob/master/bloat/i... [1]: https://www.postgresql.org/docs/current/btree.html#BTREE-DED...

Yep, I find cloud storage performance to be quite frustrating, but its the reality for many production database deployments I've seen.

Its worth noting that even on really fast local NVMe drives the new asynchronous I/O work delivers performance benefits, since its so much more efficient at issuing I/Os and reducing syscall overhead (for io_uring).

Andres Freund (one of the principal authors of the new functionality) did a lot of benchmarking on local NVMe drives during development. Here is one mailinglist thread I could find that shows a 2x and better benefit with the patch set at the time: https://www.postgresql.org/message-id/flat/uvrtrknj4kdytuboi...

It depends on the I/O method - as described in the article, "io_uring" is only available on Linux (and requires building with liburing, as well as io_uring to be enabled in the Kernel), but the default (as of beta1) is actually "worker", which works on any operating system.

The "worker" method uses a dedicated pool of I/O worker processes that run in the background, and whilst not as performant as io_uring in our benchmark, did clearly outperform the "sync" method (which is the same as what Postgres currently has in 17 and older).

Thanks, glad to hear! I like to think that one of the reasons pganalyze is a good product (though there are always parts I'd like to improve, and feedback is always welcome) is because we like to use it ourselves to optimize our own database, where we can :)

Yeah, as one of the main authors of libpg_query, I think the primary things that make this easier is that Postgres has good abstractions internally, and the parser works independently from other parts (e.g. the community discourages adding settings that affect parser behavior).

Over the years we've only had to maintain a small set of patches on top of the Postgres source [0], together with some mocks and our libclang-based extraction logic [1]. Of course it would be nice if Postgres just packaged this directly like it packages the client libraries, but there is non-trivial effort involved to do that. From what I recall, the main issue is that error handling and memory allocations work differently in the client-side libraries (and so that would have to either also be moved out of the backend source, or use some other abstraction).

[0]: https://github.com/pganalyze/libpg_query/tree/17-latest/patc...

[1]: https://github.com/pganalyze/libpg_query/blob/17-latest/scri...

pganalyze | Senior Solutions Engineer | REMOTE (US/Canada) | Full-time | $160-200k + equity | https://pganalyze.com

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.

Details on https://pganalyze.com/careers/senior-solutions-engineer

Interested? Email me (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

pganalyze | Senior Product Engineer | REMOTE (US/Canada) | Full-time | $160-200k + equity | https://pganalyze.com

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 Product Engineering role you will be responsible for driving feature development from concept to writing code to deployment. Sometimes you might have to dig deep into Postgres internals to explain information correctly in a visual way. Recent examples of work done by our product engineers include the launch of our Query Tuning Workbooks feature, our Buffer Cache Monitoring functionality, or our integration with Plan Statistics provided by some Postgres-as-a-service providers.

Details on https://pganalyze.com/careers/senior-product-engineer

Interested? Email me (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

pganalyze | Senior Product Engineer, Senior Solutions Engineer | REMOTE (US/Canada) | Full-time | https://pganalyze.com

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 Product Engineering role you will be responsible for driving feature development from concept to writing code to deployment. Sometimes you might have to dig deep into Postgres internals to explain information correctly in a visual way. Recent examples of work done by our product engineers include the launch of our Query Tuning Workbooks feature ([0]), our Buffer Cache Monitoring functionality, or our integration with Plan Statistics provided by some Postgres-as-a-service providers.

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. 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.

- Senior Product Engineer - Salary: $160-200k + equity - https://pganalyze.com/careers/senior-product-engineer

- Senior Solutions Engineer - Salary: $160-200k + equity - https://pganalyze.com/careers/senior-solutions-engineer

[0]: https://pganalyze.com/blog/introducing-postgres-query-tuning...

Interested? Email me (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

In my understanding it was a timing issue with the UUIDv7 RFC not being finalized before the Postgres 17 feature freeze in early April. Shouldn't be an issue to get this in for Postgres 18, I think.

Commenting on this a bit late, but in case anyone reads this later too:

UUIDv7 support unfortunately didn't make it to Postgres 17, since the RFC wasn't completely finalized yet by the time of feature freeze (April 8), see discussion on pgsql-hackers:

https://www.postgresql.org/message-id/flat/ZhzFQxU0t0xk9mA_%...

So I guess we'll unfortunately have to rely on extensions or client-side generation for the time being, until Postgres 18.

Yep, there is a "link to video" link on the talk page - here is the direct link:

https://www.youtube.com/watch?v=pGN_pORKtSQ

We also did a more recent webinar that has some slight revisions on top of that talk, recording available in our docs: https://pganalyze.com/docs/indexing-engine/cp-model

Thanks for the kind words!

For anyone interested in how pganalyze's approach compares to this extension (and other alternatives like dexter, or using HypoPG directly), I gave a talk with my colleague Philippe last year at PgCon that describes how we use constraint programming and CP-SAT for dealing with the trade-off between index write overhead and read performance improvement, across multiple queries on a table:

https://www.pgcon.org/events/pgcon_2023/schedule/session/422...

Good point - its set up this way since we intentionally don't syndicate the 5mins of Postgres episodes to Planet Postgres, but I could see the benefit of having a complete feed that includes everything, for those subscribing via RSS readers. Will see what I can do :)