petergeoghegan

> Fundación PostgreSQL is a 100% independent non-profit organization not tied to any company or any other institution.

What about private individuals? Specifically, you?

Quoting the charter here: https://postgresql.fund/documents/Fundacion_PostgreSQL-Statu...:

""" Article 13. Honorary Members. ... 2. The Founder, Mr. Alvaro Carlos Hernandez Tortosa, is Honorary Life Member as the Honorary President of the Foundation, appointment that is not incompatible with any other post at the Foundation or the Patronage. He can assist the Patronage acts with voice. He doesn’t have the right to vote, unless his status of elected or founding Member of the Patronage applies. """

It goes on to say:

""" Article 15. Duties of the Patronage. During its performance, the Patronage must comply with the required under current legislation and the willingness of the Founder expressed in this Statutes. """

I have to admit that this document is above my reading comprehension level. What does this actually mean?

> This "upstream first" approach to product kernel development and testing is extremely efficient. Google has been successfully doing this with Chrome OS and Android for a while now, and is hardly alone in the industry.

This seems to be conflating testing a product against an upstream kernel and making your stable Linux distribution use an upstream kernel. A huge number of open source projects and vendors already do the first thing without any difficulty. I believe that doing the second thing is much rarer. Do Google do both things?

It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation.

You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking.

I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.

It's not possible to give you a simple answer, especially not without a lot more information. Perhaps you can test your workload with postgres 14 beta 1, and report any issues that you encounter to one of the community mailing lists.

> Very interesting thanks for the update - how great is the Internet to hear directly from the developer!

I see the names of a few people that also work on Postgres on this thread. We're not all that hard to get a hold of if you're a user that has some kind of feedback or question, for what it's worth. The culture is very open in that sense.

> Do you know if anyone is turning their attention to this or is it not currently being tackled by anyone?

This is one of the goals of the zheap project. I myself have some very tentative ideas for tackling it within the standard table access method, heapam. I have not specifically committed to working on it on any timeframe. I haven't completely convinced myself that the approach I'm thinking of is truly robust and practicable. It's pretty complicated, especially because I cannot really know what will break and need to be fixed until I spend significant effort on the implementation.

Thanks.

I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration.

This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is that we let the workload figure out what cleanup is required on its own, in an incremental fashion.

Another interesting detail is that there is synergy with the deduplication stuff -- again, very nonlinear behavior. Kind of organic, even. Deduplication was a feature that I coauthored with Anastasia Lubennikova that appeared in Postgres 13.

Co-author of that feature here.

