Robert Haas

For future reference, I have found this excellent post about the same topic https://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2-vacuum-v…

If you're looking for a good series of blog posts about xid wraparound in Postgres check out these posts by Josh Berkus: http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.ht… http://www.databasesoup.com/2012/10/freezing-your-tuples-off-part-2.ht… http://www.databasesoup.com/2012/12/freezing-your-tuples-off-part-3.ht… And this more recent one by Robert Haas: https://rhaas.blogspot.com/2018/01/the-state-of-vacuum.html As Josh states at the end of the third post the current best practices for dealing with this are really workarounds and as Robert states it requires monitoring and management. Postgres is an amazing piece of software and managing this is doable but IMHO this is one of Postgres' worst warts. It would be awesome if someone could donate some funding to improve this.

This is from 2012: http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-about-64.html My guess is the 1 socket options scales great. 2 sockets are are less than ideal, and you will not double the 1 socket performance.

Check out the Tech and Company pages as well as the slides from the recent South Bay PostgreSQL Meeetup: http://goo.gl/Mtg2W6 The founders both have strong academic and commercial experience and the advisor is Prof. Joseph Hellerstein (he has both an excellent academic reputation and good experience in industry/tech transfer). Note that TPCH is a "decision support benchmark". There are other technologies for helping postgres with these workloads as well: a column-store approach like https://github.com/citusdata/cstore_fdw, or the recent work on parallel sequential scans in Postgres core (http://rhaas.blogspot.com/2015/03/parallel-sequential-scan-for-postgre… etc.

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-stability-and-use… 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

I 100% agree. This is going to be a bit of a longer comment: 1) You are right that startups want all-in Postgres. To do fast analytics, we need to get columnar storage and vectorized processing. In `pg_analytics`, we implemented a Table Access Method (TAM) to try to accomplish this. TAMs involve changing the storage format of Postgres. When we did this, we discovered there were A LOT of Postgres internals we needed to interface with. We needed to reimplement write-ahead logs (WALs), buffer caching, MVCC, JOINs, and more. Each of these projects is a significant undertaking. Hacking on Postgres is really hard: https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-really-har… As a result, in pg_analytics we cut a lot of corners to ship quickly. The performance was great, but when it came to using it in any real production setting, not having these core features of Postgres meant users couldn't really use pg_analytics, and it would take us months/over a year to build any of this. Even more so, the Table Access Method API in Postgres still has a few limitations. The folks being OrioleDB are probably the most state-of-the-art implementation of a TAM, and they joined Supabase and are pushing for upstream patches to the TAM API to enable their workload. 2) At the same time, while you are right that startups want all-in Postgres, we discovered that larger companies typically have so much analytical data that they're pretty much forced to store it on object storage…

I looked it up because I'm interested in the subject but it seems it's only a proposal right now? At least according to this 5 month old blog post https://rhaas.blogspot.com/2026/03/pgplanadvice-plan-stability-and-use… Edit: Never mind, found it https://www.postgresql.org/docs/19/pgplanadvice.html

Hmm, wonder why they didn't try FoundationDB. Interesting that they had problems with vacuuming. I always thought that part of Postgres was the worst part. I vaguely remember that you needed twice the space to vacuum successfully, which hopefully has changed in later versions. An article about why vacuum is needed in pg (as compared to mysql/innodb). http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2-vacuum-vs…

This was fixed in PG 10 (Oct 2017). See https://rhaas.blogspot.com/2017/09/postgresqls-hash-indexes-are-now-co…

The URL is mangled. Should be https://rhaas.blogspot.com/2021/06/talking-about-postgresql-optimizer-…

I thought 64 cores was a limit coming from Postgres http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-about-64.html

I doubt that's possible, for a number of reasons. 1) Neither company owns any part of the PostgreSQL code / docs. We do have various proprietary tools, of course, but so do other companies. 2) The companies employ quite a few PostgreSQL hackers, but if you look at the development stats (e.g. http://rhaas.blogspot.com/2020/05/who-contributed-to-postgresql.html) you'll see there are many committers and developers in other companies. That being said, I agree with the concerns expressed by Bruce in his blog post. Or more precisely - I do understand why people in the community have those concerns, but I believe (hope) it's not going to harm the community.

There is work to implement a similar approach for PostgreSQL, called zheap. One of the persons behind that is the one who wrote and submitted to HN this article. https://www.postgresql.eu/events/pgconfeu2018/sessions/session/2104/sl… https://github.com/EnterpriseDB/zheap/tree/master/src/backend/access/z… https://www.postgresql.org/message-id/CAA4eK1%2BYtM5vxzSM2NZm%2BpC37MC… https://rhaas.blogspot.com/2018/01/do-or-undo-there-is-no-vacuum.html

