Hacker News Reader

Comment by kaamos | Hacker News Reader

I know what I'm talking about. Over the last couple of years I had to speak to many people in the PostgreSQL community. While there are indeed many smart and nice people with balanced views and opinions there, the majority has this ideological bias. With ideology being that PostgreSQL is superior to MySQL in every single way. All counter-examples and arguments are usually ignored. I think it's something unique to PostgreSQL community, I've never met anything like that in other technical communities.

View on HN

As someone who might fairly get scolded for this, we feel like praising MySQL's features is a bit like enjoying the recessed lighting and granite counters in a house whose roof is actively leaking.

There are certainly features MySQL has that Postgres doesn't, but data integrity is problem 1, and most of us got burned by MySQL on that one at some point before we got here. Or, we deployed MySQL and then found out that the features we were depending on were InnoDB-only, but switching to InnoDB cratered our performance. Or we tripped on one too many of MySQL's numerous special cases or silent limitations.

You can use MySQL productively. You can even benefit from its special features. But the thing taken together is a bit of a mine field.

Postgres has some false advertising issues too ("object-relational"?) but the day-to-day living with it is a lot nicer, because the roof doesn't leak. I can live without the granite counters.

Data integrity is of course important, and MySQL indeed has a weak story here, especially in older versions.

Is PostgreSQL flawless when it comes to data integrity? No, that would also be false advertising. Some examples:

- data checksums are disabled by default (silent data corruptions!), and the user manual specifically warns they are not cheap. And the reasons they are expensive is that PostgreSQL does not support O_DIRECT IO;

- removing a file on the filesystem may lead to a silent loss of data in PostgreSQL: https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3...

- some important control structures (which also impact data visibility and thus, integrity) are not even protected by checksums: https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYK...

- there are no tools to verify replication integrity, similar to pt-table-checksum for MySQL; that would require statement-based replication, which is not supported in PostgreSQL, even in the next major release

Why would O_DIRECT make checksums any cheaper? You always update the checksum at the same time as the data so I cannot see how O_DIRECT could matter.

I am pretty sure the real reasons for checksums being off by default are two:

1) That you need to WAL log hint bit changes if for checksums to work. Hint bits are part of PostgreSQL's MVCC implementation and that you do not need to WAL log them is a clever optimization. This extra WAL traffic can at least in theory hurt some workloads.

2) Probably most importantly: nobody has provided the benchmarks necessary to show that the cost is not too high.

Without O_DIRECT, the page may be updated multiple times in the page cache before it hits the disk. The only thing PostgreSQL can do is to update the checksum before expelling the page from its own cache, so it will be doing unnecessary work in write-intensive workloads. But yes, hint bits and WAL is also a good point.

But PostgreSQL does not update the file directly so this is not an issue. Instead PostgreSQL does all updates in its buffer cache, and then has a background writer which flushes modified shared buffers to the file (but without using O_DIRECT), and then finally it fsyncs the modified file. The window between modifying the file and fsyncing is usually not big so almost always when a page is updated multiple times that will happen before a page is written from the buffers to the OS's file cache.

The above is obviously inefficient which slows down writes, but checksums are not affected by this since those are only calculated on flush. The costs of not using O_DIRECT are 1) extra work on flushing to disk, 2) IO spikes due to the unpredictable nature of when and in what order the OS decides to flush to disk, and 3) wasted RAM. The main benefit is that being able to use the file cache on read loads (by tuning PostgreSQL with small buffers) is that PostgreSQL is nicer to run on shared machines, for example my laptop, since the OS can steal back the read cache when PostgreSQL stops using it.

Checksums on modified pages should only be computed immediately prior to transferring pages to disk. DIRECT_IO makes that transfer point explicit so you can compute the checksum exactly once per transfer regardless of the number of page modifications. If you use the kernel cache like PostgreSQL, you must apply the checksum pessimistically because you don't know when the page will _actually_ be written back to disk by the kernel in-between page modifications.

Computing page checksums burns quite a bit of memory bandwidth, and memory bandwidth is a major resource bottleneck in modern databases. In the DIRECT_IO case, the checksum memory bandwidth should be the same as your storage bandwidth; pessimistic checksumming without DIRECT_IO can burn memory bandwidth that significantly exceeds the storage bandwidth.