Clearly it doesn't eliminate the possibility of wraparound failure entirely. Say for example you had a leaked replication slot that blocks cleanup by VACUUM for days or months. It'll also block freezing completely, and so a wraparound failure (where the system won't accept writes) becomes almost inevitable. This is a scenario where the failsafe mechanism won't make any difference at all, since it's just as inevitable (in the absence of DBA intervention).

A more interesting question is how much of a reduction in risk there is if you make certain modest assumptions about the running system, such as assuming that VACUUM can freeze the tuples that need to be frozen to avert wraparound. Then it becomes a question of VACUUM keeping up with the ongoing consumption of XIDs by the system -- the ability of VACUUM to freeze tuples and advance the relfrozenxid for the "oldest" table before XID consumption makes the relfrozenxid dangerously far in the past. It's very hard to model that and make any generalizations, but I believe in practice that the failsafe makes a huge difference, because it stops VACUUM from performing further index vacuuming.

In cases at real risk of wraparound failure, the risk tends to come from the variability in how long index vacuuming takes -- index vacuuming has a pretty non-linear cost, whereas all the other overheads are much more linear and therefore much more predictable. Having the ability to just drop those steps if and only if the situation visibly starts to get out of hand is therefore something I expect to be very useful in practice. Though it's hard to prove it.

Long term, the way to fix this is to come up with a design that doesn't need to freeze at all. But that's much harder.

Although the article doesn't mention it, index bloat will be far better controlled in Postgres 14:

https://www.postgresql.org/docs/devel/btree-implementation.h...

One benchmark involving a mix of queue-like inserts, updates, and deletes showed that it was practically 100% effective at controlling index bloat:

https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZ...

The Postgres 13 baseline for the benchmark/test case (actually HEAD before the patch was committed, but close enough to 13) showed that certain indexes grew by 20% - 60% over several hours. That went down to 0.5% growth over the same period. The index growth much more predictable in that it matches what you'd expect for this workload if you thought about it from first principles. In other words, you'd expect about the same low amount of index growth if you were using a traditional two-phase locking database that doesn't use MVCC at all.

Full disclosure: I am the author of this feature.

I would say that that's pretty dubious claim with modern versions of Postgres and MySQL/InnoDB, running on modern hardware. See for example this recent comparative Benchmark from Mark Callaghan, a well known member of the MySQL community:

https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs...

I'm not claiming that this benchmark justifies the claim that Postgres broadly performs better than MySQL/InnoDB these days -- that would be highly simplistic. Just as it would be simplistic to claim that MySQL is clearly well ahead with OLTP stuff in some kind of broad and entrenched way. It's highly dependent on workload.

Note that Postgres really comes out ahead on a test called "update-index", which involves updates that modify indexed columns -- the write amplification is much worse on MySQL there. This is precisely the opposite of what most commentators would have predicted. Including (and perhaps even especially) Postgres community people.

The Postgres community mailing list for development work -- pgsql-hackers.

There is also index deduplication in Postgres 13 and the B-Tree enhancements in Postgres 12. All of these enhancements significantly improved the situation for workloads affected by what the blog post calls write amplification. (I myself call this phenomenon index version churn, since it is more descriptive and has less baggage.)

I was the author of all of the above, including the Postgres 14 work you mentioned (though Anastasia Lubennikova was the primary author of index deduplication). To me it feels like one very large project -- the effects are cumulative, and each major Postgres version had B-Tree work that built on the last release in one way or another.

Thanks

I committed a patch that added a mechanism I called "bottom-up index deletion" recently:

https://www.postgresql.org/docs/devel/btree-implementation.h...

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Bottom-up deletion is specifically designed to ameliorate what the blog post refers to as "write amplification". Testing has shown that it's very effective with many workloads.

I am the author of a tool that generates wxHexEditor tags and annotations for Postgres relation files -- pg_hexedit:

https://github.com/petergeoghegan/pg_hexedit

I've invested quite a lot of effort in it, and it would be nice to have support for multiple hex editors. That was anticipated to some degree:

https://github.com/petergeoghegan/pg_hexedit#supporting-othe...

I understand why you favor a declarative template format for describing files with tags -- that probably scales really nicely. What I'm doing is pretty grotty, but works surprisingly well in practice. I procedurally generate a description of each file in a shell script, and then open the file in wxHexEditor. I'm generating huge XML files, which is slow, but there are simple workarounds to get acceptable performance.

wxHexEditor doesn't support declarative tags. But even if it did I might not want to use them; the on-disk format of PostgreSQL is much more complicated than most file formats, and isn't supposed to be consumed by third party tools. Writing a C program that uses the struct definitions from the server itself makes the complexity quite manageable -- the tool is basically feature complete, even though I haven't spent a huge amount of time on it.

That said, it would be great if I could adapt pg_hexedit to a hex editor that had some kind of "best of both worlds" support for tags - tags that can be generated lazily and on-demand, when a portion of the file needs to be drawn or redrawn. This would be easy to adapt to -- Postgres relation files always consist of a series of 8KiB blocks/pages. My tool can easily generate tags for any single block without knowing any special context or having any expensive-to-generate state -- I just need a block number (i.e. an 8KiB-aligned byte offset).

> I'm calling for an strategic discussion

There is practically no information about Babelfish available to analyze. You can currently request a preview of Babelfish if you have an AWS account, though I'm not aware of anyone having even that level of access. There is a single page placeholder on a Github.io that says "Babelfish for PostgreSQL will be available on Github in 2021".

And so I genuinely don't know what a strategic discussion really means here. How much can be decided about something based on a vague press release?

> To announce such a big contribution to Postgres, and be it open sourced when published, is very welcome

But the source code hasn't been published. As I said to you today on pgsq-hackers, that's why it hasn't been taken seriously - that's just how it works.

> But there's still some worry that it would become one, one day

Who are you attributing that concern to, exactly?

You're basically claiming that it will be a great opportunity. But you're also claiming that it might be an existential crisis. Do I have that right?

Have you considered the possibility that it might actually be neither of those two things?

NULL values are not special as far as deduplication is concerned. They use approximately as much disk space as a non-NULL integer column without deduplication, and compress just as well with deduplication. Deduplication is effective because it eliminates per-tuple overhead, so you see most of the benefits even with index tuples that naturally happen to have physically small keys. You'll still get up to a 3x decrease in storage overhead for the index provided there is low cardinality data (and not necessarily that low cardinality, ~10 or so tuples per distinct value will get you there).

The NULL issue is documented directly -- see the "Note" box here:

https://www.postgresql.org/docs/devel/btree-implementation.h...

Are you aware of any contemporary on-disk or in memory DB system that typically uses a page size of 4KiB or less? And if not, are you expecting that to become a trend in the future?

I didn't say that these specific claims are simply false. Just that they sure seem to hint at extraordinary improvements in a broad range of cases. The website itself also seems to me to be suggestive in just the same way, only much more so.

I myself don't think that that amounts to taking pains to qualify their claims. Other people will have to make their own minds up about whether or not that's the case.

The paper says 83x, and even makes stronger claims that are fairly unqualified:

In short, the experimental achievements of the PGM-index are: (i) better space occupancy than the FITing-tree by up to 75% and than the CSS-tree by a factor 83×, with the same or better query time; (ii) uniform improvement of the performance of RMI in terms of query time and space occupancy, and 15× faster construction, while requiring no hyperparameter tuning; (iii) better query and update time than a B+-tree by up to 71% in various dynamic workloads while reducing its space occupancy by four orders of magnitude (from gigabytes to few megabytes).

I wonder why it was just four orders of magnitude, though. Why not six or twelve?

Not likely. I have done a lot of work on B-Tree indexing in Postgres in the past several years, and this is all Greek to me.

I think that you're on to something here.

I write C full time, and have done so for many years. It didn't occur to me that the pointer to a pointer aspect was the source of any of the confusion until just now. Actually, I don't think that I consciously registered its presence when I read the code. There is only one interpretation of the code that makes any sense, and that's at least easy for an expert to see quickly.

I am sure that people that think that the terser variant is overly clever don't get it. To be fair, it's hard to generalize from this one example. But I think that I know exactly what Torvalds intended to draw attention to.

It seems to me that what is at issue here is the difference between easy to understand and simple. They're not always the same thing. Simpler code tends to be easier to audit for bugs (as a practical matter).

pg_stat_activity isn't an extension -- it's a standard system view

I am cautiously optimistic that my own recent work on version churn in B-Tree indexes will go a long way towards fixing those problems:

https://postgr.es/m/CAH2-Wz=CEKFa74EScx_hFVshCOn6AA5T-ajFAST...

This can be used without changing anything in the heap. It's less than a thousand lines of C. You could say that it's complementary to zheap, actually.

zheap makes it possible to update the same row many times without requiring new index entries, even when there is a long running transaction that holds back VACUUM. However, it does not avoid the problem of requiring a whole new set of index entries for all indexes in the event of even one indexed column being modified by updates.

Strictly speaking my patch doesn't "fix" that problem, either, but it comes pretty close. It teaches the indexes to "fight back" against version churn caused by updates that cannot use the HOT optimization. It makes non-HOT updates responsible for cleaning up their own mess -- no more negative externalities. In practice this seems to more or less fix the exact thing that the Uber blog post complained about, which was the "write amplification" incurred in all indexes when only one indexed column was changed by an update.

(I am omitting some subtleties here, but that's the general thrust of it.)

Amazon themselves use Aurora PostgreSQL for Amazon retail fulfillment databases:

https://aws.amazon.com/solutions/case-studies/amazon-fulfill...

Your point about MyRocks is a good one. It's also true that MyRocks takes a similar approach to Postgres when it comes to implementing the higher isolation levels. It wouldn't make sense to add InnoDB style next-key locks to MyRocks, either.

Systems that closely adhere to traditional 2PL designs such as InnoDB or DB2 end up with tight coupling between recovery, concurrency control, and storage. This has many consequences, both good and bad. It's no coincidence that Postgres can support quite a variety of index access methods, including support for transactional full text search. You get the transactional stuff "as standard" with the Postgres approach to versioned storage. There is no need to bake concurrency control into each and every index access method.

Recovery in Postgres is simplified and sped up considerably by the approach taken to MVCC. Same is true with transaction rollback. SQL Server users (on recent SQL Server versions) can optionally get a similar trade-off by enabling constant time recovery (CTR), which is called Accelerated Database recovery (ADR) in the MS marketing material. See the paper:

https://www.microsoft.com/en-us/research/uploads/prod/2019/0...

SQL Server has something called "background cleanup" when this feature is enabled, which takes place in a separate process called "the cleanup process" - just like VACUUM. The paper itself explicitly acknowledges the similarity. All of the advantages the paper claims for CTR/ADR are also advantages for Postgres.

It's also true that both traditional SQL Server (SS with ADR/CTR disabled) and MySQL have background cleanup processes of their own for indexes, but that is a little different. It's about asynchronously reclaiming space for index entries that were already delete-marked.

Actually, Postgres does not add any performance features to point releases at all. Point releases are only for bug fixes. You may be thinking of MySQL.

Occasionally, there are cases that could be argued to be exceptions to the general rule. But that's a hard argument to make -- everything committed to a back branch is officially a bug fix. Things like optimizer regression fixes are "performance enhancements" in a certain sense, but are nevertheless justified as bug fixes.