News for hackers. Stuff that matters.
Re:Leaving MySQL by leetrout on Sunday December 05, 2021 @11:20PM (#29456102)
> MySQL is a pretty poor database, and you should strongly consider using Postgres instead.

Wow, right on the chin with that one.

> More jarring were the people who insisted everything was OK (it seems most MySQL users and developers don't really use other databases)

I only have anecdata of my usages of MySQL and Postgres but I swear people that cut their teeth on MySQL and have never used Postgres just don't know what they are missing.

Yes Postgres can be slower out of the box and yes Postgres has worse connection handling that usually requires a pooler but the actual engine and it's performance makes it worth it in my opinion.

Re:Leaving MySQL by ploxiln on Monday December 06, 2021 @12:31AM (#29456498)
The author was rather focused on the optimizer/scheduler. I can easily believe that it is much less advanced and robust, and messier, in MySQL. I really liked MySQL (and more recently MariaDB), and I have experienced some significant annoyances with Postgres recently, and it's for a completely different set of reasons.

Logical Replication is the big one. MySQL has had logical (and "mixed") replication for a decade (or more?). PostgreSQL has gotten built-in logical replication only recently, and it's still very annoying. It doesn't replicate schema change statements, so those have to be applied independently by some external system, and of course it's not going to be timed exactly right so replication will get stuck. And somehow for very lightly active applications generating just a few MB of writes per day, the stuck replication log can back up 10s of GBs per day. And does logical replication across versions for zero-downtime upgrades work in Postres yet? I dunno.

Connections you mentioned already, Postgres needs an external connection pooler/proxy, MySQL not nearly so soon. Vacuuming is a problem I never had with MySQL/MariaDB of course.

I'm an infrastructure dev, not an DBA or an advanced SQL user, I never used window functions, or wrote a query that filled half the screen, or have a thick web of foreign keys ... If you keep things damn simple so you have confidence in efficiency and performance, then MySQL works pretty damn well in my experience, and Postgres, for all its academic correctness and advanced query feature set, has been pretty damn annoying.

And, you can tell MySQL to use a particular index for a query!

Re:Leaving MySQL by ufmace on Monday December 06, 2021 @12:58AM (#29456639)
This is about where I'm at. I've used both DBs and adminned sites with them. Sure PG has a ton of nice SQL features that are really handy if you want to do any kind of more advanced analysis. I wouldn't be surprised either to learn that it's much more efficient on executing complex queries. But the bottom line is that the great majority of production usage is basic CRUD queries using simple SQL, and a lot of applications will never need to use advanced SQL. The things MySQL does do better, replication and connections like you mentioned, tend to be more suited to keeping big dumb basic sites up and running reliably.

I'm usually gonna reach for Wordpress first along with MySQL if I need to set up a basic blog for non-technical people to contribute to. Maybe the DB isn't as cool as it could be, but it works fine, and I hardly ever have need to touch the database itself directly.

Re:Leaving MySQL by yholio on Monday December 06, 2021 @03:10AM (#29457383)
it's prety much a 90%-10% issue: Mysql is quite good at the 10% of things regular users need to do 90% of the time. A website that performs simple selects and inserts, for a few hundred clients a second, only cares about speed and consistency, and MySQL delivers.

When the client count and the business complexity explode, things start to get hairy fast, but by that time the investment in MySQL is hard to walk away from. You will start to hit all sort of edge cases in other DBs - which MySQL did not exhibit simply because your application grew around its feature set and limitations.

Re:Leaving MySQL by eitland on Monday December 06, 2021 @06:43AM (#29458542)
> When the client count and the business complexity explode, things start to get hairy fast, but by that time the investment in MySQL is hard to walk away from. You will start to hit all sort of edge cases in other DBs - which MySQL did not exhibit simply because your application grew around its feature set and limitations.

I'd like to point out that this seems to happen whatever db you move from and to.

Sometime over a decade ago I was part of a team moving from an ancient version of Sybase Adaptive Server Anywhere to the newest Microsoft SQL server running on a beefier server.

We ported everything carefully and tested it out. We also felt good since both where dialects of T-SQL.

...and then we ran right into so large performance problems that we had fall back to the old system.

The reason was that at least older models of had Sybase ASA had implicit indexes.

Feels like there is always something.

Re:Leaving MySQL by ksec on Monday December 06, 2021 @11:08AM (#29461455)
I am pretty sure that is also a 90% - 10% issue as well. 90% of user's App complexity dont grow out of those MySQL comfort zone.
Re:Leaving MySQL by mst on Monday December 06, 2021 @12:14PM (#29462498)
I've seen quite a few codebases where the complexity of the problem did but they basically bodged around it in the app tier in order to keep the SQL within what MySQL could handle - and it's -very- easy to not even notice you're doing that because it's a technical debt/death by a thousand cuts sort of experience when it does happen.

Then again, depending on your scenario, introducing a bunch of extra complexity into your app tier code may be less annoying than introducing a bunch of extra complexity (connection poolers etc.) into your infrastructure.

So it ends up that there's a bunch of workloads where mysql is obviously the better option, and a bunch of workloads where postgresql is obviously the better option, and also a bunch where they're mostly differently annoying and the correct choice depends on a combination of your team composition and where your workload is likely to go in the next N years.

Re:Leaving MySQL by brightball on Monday December 06, 2021 @11:20AM (#29461624)
A lot of applications don’t use more advanced SQL simply because it’s not available.

Just as an example, there are so many people who assume they need a 3rd party search engine simply because what’s built into their database is so bad compared to the options you get with PG.

You’re welcome to only use your database as a crud data store while you manage a mountain of other tools to work around all of the limitations…but you don’t have to.

Re:Leaving MySQL by mst on Monday December 06, 2021 @12:08PM (#29462403)
> I'm an infrastructure dev, not an DBA or an advanced SQL user, I never used window functions, or wrote a query that filled half the screen, or have a thick web of foreign keys ... If you keep things damn simple so you have confidence in efficiency and performance, then MySQL works pretty damn well in my experience, and Postgres, for all its academic correctness and advanced query feature set, has been pretty damn annoying.

At that point you're effectively using MySQL as a key/value store with an SQL interface, rather than a full RDBMS.

Which is, in fact, pretty much exactly what MySQL was originally designed to be and it's extremely good at it.

> If you keep things damn simple so you have confidence in efficiency and performance

I can have that level of confidence on a minimally tuned (albeit sensibly indexed, you still have to make sure the indices you need -exist- on anything, realistically) postgres at levels of schema/query complexity where getting mysql to handle it -at all- would be ... an experience.

Having used both extensively, I'd say that there's a lot of knowledge when using mysql that's basically "introducing complexity into your application and query pattern to work around the optimiser being terrible at doing the sensible thing" and then there's lots of knowledge when administrating postgresql that's "having to pick and choose lots of details about your topology and configuration because you get a giant bag of parts rather than something that's built to handle the common cases out of the box".

So ... on the one hand, I'm sufficiently spoiled by being able to use multi column keys, complex joins, subqueries, group by etc. and rarely have to even think about it because the query will perform just fine that I vastly prefer postgres by default as an apps developer these days.

On the other hand, I do very much remember the learning curve in terms of getting a deployment I was happy with and if I hadn't had a project that flat out required those things to be performant I might never have gotten around to trying it.

So, in summary: They've chosen very different trade-offs, and which set of downsides is more annoying is very dependent on the situation at and.

Re:Leaving MySQL by da_chicken on Monday December 06, 2021 @01:45PM (#29463859)
> Having used both extensively, I'd say that there's a lot of knowledge when using mysql that's basically "introducing complexity into your application and query pattern to work around the optimiser being terrible at doing the sensible thing" and then there's lots of knowledge when administrating postgresql that's "having to pick and choose lots of details about your topology and configuration because you get a giant bag of parts rather than something that's built to handle the common cases out of the box".

This completely maps to my experience as well. I still believe Postgres' defaults being set to use an absolute minimum footprint, especially prior to PostgreSQL 9, significantly impacted it's adoption. It's better now, although IMO it could still do with an easier configuration where you could flag the install as dev/shared or prod/standalone to provide more sensible settings for the most common situations. Like, gosh, I'd like to be able to stand up an instance and easily tell it to use 90% of the system memory instead of 100 MB or whatever it was.

But the MySQL issues were much worse. Outer join and subquery problems, the lack of functions like ROW_NUMBER(), a a default storage engine that lacked transactions and fsync(), foreign keys that don't do anything, etc. I've met so many people who think MySQL limitations are RDBMS limitations, or don't understand database fundamentals because MySQL just didn't implement them properly. Then again, I also remember when the best reason to use MySQL was the existence of GROUP_CONCAT(), which everything else lacked for religious reasons.

I also vividly remember prior to MySQL 5.5 when new users of PostgreSQL, Oracle, or MS SQL Server would discover that, instead of making a guess for what you wanted, the RDBMS would actually return error messages and expect you to understand and solve them deterministically. And somehow they would be angry about it! Old MySQL (mostly 3.3, but 4.0 and 4.1, too) used to silently truncate data, or allow things like February 31 in a date field, or silently allow non-deterministic GROUP BY, or only reporting warnings when you explicitly asked for them really undermined the perception of MySQL to database folks. This wasn't that long ago, either.

Re:Leaving MySQL by rtpg on Monday December 06, 2021 @01:16AM (#29456752)
We had tried to do a logical replication-based strategy for Postgres to achieve no-downtime version upgrades during business hours, but there were so many gotchas and things you have to do on the side (stuff like sequence tracking etc), that we just gave up and took the downtime to do it more safely.

I think Postgres is really cool and has a lot of good reliability, but I've always wondered what people do for high availability. And yeah, pgbouncer being a thing bugs,

It feels like we're getting closer and closer to "vanilla pg works perfectly for the common worklfows" though, so tough to complain too much.

Re:Leaving MySQL by PikachuEXE on Monday December 06, 2021 @01:44AM (#29456920)
I have written an article about upgrading PSQL with no/low downtime with Logical Replication. (More like a note to my future self)

See if you can understand enough of it and consider doing it again for next upgrade. (I have done that for 12 > 13)

Re:Leaving MySQL by rtpg on Monday December 06, 2021 @03:08AM (#29457367)
So to be honest I see that article (linked below) and I think "Yeah that's probably all right", but given this is for a Real Sytem With Serious People's Data, when faced with "take the downtime and do the offline data upgrade that _basically everyone does_" and "try to use logical replication to get a new database in the right state, knowing that logical replication doesn't copy _everything_ so I need to manually copy some stuff, and if I miss stuff I'm in a bad situation"...

I trust the pg people that tools do what are advertised, but it's a very high risk proposition

Re:Leaving MySQL by psychoslave on Monday December 06, 2021 @02:32AM (#29457168)
Maybe you could give a link to the post
Re:Leaving MySQL by nikisweeting on Monday December 06, 2021 @02:31AM (#29457162)
Whats the link?
Re:Leaving MySQL by virgilp on Monday December 06, 2021 @02:57AM (#29457306)
Re:Leaving MySQL by PikachuEXE on Monday December 06, 2021 @09:22PM (#29468480)
This is what happen I don't have enough sleep...

I forgot to post the article link!

Thanks for posting it.

Re:Leaving MySQL by skunkworker on Monday December 06, 2021 @01:17AM (#29456759)
IMO logical replication is too slow for Production. At scale you'll be doing physical replication and rarely using logical replication as it is quite slow in comparison still (As of PG 13.2). Logical replication is helpful when doing a Postgres version upgrade primary/replica switch, but not useful many other places at the moment.

You do need to use an external connection pooler (pgbouncer etc), though Postgres 14 has made some large improvements in gracefully handling large numbers of connections, though you can still run into problems.

As for the query planner/optimizer, so far in all of the optimizations and improvements I have worked on, I've only run into 1 or 2 that had a query plan that made my head scratch. There are some extreme edge cases that will prompt the cost function to prioritize the wrong index, but in production I have found that 99%+ of slow queries can be easily improved by a rather simple composite index. One thing I do love about postgres is using partial indexes, which can significantly reduce the amount of space required and also make it extremely easy to create indexes to match predicates of a function, while indexing other columns.

Other than one or two slow queries I've tracked down and worked on, I've never wished that I could "hint" to use a particular index over another.

Now some of the things that people like for Postgres over Mysql, in practice aren't that great at the moment. People like doing atomic DDL operations, when in reality locking the table schema can cause lots of problems, and in production you only add indexes etc concurrently.

You still get the issue of dead tuples in mysql and need to periodically clean them up using OPTIMZE TABLE et al, though postgres and innodb have different designs, but ultimately it needs to happen sometime. Its just that postgres IMO requires more tuning to get the right balance.

Re:Leaving MySQL by mikepurvis on Monday December 06, 2021 @01:22PM (#29463546)
> If you keep things damn simple so you have confidence in efficiency and performance...

... then SQLite might be the right choice for you?

In all seriousness, so many use cases are met by SQLite on the low end, PostgreSQL on the high end, and dedicated KV stores like Elastic off to the side, that what is left for MySQL? When you have a write-heavy workload on a multi-TB dataset that needs to be sharded, with a schema that is "simple" but not so simple that it works with an actual KV store?

Re:Leaving MySQL by ploxiln on Monday December 06, 2021 @03:24PM (#29465117)
... unless I want logical replication, a few hundred concurrent connections, and a more strictly typed schema than a KV store ...
Re:Leaving MySQL by leetrout on Monday December 06, 2021 @10:34AM (#29460953)
> And, you can tell MySQL to use a particular index for a query!

I didn't know this was a thing or even common until this year and it opened my eyes to some major issues when the PG optimizer doesn't get a query right you have no escape hatch.

That being said, and me being weak in MySQL, it wasn't obvious to me if this is very common and how often you _have_ to tell MySQL what index to use because the optimizer misses it...

Re:Leaving MySQL by viraptor on Monday December 06, 2021 @11:56PM (#29469209)
> it opened my eyes to some major issues when the PG optimizer doesn't get a query right you have no escape hatch.

For me the biggest issue with that is that the optimizer decision can change basically at any time. If the stats start preferring one index over the other at 2am, you can't just say "no, trust me, use this one instead" - you have to find out exactly which value crossed some threshold and tweak the stats to restore the previous behaviour.

> it wasn't obvious to me if this is very common and how often you _have_ to tell MySQL what index to use because the optimizer misses it...

I'm not sure how representative my sample is, but in the last 20 years, I've seen a hint used two times and one of them was an app bug (the forced index was not better, the order of columns in it should've been reversed and the hint removed). But I haven't worked with super-complicated reporting queries.

Re:Leaving MySQL by dijit on Monday December 06, 2021 @02:44AM (#29457231)
> PostgreSQL has gotten built-in logical replication only recently, and it's still very annoying. It doesn't replicate schema change statements, so those have to be applied independently by some external system

I’ve been using PostgreSQL built-in replication since 9.1 (when it was introduced) and this is definitely not my experience....

In fact it wouldn’t be possible to apply out of replication changes because (unlike MySQL) Postgres replication forces immutability (the replica nodes cannot become r/w).

The "issue" could potentially be that it's async? But I use postgresql a _lot_ and I've never experienced this.

Re:Leaving MySQL by atombender on Monday December 06, 2021 @01:15PM (#29463470)
They're referring to logical replication, which is a relatively new feature.

The original mode of replication merely ships the WAL, which describes binary changes to data files. Every change comes in the form "set page 32435 in <file> to <bytes>", with no distinction between the operations that the changes represent.

Logical replication describes the changes logically. A change may be "insert <column values> into <table>" or "add <column name> of <type> to <table>". Since this format isn't tied to the underlying database file format, it offers greater compatibility between PostgreSQL versions. It also allows other applications to process the log and do things like listen for data changes and ingest them into other databases, queues, etc.

Re:Leaving MySQL by bigiain on Monday December 06, 2021 @12:36AM (#29456523)
> I swear people that cut their teeth on MySQL and have never used Postgres just don't know what they are missing

I seriously trialled and compared Postgres vs MySQL at the start of a major major project, and MySQL had a few clear wins for us (mainly replication) while the features Postgres had weren't in our current roadmap's requirements (the biggest regret that caused me was not having stored procedures). That was in 1998/1999. I now seem to be stuck on a career path where everything is MySQL and switching to Postgres anywhere seems to have huge back pressure from everyone I work with - even though at least half of them know damned well (like I do) that MySQL hasn't been the right choice for a couple of decades almost.

:shrug:

Re:Leaving MySQL by raffraffraff on Monday December 06, 2021 @04:06AM (#29457693)
I could tell a similar story from 2015. Worked at a MySQL shop where we had several big clusters with bi-directional replication between two "masters", and used failover between them if anything died. Also replicated to a 3rd node which was read only (used for huge queries, backups etc). Sounds ok, but our developers were not good at writing SQL or designing schemas, and our ops team didn't have any DBAs. So all of the problems were our own fault. But a new senior engineering hire told us that Postgres would fix everything because MySQL was a shitshow. He demonstrated amazing benchmarks on a specific database we were having issues with. But OMG once we tried to get replication working... Bloody awful. Major internal fighting ensued. Another engineer decided to build a clean MySQL and tune it using best practices, and demonstrated comparable performance to the Postgres demo. That was it for Postgres, and a major project kicked off to fix the MySQL problems (switch to innodb, turn on GTIDs, fix bad SQL that was breaking replication, use row based replication, better indexes, monitoring, actual metrics from the underlying hardware etc). Who'd have thought you had to spend time on your databases? Crazy right?
Re:Leaving MySQL by bigiain on Monday December 06, 2021 @06:36PM (#29467215)
Yeah. Interesting to see the replication results came out the same 15 years apart.

And I feel seen on the devs with no database skills thing. Every single database problem I can recall having to step in and help fix in the last ~5 years has been Java devs leaving everything up the the ORM to deal with, resulting in unindexed full table scans and similar brain damage. I "fixed" a production problem with a query that'd crept up to over 30 seconds without anybody noticing, by adding a single index to a single table. (And then sent the devs to work out how to give Hibernate the hints it needed to create the schema properly and set up more monitoring so once-weekly reporting SQL queries taking 10+ seconds wouldn't be hidden in 95 and 99 percentile alerting...)

Re:Leaving MySQL by txdv on Monday December 06, 2021 @01:26AM (#29456813)
Just sticking with your old horse, has its problems, but you wont get surprised by new ones
Re:Leaving MySQL by Spivak on Monday December 06, 2021 @10:29AM (#29460893)
And the fact that the community and tooling surrounding MySQL is absolutely bonkers massive. It's not that the grass has been trodden down to a path; no there's a 6 lane highway with a rest area and a Cracker Barrel.
Re:Leaving MySQL by leetrout on Monday December 06, 2021 @10:38AM (#29461020)
LOL amazing metaphor.

The one thing that I've learned recently which surprised me- if you want to cluster MySQL (via partitioned tables or PlanetScale, etc) you cannot use foreign keys.

Some of the Postgres compatible tooling (Citus, Cockroach, etc) appears to support foreign keys across partitions / shards.

I was quite surprised to see this. Makes sense that not allowing FKs makes the data easier to shard but I never imagined that would be a thing you would have to contend with.

Re:Leaving MySQL by aarondf on Monday December 06, 2021 @10:41AM (#29461068)
I also ran into this recently when looking at SingleStore! I was surprised when they told me I couldn't use foreign keys, and was even more surprised that they were surprised that I do use foreign keys. They acted like I was the only one. And to be fair, given their clientele, I might be the only one!
Re:Leaving MySQL by js4ever on Monday December 06, 2021 @02:06PM (#29464150)
At "scale" you just can't afford to use FK, it's indeed slowing down everything and preventing good and fast clustering / replication. For some use cases you just have to live with it
Re:Leaving MySQL by aarondf on Monday December 06, 2021 @02:23PM (#29464352)
Yup for sure, that's what they were saying. It makes sense, but I was still surprised by it. I've never had to manage something at this scale before, so it was news to me.

For reference, we're at about 250 million rows, so not even proper "scale" scale!

Re:Leaving MySQL by vvern on Wednesday December 08, 2021 @09:02AM (#29484762)
citus has some rather severe limitations around foreign keys. Namely, you can’t use them from any distributed tables. See https://docs.citusdata.com/en/v10.2/develop/reference_ddl.ht...
Re:Leaving MySQL by dotancohen on Monday December 06, 2021 @02:21AM (#29457104)

  > Yes Postgres ... has worse connection handling that usually requires a pooler
This is the issue. The vast majority of PHP (Wordpress runs ~30% of the internet or something) and Python (Django) devs are not going to write a database connection pooler. I happen to like PHP and love Python, but the low bar to entry on those two (PHP especially) means that the vast majority of devs would be completely lost without the default tooling provided by e.g. php-mysqlng or other prepackaged database connection modules in their default settings with the most trivial of use cases.

A slow database can be ignored for a while. The need to add additional code in which the database is no longer a black box can not be ignored.

Re:Leaving MySQL by makeitdouble on Monday December 06, 2021 @03:03AM (#29457327)
It usually comes down to setting up a pgbouncer in front of the DB, from there you can mostly ignore the pooling layer in your application.

It sure is one more step that for MySQL, but we're talking production level DB, and in my experience it's a small task compared to the rest of what you will be doing to have your architecture properly running.

Re:Leaving MySQL by dotancohen on Monday December 06, 2021 @05:20AM (#29458046)
But pgbouncer comes with it's own set of bugs and behaviours to be familiar with.

Just look at the changelog for the bugs fixed (and yes, there are unfixed bugs as well): https://www.pgbouncer.org/

And the FAQs for the unusual behaviours to be aware of and find workarounds for: https://www.pgbouncer.org/faq.html

I would love nothing more than to be convinced. I find MySQL to be adequate, but far from great. Postgres is described as great by everyone who uses it, however I prefer adequate but reliable over great but leads to downtime.

Re:Leaving MySQL by makeitdouble on Monday December 06, 2021 @08:22PM (#29468079)
On your main point, yes, you need to understand how pgbouncer works, it has its quirks and limitations. In particular a dev needs to be aware of how sessions and transactions work if they have some critical transactions. When introducing pgbouncer on one of our rails project we had to fine tune ActiveRecord to play nice, but once it's done there wasn't much to adjust from there.

I'm with on how much it would help if it had a magic mode without these quirks, but in my experience you're still ahead of MySQL and its per session configuration quirks for instance. On a day to day basis, you'll rarely be scratching your head about how to deal with the pgbouncer pooling behavior you chose, an you'll still have the option to directly hit the DB on anything you don't trust the pooler to deal with appropriately.

For the bugs, those are mostly on the security and configuration side. For the few years I've used it, I never hit a behavioral bug TBH.

Re:Leaving MySQL by dotancohen on Tuesday December 07, 2021 @01:30AM (#29469594)
Terrific, thank you
Re:Leaving MySQL by philliphaydon on Sunday December 05, 2021 @11:51PM (#29456286)
> Yes Postgres can be slower out of the box

Is it though? I read this post earlier this year and it mirrors some similar tests I did back in 2020 when I was just checking basic perf between PostgreSQL / SQL Server / MySQL on my laptop.

https://itnext.io/benchmark-databases-in-docker-mysql-postgr...

> A simple conclusion: Postgres engine family is about twice as fast as MySQL engine family, except MariaDB.

Also:

> Postgres has worse connection handling

Is this addressed in PG14? Or is it still to come in PG15? I haven't kept up with the latest Postgres stuff but I thought they were fixing the connection stuff.

Re:Leaving MySQL by luhn on Monday December 06, 2021 @01:09AM (#29456718)
> I haven't kept up with the latest Postgres stuff but I thought they were fixing the connection stuff.

pg14 improves the performance for large numbers (thousands) of connections. IIRC there is some work to add a primitive connection pooler as an option, but I can't find anything references on that ATM.

So the situation is improving, but the fundamental connection=process model is so baked into Postgres that it's unlikely to ever change.

Re:Leaving MySQL by remram on Sunday December 05, 2021 @11:57PM (#29456319)
Naive question (I only use PostgreSQL and SQLite): is there any point using MySQL over MariaDB nowadays?
Re:Leaving MySQL by evanelias on Monday December 06, 2021 @11:47AM (#29462080)
MySQL 8 has a lot of functionality that MariaDB lacks.

People like to criticize MySQL for some bad engineering decisions made decades ago. For whatever reason, often these folks have a more positive view of MariaDB, but without stopping to consider where all those original MySQL engineers went. FWIW one of the footnotes in the original article touches on this too.

Re:Leaving MySQL by throwaway984393 on Monday December 06, 2021 @12:44AM (#29456566)
Pretty much only three reasons: you want Enterprise support, you don't want to think about license terms, and you really want to help pay for Larry Ellison's personal island.
Re:Leaving MySQL by tehbeard on Monday December 06, 2021 @01:50AM (#29456948)
Last I checked mariaDB still misses some functiinality around JSON, and i don't know what the story is exactly about compat. With the newer X protocol and its collections.
Re:Leaving MySQL by takeda on Monday December 06, 2021 @01:29PM (#29463634)
> Is this addressed in PG14? Or is it still to come in PG15? I haven't kept up with the latest Postgres stuff but I thought they were fixing the connection stuff.

This is by design, postgresql doesn't come with a connection pooler. For each connection a separate process is started. If you have a lot of mostly idle connections then you should use use pooler in your application, server side (e.g. pgbouncer) or both (there are trade offs).

Re:Leaving MySQL by elktea on Monday December 06, 2021 @04:37AM (#29457822)
These are not typical workloads for databases, and I don't think very useful
Re:Leaving MySQL by mastax on Monday December 06, 2021 @12:02AM (#29456347)
Pretty surprised about the substantial difference between MariaDB and MySQL.
Re:Leaving MySQL by leetrout on Monday December 06, 2021 @12:22AM (#29456458)
Maria has some additional, interesting settings around replication I saw this week.

Optimistic mode for parallel in-order replication

https://mariadb.com/kb/en/parallel-replication/#optimistic-m...

Re:Leaving MySQL by bigiain on Monday December 06, 2021 @12:43AM (#29456561)
Heh.

I was at the Perl conference in maybe 1999 or 2000 where there was some guy giving a MySQL talk. It wasn't Monty, but he was in the audience. I asked a question during question time about enabling ssl for encrypted replication, and the guy giving the talk said "Sorry, I don't know if/when that'll happen". Monty stood up and said "I've just done that. I'll check it in tonight, come grab me out in the corridor if you'd like me to mail you the patch."

I then spent the next 7-8 years running replicated MySQL databases in SF/Dallas/London/Singapore/Sydney. It worked really well for us.

Re:Leaving MySQL by evanelias on Monday December 06, 2021 @11:55AM (#29462210)
All this is showing is that the default settings for MariaDB Docker image are a better fit for this person's particular machine specs than the default settings for the MySQL Docker image. IMO it's not really a useful comparison at all, for any real-world use-case whatsoever.
Re:Leaving MySQL by takeda on Monday December 06, 2021 @11:26AM (#29461712)
When working as an SRE every place that I worked at that used MySQL had at least one situation where MySQL database got corrupted itself without any damaging events (i.e. no broken hardware, no disk filling up (but even that shouldn't corrupt data), no machine crash or hard restart).
Re:Leaving MySQL by leetrout on Tuesday December 07, 2021 @07:46PM (#29480097)
This exact situation keeps me up at night.

Fear of a db not coming back up in a restart and backups being corrupt as well.

We pull our backups into new instances daily and I think that would catch it.

Re:Leaving MySQL by cortesoft on Monday December 06, 2021 @02:00PM (#29464076)
> I swear people that cut their teeth on MySQL and have never used Postgres just don't know what they are missing.

Yeah, I am one of those people. Needed a database in 2005, and MySQL was the de facto choice. Got used to it and never ran into problems that couldn’t be solved by getting better at schema design and indexing.

I never felt limited by MySQL and I am very comfortable with it, so never felt the need to try anything else. I might be missing something, but there is an opportunity cost in switching without a real motivating reason.

Re:Leaving MySQL by toast0 on Sunday December 05, 2021 @11:42PM (#29456237)
Vaccuuming sounds much more pleasant than dealing with innodb without file per table?
Re:Leaving MySQL by leetrout on Monday December 06, 2021 @12:33AM (#29456508)
Yes I would rather have the ability / requirement to vacuum than have unused space in large table files especially one large innodb file.
Re:Leaving MySQL by toast0 on Monday December 06, 2021 @02:26AM (#29457130)
Unless MySQL changed it since I last looked, the one large innodb file is a big PITA, but otherwise you can do OPTIMIZE TABLE to compact tables; apparently this can sometimes be done in place, but I'm used to it writing to a new file so you'd need room for old and new; and at least for MyISAM locks the table for the whole time, so I hope you've got a way to manage that too, but that's about the same as VACUUM FULL.
Re:Leaving MySQL by xd on Monday December 06, 2021 @02:38AM (#29457199)
Innodb can be split into individual per table files and has had this ability for years.
Re:Leaving MySQL by toast0 on Monday December 06, 2021 @03:04AM (#29457339)
Yeah, but the default used to be one file for everything, and it's hard to split things after you started with that. Especially if your data is sizable.
Re:Leaving MySQL by evanelias on Monday December 06, 2021 @11:38AM (#29461920)
The default innodb_file_per_table changed to ON in 5.6, released nearly 9 years ago.

It's not really that hard to change if you started with OFF, it just requires a logical dump and reload (typically on a replica, followed by some re-cloning and promotion). Companies with sizable data tend to have database engineers who can handle this fairly easily.

In any case, it's not an urgent maintenance operation like a VACUUM problem...

Re:Leaving MySQL by leetrout on Monday December 06, 2021 @01:32PM (#29463677)
It's not nearly the problem now it was ~6 years ago when I was stuck with 8TB in a single file at a small startup.

I look around now and you a right. It's really no big deal to move a few terabytes of data around but it sure was a mess back then and I'm once bitten, twice shy.

Re:Leaving MySQL by evanelias on Monday December 06, 2021 @02:52PM (#29464690)
Having been in a similar situation 11 years ago, from my POV it was waaay easier 6 years ago than it was back then ;)

I was fortunate that the startup was willing to devote sufficient time and resources to help me get our DB problems under control -- including getting expert consulting services from Percona, attending conferences, etc. However I've definitely seen some other startups that didn't take DB tech debt seriously enough, and just kicked the can down the road until a major disaster occurred.

Anyway though, even aside from improvements in hardware and tooling, managing large DBs just gets less stressful once you've spent some time on the problem. Assuming you successfully solved this 6 years ago, why shy away from it in the future? You almost certainly learned a lot in the process, and the good news is it only gets easier from there!

Re:Leaving MySQL by jayflux on Monday December 06, 2021 @12:19AM (#29456444)
> Yes Postgres can be slower out of the box

Do you have an up to date source for that?

Re:Leaving MySQL by throwdbaaway on Monday December 06, 2021 @03:29AM (#29457503)
Not just out of the box. There is a good comment at https://news.ycombinator.com/item?id=7488498 that listed a few key features of InnoDB:

1. Change Buffering - If your benchmark result shows that MySQL is faster than PostgreSQL, it is likely due to change buffering. For random read/write workload that doesn't fit into the memory, this feature is fantastic. This is available since the very first commit of InnoDB, and of course also has been causing random corruption during crash recovery throughout the years: https://jira.mariadb.org/browse/MDEV-24449 (fixed in MariaDB, still exists in MySQL)

2. Page-level Compression - PostgreSQL still doesn't support this, but I am fine with relying on ZFS filesystem compression when running either MySQL or PostgreSQL. Arguably it should be better for the database engine to do its own compression, but see this blocker level bug that impacted FreeBSD: https://jira.mariadb.org/browse/MDEV-26537 (MariaDB only, but feel free to search bugs.mysql.com for other compression related bugs)

3. Clustered Index - The main topic in the Uber drama. I think InnoDB has the better design, since scaling write is harder for a RDBMS. I also think that InnoDB implementation causes a severe performance cliff, when it comes to optimistic update versus pessimistic update.

Re:Leaving MySQL by dspillett on Monday December 06, 2021 @06:09AM (#29458276)
> Page-level Compression ... but I am fine with relying on ZFS filesystem compression

There are compromises there. With page level compression the pages usually remain compressed in memory as well as on-disk and get unpacked on access (and repacked on change). This eats CPU time, but saves RAM as well as disk space which for some workloads is a beneficial trade-off as a larger common working set fits into the same amount of active memory (particularly when IO is slow, such as traditional spinning disks, cloud providers when you aren't paying a fortune, or when competing with a lot of IO or network contention on a busy LAN/SAN).

With filesystem compression you don't get the same CPU hit every time the page is read, and depending on your data you may get better compression for a couple of reasons, but you don't get the RAM saving.

Re:Leaving MySQL by throwdbaaway on Monday December 06, 2021 @06:26PM (#29467125)
ZFS ARC actually works the same way, i.e. the records would remain compressed in memory.

When running PostgreSQL, I would probably go with `primarycache=all` for the data dir, a largish ARC cache, and a smallish shared buffers, to take advantage of the filesystem compression.

When running MySQL, I would probably go with `primarycache=metadata` for the data dir, a smallish ARC cache, and a largish buffer pool, to still benefit (slightly) from the filesystem compression.

Re:Leaving MySQL by Izkata on Monday December 06, 2021 @01:35AM (#29456860)
IIRC it's because some of the default settings were chosen back when RAM was measured in MB and low GB and not really updated. work_mem, for example, can typically be safely set to several times its default of 4MB, which then allows individual operations to use more memory before spilling over into temporary files on disk.

Of course, that particular example really only matters if you're actually hitting the 4MB limit in your queries, but I think there were some other settings with similar too-low defaults.

Re:Leaving MySQL by fuy on Monday December 06, 2021 @03:04AM (#29457333)
It's because the default Postgres configuration is very basic and targeted at low-end hardware. Besides, in Postgres you have to tune some of the settings that other databases take care of automagically (work_mem being probably the trickiest one).

So the first thing you do after setting up Postgres on a reasonable hardware is tune the config file. There's a lot of advice on the web, and there's pgtune service that makes it very easy to start in the right direction (https://pgtune.leopard.in.ua). Still, you need to know about it to do it, so there's definitely quite a lot of suboptimal Postgres installations out there.

Re:Leaving MySQL by dspillett on Monday December 06, 2021 @06:01AM (#29458237)
That isn't unique to pg though: just look at the number of performance questions in dba.stackexchange and on web hosting forums that, once you take out those that turn out to be bad queries and/or non-existent/inappropriate indexes, come down to tuning certain settings in my.ini.

I've not used either for a long time so my knowledge may be out of date, but mysql definitely used to be the easiest to get started out of the box which was part of the reason it won out in the shared hosting space. Of course the main reason it won out in that area was because it was faster than pg in many artificial benchmarks because at the time it didn't bother with silly things like transactions (as MyISAM was the default table type), and foreign key constraints, and other necessities…

Re:Leaving MySQL by leetrout on Monday December 06, 2021 @12:30AM (#29456493)
Not on my phone and i'm headed to bed but I can go educate myself in the morning.

Maybe its all better now?

Re:Leaving MySQL by johnmato on Monday December 06, 2021 @11:06AM (#29461425)
I have worked with MySQL and every database has its ups and downs.
Re:Leaving MySQL by mayli on Monday December 06, 2021 @12:58AM (#29456643)
Thanks, but no, I don't like slower and 1 process per connection.