I'm not sure why this comment is downvoted. The behavior of PostgreSQL on workloads that require vacuuming of large tables -- and especially the default behavior in that case -- is hugely problematic. There's a major project ongoing to build a new storage engine to address this: http://rhaas.blogspot.com/2018/01/do-or-undo-there-is-no-vacuum.html Like you, I've long wished I could pay a performance penalty on queries to collect garbage immediately rather than batching up this debt to pay all at once some time later. [edited: I'd pay that cost on all queries, not just writes, if the performance were more consistent over time.]

They need to add support for incremental refreshes of materialized views. It is something they have thought about for a while: https://rhaas.blogspot.com/2010/04/materialized-views-in-postgresql.ht… and makes the lists of most requested features: https://rhaas.blogspot.com/2016/01/postgresql-past-present-and-future.…

Related: Why Uber Engineering Switched from Postgres to MySQL https://eng.uber.com/mysql-migration/ Edit: This is above my technical paygrade so I'll let others be the judge of whether these are in fact related! I believed so, from my earlier readings about the issue. Here's an article from the same blog in the OP about the Uber migration: http://rhaas.blogspot.in/2016/08/ubers-move-away-from-postgresql.html

> very interesting read! Well, it's also quite a misleading read as the numerous rebuttals will point out: http://rhaas.blogspot.co.uk/2016/08/ubers-move-away-from-postgresql.ht… https://blog.2ndquadrant.com/thoughts-on-ubers-list-of-postgres-limita… http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-datab…

maybe the benchmarks are not so impressive and the workloads not super common, so they didn't want to mislead you? :) Anyway, some more info http://rhaas.blogspot.hu/2017/03/parallel-query-v2.html

> My biggest question, when everyone points out how many features PosgreSQL offers, is "why are you doing that in the database?" Your database is your biggest chokepoint. You shouldn't be doing any CPU intensive calculations in the database if you can avoid it, definitely. But these postgresql features are often about improved efficiency. Take a look at new things coming in postgresql 10[1]. All the new features in there are either performance neutral or big performance wins. FWIW, I've spent the last few years fixing performance problems in PostgreSQL databases. The solution is most often to de-normalize/pre-compute something, which means using stored procedures and triggers, which are most definitely doing more than "transactionally store and retrieve data". [1] https://rhaas.blogspot.com/2017/04/new-features-coming-in-postgresql-1…

Replication across major versions, for example to upgrade without downtime. Partial replication, to distribute shared data across a series of clusters, or for analytics and reporting as you mention. Replicating the data without replicating any table bloat. Being able to do limited writes (e.g. to temporary tables) on the standby. http://rhaas.blogspot.com/2011/02/case-for-logical-replication.html

Vacuuming has traditionally been a problem with large table sizes. In TimescaleDB we break up the tables so that they are smaller. That, combined with the new freeze map feature in Postgres (since PG 9.6: http://rhaas.blogspot.jp/2016/03/no-more-full-table-vacuums.html) make vacuums not an issue for us. Certainly we've never seen this issue (even on default autovacuum settings) and we've tested some huge datasets.

True (see here: https://www.postgresql.org/docs/9.6/static/parallel-plans.html ), but in PostgreSQL 10 there are Parallel Bitmap Heap Scans and Parallel Index Scans (see here: http://rhaas.blogspot.com/2017/03/parallel-query-v2.html ).

PG pre-dates (working) threads in Unix by a long time. Processes and threads are much the same thing as far as concurrency and parallelism are concerned. PG maintains a pool of pre-forked processes so there is typically no process startup delay. Shared memory regions and SysV cross-process locks facilitate IPC. Back in the day this is how threads were done in user-land: https://www.postgresql.org/docs/9.6/static/kernel-resources.html It does have parallel scans though : http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan-is-commit…

It is my vague understanding that a lot of these XID vacuum issues are being massively improved by a patch that landed a few months ago. http://rhaas.blogspot.com/2016/03/no-more-full-table-vacuums.html "No More Full-Table Vacuums" - Robert Haas

Compared to the big commercial engines, the one thing I miss is query parallelisim, but the initual stages are coming in v9.6: http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-is-committe…

Multiple worker thread will work together to do sequential scans, joins and aggregation. Some mentions here: http://rhaas.blogspot.com/2016/03/parallel-query-is-getting-better-and… But this is just a start and there are lots more opportunities to improve as mentioned here: https://wiki.postgresql.org/wiki/Parallel_Query_Execution

Blog post with some details and benchmarks by one of the authors of the patch: http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-is-committe…

I think the original ambition was to release it in 9.5 [0], but then it was delayed and rescheduled for 9.6 [1]. [0]: http://rhaas.blogspot.co.uk/2015/03/parallel-sequential-scan-for-postg… [1]: http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan-is-commit…

I don't think you can count on the file system to actually persist data on disk when you call flush(). Example http://rhaas.blogspot.it/2014/03/linuxs-fsync-woes-are-getting-some.ht…