4 ms·
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 ni
by kaamos 9y ago
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.
- fusiongyro 9y agoAs 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.
- kaamos 9y agoData 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-4e3f-45bc94c11759%40postgrespro.ru 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%3DXS3k7X9pfYKOZ6KGu2PbzGiX%2B1NF-dFkC35FJMCtWQ%40mail.gmail.com 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
- jeltz 9y agoWhy 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.
- kaamos 9y agoWithout 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.
- jeltz 9y agoBut 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.
- jandrewrogers 9y ago
- imron 9y ago> 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.
- cesarb 9y ago> 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.
- dhd415 9y agoDescribing 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/ 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.
- rleigh 9y agoThe 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.
- shangxiao 9y agoYour 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?