piggybacking on this: does anyone know a Postgres alternative to PlanetScale?
Ask HN: It's 2023, how do you choose between MySQL and Postgres?
141–150 of 366 posts
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#142There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…
> avoid Oracle as if it has herpes herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox). you should avoid Oracle like it's a blood pathogen.
[deleted]
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#143I know it seems dumb, but postgres really needs to add the simple developer experience stuff like:
SHOW CREATE TABLE;
SHOW TABLES;
SHOW DATABASES;
SHOW PROCESSLIST;
CockroachDB added these aliases ages ago.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#144I choose Postgres every time, because that is what I am familiar with.
But nerdy stuff:
Postgres stores data linearly (in heap - which has nothing to do with the heap data structure used for sorting, it just means pile of data). If you need to have fast access to data, you need to add secondary indexes - and the secondary indexes point to location in the heap as "this is where you find the data".
MySQL stores data in a tree - a table is a tree sorted by primary key. You can create secondary indexes and instead of a pointer they contain the primary key value.
That means for example that data with similar primary key will be located physically nearby each other, in MySQL but not in Postgres. At the same time, inserting new data with random (like UUID) primary key in MySQL will write all over the table, but will mostly "append at the end" in Postgres.
Postgres also implements MVCC with Serializable Snapshot Isolation - so data that someone changes exists in multiple copies and needs to be cleaned up later - but there's no locking. MySQL relies on locks instead so there's no duplication but you might see transactions waiting for each other. I don't remember if MySQL implements a proper serializable isolation - but that is not really the default on any database anyway.
Interestingly, Oracle has very similar design to Postgres (though it uses rollback segment for old data, so there's no bloat and vacuum but you might get "snapshot too old" error) while MS SQL Server is also tree and lock-based database like MySQL.
Does this impact you? It might, like in cases where MySQL performs terribly due to UUID keys or Postgres can't vacuum fast enough due to high volume of updates or something. Or you're implementing money settlement logic and need proper serilizable transactions, who know. But it is cool to know the implementation details.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#145Earlier quoted context omitted.
> avoid Oracle as if it has herpes herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox). you should avoid Oracle like it's a blood pathogen.
hello, fellow person with herpes! (I assume) The worst part about having it is having to talk about having it. It's really not bad as a condition separate from societal concern.
I find similar societal concern when I tell friends I use Oracle.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#146Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime.…
Having used postgres for the past decade, I tried MySQL for a side project to see whats changed with it. The sad answer is that it feels like nothing has changed - Oracle seems to have let what used to be a core technology of the industry languish. I'm sure there are use cases where MySQL will be the better choice over postgres, but the future for the stack looks bleak.
Oracle seems to have let what used to be a core
technology of the industry languish
I wonder how much Oracle spends on MySQL every year? They're spending a lot of money to keep MySQL at kind of a "not quite good enough" state. But they can't kill it outright - it'd be like boiling a frog fast instead of slow.
In the end, I wonder what extinguishing MySQL really accomplished for them. It might have bought them some breathing room but Postgres quickly filled MySQL's old segment.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#147There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…
Postgres is >50x slower for range queries(example below) and is akin to using array-of-pointers (ie Java) whereas MySQL supports array-of-struct (C). Illustration from Dropbox scaling talk below.
Sneak peek photo [1] (from [2]). Just imagine its literally 500-1000x more convoluted per B-tree leaf node. That's every Postgres table unless you CLUSTER periodically.
[1]: https://josipmisko.com/img/clustered-vs-nonclustered-index.w...
[2]: https://josipmisko.com/posts/clustered-vs-non-clustered-inde...
Mind boggling how many people aren't aware of primary indexes in MySQL that is not supported at all in Postgres. For certain data layouts, Postgres pays either 2x storage (covering index containing every single column), >50x worse performance by effectively N+1 bombing the disk for range queries, or blocking your table periodically (CLUSTER).
In Postgres the messiness loading primary data after reaching the B-tree leaf nodes pollutes caches and takes longer. This is because you need to load one 8kb page for every row you want, instead of one 8kb with 20-30 rows packed together.
Example: Dropbox file history table. They initially used autoinc id for primary key in MySQL. This causes everybodys file changes to be mixed together in chronological order on disk in a B-Tree. The first optimization they made was to change the primary key to (ns_id, latest, id) so that each users (ns_id) latest versions would be grouped together on disk.
Dropbox scaling talk: https://youtu.be/PE4gwstWhmc?t=2770
If a dropbox user has 1000 files and you can fit 20 file-version rows on each 8kb disk page (400bytes/row), the difference in performance for querying across those 1000 files is 20 + logN disk reads (MySQL) vs 1000 + logN disk reads (Postgres). AKA 400KiB data loaded (MySQL) vs 8.42MiB loaded (Postgres). AKA >50x improvement in query time and disk page cache utilization.
In Postgres you get two bad options for doing this: 1) Put every row of the table in the index making it a covering index, and paying to store all data twice (index and PG heap). No way to disable the heap primary storage. 2) Take your DB offline every day and CLUSTER the table.
Realistically, PG users pay that 50x cost without thinking about it. Any time you query a list of items in PG even using an index, you're N+1 querying against your disk and polluting your cache.
This is why MySQL is faster than Postgres most of the time. Hopefully more people become aware of disk data layout and how it affects query performance.
There is a hack for Postgres where you store data in an array within the row. This puts the data contiguously on disk. It works pretty well, sometimes, but it’s hacky. This strategy is part of the Timescale origin story.
Open to db perf consulting. email is in my profile.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#148I know it seems dumb, but postgres really needs to add the simple developer experience stuff like: SHOW CREATE TABLE; SHOW TABLES; SHOW DATABASES; SHOW PROCESSLIST; CockroachDB added these aliases ages ago.
This.
For anything at home, I would use MySQL just for those things. The psql client feels very primitive by comparison to me - even though it isn't.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#149Use MySQL if you're expecting to have to do large operational database tasks re: migrations, maintenances, with no ability to go offline. gh-ost, percona-osc, the new INSTANT DDL stuff, is all quite far ahead in MySQL-land. Additionally, Vitess and Planetscale are making huge strides in MySQL performance. There are more people and guides in the world to help recover even the most mutilated of MySQL databases. MySQL g…
This is wrong. MySQL does not support transactional DDL, so you cannot run migration and abort them in the middle. Always use postgresql. It's more logical, more extensible, saner, supports many extensions and is more predictable. MySQL is inconsistent crap, that trades away consistency, correctness and stability for a little bit of performance in standard use cases. Do yourself a favor and always use postgreSQL. I s…
Production MySQL databases of any significant size use pt-osc or gh-ost for schema changes, and these can be throttled, paused, aborted and so on.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#150Unpopular opinion on HN apparently, but MySQL - It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. - Maintenance is simpler and far less necessary in my general experience. - Replication is simpler and more reliable. - You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend. That said, I wouldn't run Orac…
I would disagree on maintenance being simpler. I have never had Postgres randomly munge a table and require me to run a command to fix it.