Particularly in cases where you have modern, high-performance storage devices, like PCIe connected flash arrays, you can't afford to burn memory bandwidth on checksumming beyond the minimum technically required.

As I explained in another reply, PostgreSQL does no write directly to the files/file cache. It has its own buffer cache and only calculates the checksum when flushing dirty pages to disk (where PostgreSQL does a normal, cached write syscall and then fsyncs). The lack of O_DIRECT causes other performance issues, but not this one.

> data checksums are disabled by default (silent data corruptions!)

Use ZFS.

> removing a file on the filesystem may lead to a silent loss of data in PostgreSQL

Don't remove database files. LOL. Looking at the link:

> data file is zeroed after some power loss(it is the most known issue on XFS in the past)

Use ZFS.

> not even protected by checksums

Use ZFS.

> and most of us got burned by MySQL on that one at some point before we got here

Using 3 bytes per codepoint for UTF8 was the one that got me to switch (boy wasn't that fun to discover).

Yes it was fixed a while ago now, but it wasn't fixed when I needed it and so I switched to PostgreSQL and never looked back.

> Or, we deployed MySQL and then found out that the features we were depending on were InnoDB-only, but switching to InnoDB cratered our performance.

Or worse, we were expecting InnoDB but the default on the mysqld which comes with the RHEL version the client used to deploy our software was MyISAM, so the cascade didn't work when deleting a row, and the dangling reference caused an exception deep in the framework we were using, making parts of our software stop working.

Describing the production operation of MySQL as a minefield is an excellent analogy. I maintained a list of the mines I stepped on in the last job I had where MySQL was used and here are the two I encountered with the biggest blast radius:

* If you have pre-5.6.4 date/time columns in your table and perform any kind of ALTER TABLE statement on that table (even one that doesn't involve the date/time columns), it will TAKE A TABLE LOCK AND REWRITE YOUR ENTIRE TABLE to upgrade to the new date/time types. In other words, your carefully-crafted online DDL statement will become fully offline and blocking for the entirety of the operation. To add insult to injury, the full table upgrade was UNAVOIDABLE until 5.6.24 when an option (still defaulted to off!) was added to decline the automatic upgrade of date/time columns. If you couldn't upgrade to 5.6.24, you had two choices with any table with pre-5.6.4 types: make no DDL changes of any kind to it or accept downtime while the full table rewrite was performed. To be as fair as possible, this is documented in the MySQL docs, but it is mind-blowing to me that any database team would release this behavior into production. In other words, in what world is the upgrade of date/time types to add a bit more fractional precision so important that all online DDL operations on that table will be silently, automatically, and unavoidably converted to offline operations in order to perform the upgrade? To me, this is indicative of the same mindset that released MySQL for so many years with the unsafe and silent downgrading of data as the default mode of operation.

* Dropping a table takes a global lock that prevents the execution of ANY QUERY until the underlying files for the table are removed from the filesystem. Under many circumstances, this would go unnoticed, but I experienced a 7-minute production outage when I dropped a 700GB table that was no longer used. Apparently, this delay is due to the time it takes the underlying Linux filesystems to delete the large table file. This was an RDS instance, so I had no visibility into the filesystem used and it was probably exacerbated by the EBS backing for the RDS instance, but still, what database takes out a GLOBAL LOCK TO DROP A TABLE? After the incident, I googled for and found this description of the problem (https://www.percona.com/blog/2009/06/16/slow-drop-table/) which isn't well-documented. It's almost as if you have to anticipate every possible way in which MySQL could screw you and then google for it if you want to avoid production downtime.

There are others, too, but to this day, those two still raise my blood pressure when I think about them. In addition to MySQL, I've pushed SQL Server and PostgreSQL pretty hard in production environments and never encountered gotchas like that. Unlike MySQL, those teams appear to understand the priorities of people who run production databases and they make it very clear when there are big and potentially disrupting changes and they don't make those changes obligatory, automatic, and silent as MySQL did.

The object-relational stuff works (table inheritance). Or at least, it did the last time I tried it out a few years back; I've never used it in production.

Your insistence on replying to every single comment on this thread combined with your statements like "I know what I'm talking about" (and you don't) suggests to me that perhaps you're an evangelist yourself?

It's just something I care about. You may call me an evangelist if you like, I consider myself an anti-evangelist.

Indeed you're not very effective in prasing MariaDB.

Reply on news.ycombinator.com