An open MySQL bug now has a birthday cake
-
@Ronald said:
I don't disagree but for the sake of discussion I would see a few specific cases where Postgres has a clear advantage over MySQL:
- Non-boolean negative filters (when you query a table to get records that are *not* matching a value, and not merely checking for their existence). This will basically shortcut B-tree indexes and on systems where there is no bitmap index (MySQL, SQL Server) this is a big hit in performance as it will almost always lead to a full table scan.
- Query on tables that are polluted with deadwood values, such as cancelled orders. MySQL cannot exclude values from indexes.
- Anything that has to do with GIS. Postgres even has exclusion constraints that would prevent (as an example) to have cities with overlapping points.
- Anything that has to do with ranges (surveys with age groups, insurance claims, options strikings, etc). Postgres support ranges for native data types (as well as arrays).
This being said, most people run basic SELECT * queries so unless there is an underlying performance boosting mechanism (like the Oracle 11g filesystem or the Interbase versioning) it's unlikely to see a significant performance difference between mainstream RDBMS unless the workload is very high or very atypical.I agree that Postgres has a lot of nice features. Unfortunately it just lacks a lot of the core features needed to scale up to high loads. Or if you need high-availability, it's really not going to be your friend. As I mentioned above, I was using a 20MB database with streaming replication and it was a joke.
That said, if your load is light and you don't need super-effective HA, then Postgres is a fine choice. If somebody said "Morbs, we need you to design an app that will only ever have a few hundred simultaneous users, less than 1 gig of data and for which an acceptable HA solution is to take a recent SQL dump and load it up onto a hot standby," then I'd probably use Postgres, just because it has nicer data types and it actually lets you set default values that aren't simply static strings of text. Also, the use of schemas is a bit nicer than MySQL's "just chuck it in its own database" philosophy.
-
@ObiWayneKenobi said:
I swore off MySQL and all its related databases after seeing this video from Rob Conery highlighting the funky and downright crazy things MySQL does with your data. I don't care who uses it, the fact that a database arbitrarily mucks around with your data in violation of SQL constraints you put on it is scary.
I tried to watch that, or rather listen to it, but it's eleven minutes of annoying music with someone quack-quack-quacking away in a foreign language on top. Is there a transcript anywhere? It can't be that important if it isn't.
-
@ObiWayneKenobi said:
I swore off MySQL and all its related databases after seeing this video from Rob Conery highlighting the funky and downright crazy things MySQL does with your data. I don't care who uses it, the fact that a database arbitrarily mucks around with your data in violation of SQL constraints you put on it is scary.
I'm not watching an 11 minute video, especially with someone who sounds that douchey. While I don't dispute that MySQL does some pretty dumb stuff under-the-hood, I've never had a problem with it in the real world. And considering that the only reason you swore off MySQL is because of a YouTube video you saw, I'm going to assume you never had any problems, either.
That said, Postgres isn't exactly hot shit in a champagne glass under the hood, either. From its use of multiple processes and shared memory (versus threads) and the WAL and a number of controls which rival the Space Shuttle (which usually come with very sub-optimal defaults that need to be tuned--but be careful you know what you're doing because bad tuning will take shit down), Postgres is brimming with questionable design decisions. Then there's the whole vacuum thing; supposedly autovacuum now actually works, but the whole thing reeks of a bad design decision that was baked-in years ago and which can never be fixed now.
-
@blakeyrat said:
Well you better get on that 20lb Bakelite telephone, dial YUKon 5063, and get that straightened-out immediately while enjoying a Chesterfield.
Mmm.. Chesterfield.
Seriously, though, have you never had to look through a database when developing an app? I really didn't think that was unusual.
-
@Ronald said:
This is totally ludicrous.
- The video shows "bugs" in older MyISAM (v4 and older), not InnoDB. MyISAM was not designed for relational integrity but performance; it was loosely compliant with ANSI SQL so it would let you run DDL commands to create constraints even if it did not enforce them. This was the intended behavior and was clearly stated in the documentation.
- The "bug" about truncating data instead of cancelling the update is also not a bug but by design. The database is not "doing crazy things with your data" anymore than ROUND() is "doing crazy things" with numbers. And again - this was in older versions. Now InnoDB behaves like other databases.
So yeah, if you wanted a database that was less forgiving and would not try to accommodate inconsistencies instead of barfing out error messages, then MySQL MyISAM was not for you. That's like comparing PHP3 and Java; you have to take into consideration the purpose and history of the technology. MyISAM was basically a SQL layer running above a bunch of files and was faster than using the files yourself, nobody ever pretended it was a free Oracle. Anyone getting their panties in a bunch over MyISAM behavior is TRWTF because they did not RTFM.
So if you get nauseous because forcing 100 in a decimal(2,0) will store 99 instead of shitting bricks, then please remain as far as possible from today's NoSQL products with their "eventually consistent" approach and other considerations based on the CAP theorem instead of the ACID model.Oh, MyISAM.. So apparently the Postgres developers also just emerged from their group TimePod®. Yeah, MyISAM is shit, but I stopped using it in, what, 2004? If this is the best the Postgres devs can come up with, it really speaks volumes about how confident they are in their product..
-
@morbiuswilters said:
Seriously, though, have you never had to look through a database when developing an app? I really didn't think that was unusual.
I think once or twice. If it's my app, the first thing I do is write some admin tools so I don't have to touch the database directly other than maybe its backup command.
-
@Ronald said:
play some black magic like some semi-skilled programmer who puts NOLOCK all over the place
Ugh, the land of missing rows, duplicated rows and impossible-to-reproduce bugs. I feel dirty whenever I read that.
-
@Groaner said:
@Ronald said:
play some black magic like some semi-skilled programmer who puts NOLOCK all over the place
Ugh, the land of missing rows, duplicated rows and impossible-to-reproduce bugs. I feel dirty whenever I read that.
I remember when Jeff Atwood was like "I'm going to add NOLOCK to every single query on Stack Overflow!" sigh
-
@blakeyrat said:
Now sure why you didn't link to the video.
1) Now that the MySQL Bug told everyone its wish, it won't come true. The bug will never be fixed. THE VIDEO WAS COUNTERPRODUCTIVE!
2) He blew out candles with compressed air. I was really hoping he'd have unknowingly grabbed something more flammable, and torched his netbook. And curtains. And internal organs. It would have been less painful for everyone involved.
-
@morbiuswilters said:
I remember when Jeff Atwood was like "I'm going to add NOLOCK to every single query on Stack Overflow!" *sigh*
@Jeff Atwood said:
But in practice adding nolock to queries that you absolutely know are simple, straightforward read-only affairs never seems to lead to problems. I asked around, and I got advice from a number of people whose opinions and experience I greatly trust and they, to a (wo)man, all told me the same thing: they've never seen any adverse reaction when using nolock. As long as you know what you're doing. One related a story of working with a DBA who told him to add nolock to every query he wrote!
I don't know what's more depressing, the cavalier attitude or actually following through with it. To propose such a strategy to any DB developer worth his salt would only court isolation and hopefully end with the offender thrown in a vat of acid.
-
@Groaner said:
@morbiuswilters said:
I remember when Jeff Atwood was like "I'm going to add NOLOCK to every single query on Stack Overflow!" sigh
@Jeff Atwood said:
But in practice adding nolock to queries that you absolutely know are simple, straightforward read-only affairs never seems to lead to problems.
I asked around, and I got advice from a number of people whose opinions
and experience I greatly trust and they, to a (wo)man, all told me the
same thing: they've never seen any adverse reaction when using
nolock. As long as you know what you're doing. One related a story of
working with a DBA who told him to add nolock to every query he wrote!I don't know what's more depressing, the cavalier attitude or actually following through with it. To propose such a strategy to any DB developer worth his salt would only court isolation and hopefully end with the offender thrown in a vat of acid.
Why people bother with nolock is beyond me. Just set a lower transaction isolation level in the connection string and be done with. And it's not like the read committed level is bullet-proof anyways.
