lfittl

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 :)

(OP here, happy to see this on HN!)

If you're interested in this topic, Robert Haas (the author/committer of the feature) also wrote two posts after I published this, which talk more about incremental backup:

http://rhaas.blogspot.com/2024/01/incremental-backup-what-to...

http://rhaas.blogspot.com/2024/01/incremental-backups-evergr...

For additional context, here is a copy of the email we (as a current Sentry customer) received earlier today:

---

As you may have heard, we are beginning to make investments in machine learning ([0]) to improve our product experience along with launching an EU data region ([1]) for Sentry.

To support these new product offerings, we need to make the following changes to our Terms of Service (TOS):

- Update Sentry's data usage rights to use the error and other data sent by our customers to Sentry for AI model training and other product development efforts (but only in an aggregate and anonymous manner for any external-facing uses); and

- Update our existing data locality language to align with our making new data regions available.

We are also making a few administrative changes to our TOS for further clarity. Please check out our Change Log ([2]) and the full text of our updated TOS ([3]) for more details.

The updated TOS goes into effect on February 3, 2024 (the “TOS Effective Date”). The updated TOS applies to [removed customer name] as follows:

- If you have an existing free subscription to use our products and services (“Service”), your continued use or access of our Service on or after the TOS Effective Date will be deemed to be your assent to the updated TOS; or

- If you have an existing paid subscription to use the Service, you will stay on the existing TOS through the end of your current subscription period. The updated TOS will apply to you beginning on your next renewal on or after the TOS Effective Date.

- If you do not want to be governed by the updated TOS, be sure to close your account ([4]) before the updated TOS applies to you.

For clarity, if your organization has entered into a separate signed agreement governing use of the Service, then that agreement (not the updated TOS) will continue to apply to you in accordance with its terms.

[0] https://blog.sentry.io/ai-privacy-and-terms-of-service-updat...

[1] https://blog.sentry.io/sentrys-eu-data-region-now-in-early-a...

[2] https://sentry.io/legal/changelog/

[3] https://sentry.io/terms/

[4] https://docs.sentry.io/product/accounts/user-settings/

Other related projects:

* PGXN (https://pgxn.org/ - the original Postgres community extension repository, though more "old school")

* Trunk by Tembo (https://github.com/tembo-io/trunk - very similar to pgxman, but using the more liberal Postgres BSD-style license)

Additionally its important to mention that many popular extensions are already packaged as part of the Postgres YUM and Apt repositories (https://wiki.postgresql.org/wiki/Apt and https://yum.postgresql.org/).

I would generally recommend using the upstream repository packages directly, since they are tested/updated with each major release, and their maintainers are proactive in reaching out to extension maintainers that need to update their extension due to upstream code changes.

Thanks, thats good feedback! And agreed, two servers is a bit of a gap in our pricing right now. If you're open to paying annual instead of monthly, we have a bit more flexibility since we can set them up as custom contracts (feel free to reach out)

You can find our pricing on the website [0] - generally $100/month per instance (though note our smallest plan is $150/mo). pganalyze Index Advisor, which is the subject of the post is available with all plans.

Generally I (Founder and CEO) feel the pricing is fair for the value provided for production databases, and it allows us to run the business as an independent company without external investors, whilst continuing to invest in product improvements. That said, it may not be a good fit if you have a small production database, or only make database-related changes infrequently.

[0] https://pganalyze.com/pricing

Author here - what we call "pg_plan" internally, is essentially very similar in spirit to what we do with pg_query [0], but the difference is that we pull in a lot more code from Postgres, and have more overrides in places that are not needed for the use case (e.g. MVCC handling, etc).

My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Mainly from there being a lot of entanglement with reading files on disk, memory management, etc - I suspect one of the pushbacks would be it would complicate development work for Postgres itself, to the point its not worth the benefits.

For the pg_query library on the other hand I have hopes that we can upstream this eventually - there are enough third-party users out there to clearly show the need, and its much more contained (i.e. raw parser + AST structs). Hopefully something we can spend a bit of time on next year.

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

OP here (happy to see this on HN!) - if you're interested in our work in this area, I hosted a webinar earlier this week where we walked through a new constraint programming based system (utilizing CP-SAT) for Postgres index selection that we're starting to roll out in pganalyze:

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

Compared to the initial version, this updated version is both more configurable, as well as has better handling of competing objectives in index selection (write overhead vs query performance).

If you want to give this a try, and have pganalyze set up / want to try it out, feel free to send me a message (email in profile).