5 mins of Postgres!
I still do them occasionally, but its hard to find the time currently. Glad to hear you liked them :)
1,954 karma · joined October 30, 2007
Founder of pganalyze - Postgres performance at any scale.
Feel free to drop me an email: lukas@fittl.com
[ my public key: https://keybase.io/lukasfittl; my proof: https://keybase.io/lukasfittl/sigs/rqDeEWdp4ktJubli4QY0dNaUJ6K-pdpDTHCFOi9v7WU ]
I still do them occasionally, but its hard to find the time currently. Glad to hear you liked them :)
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
Presumably so folks can be sure they're not accidentally pulling in proprietary code.
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.
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).
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.
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.
[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 :)
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...
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...
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).
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...
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.
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.
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.
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.
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
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...
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...
---
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...
* 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.
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.