lfittl
Joined 19 years ago
Karma
1,954
Submissions
213
Member since
Oct 30
Recent submissions
Comment · 2 months ago
5 mins of Postgres!
I still do them occasionally, but its hard to find the time currently. Glad to hear you liked them :)
Comment · 2 months ago
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
Comment · 3 months ago
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.
Comment · 4 months ago
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.
Comment · 4 months ago
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
Comment · 6 months ago
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.
Comment · 7 months ago
Its worth reading this follow-up LKML post by Andres Freund (who works on Postgres): https://lore.kernel.org/lkml/yr3inlzesdb45n6i6lpbimwr7b25kqk...
Comment · 8 months ago
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.
Comment · 8 months ago
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).
Comment · 9 months ago
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.
Comment · 10 months ago
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)
Comment · 10 months ago
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 :)
Comment · 10 months ago
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...
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 ]