News for hackers. Stuff that matters.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by blowski on Saturday February 27, 2021 @07:04AM (#26284302)
I spent a whole decade saying "Why do I need Postgres? MySQL is fine."

Started using Postgres a couple of years ago, and I now can't believe I ever lived without window functions, native arrays, custom types, etc.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by tpetry on Saturday February 27, 2021 @09:46AM (#26285162)
The best part is transactional ddl statements. You can do your database migration in a transaction, if something fails the transaction is rolled back compared to an invalid state with mysql.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by chousuke on Saturday February 27, 2021 @10:19AM (#26285361)
Being used to PostgreSQL, this one actually bit me once during a production upgrade. A database migration failed due to a Galera transaction size limit that I unfortunately hadn't caught testing the migrations on a single database, and I had to restore from the pre-upgrade backup before resolving the issue and continuing. It wasn't a major issue (the upgrade finished well within the acceptable downtime window), but until then I had assumed that the migrations would be transactional because of course they should be, it's a database!

Now I know better than to assume everything you do in a database is transactional. :P

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by spuz on Saturday February 27, 2021 @10:30AM (#26285431)
Which database are you talking about? Postgres or Mysql?
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by llarsson on Saturday February 27, 2021 @10:34AM (#26285466)
Something that happened when they were on MySQL, before PostgreSQL.

The phrasing at the start and "Galera" mention shows this.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by derekperkins on Saturday February 27, 2021 @01:05PM (#26286716)
That was added a couple years ago in MySQL 8, as were window functions and arbitrary checks. The two databases continue to fill in the gaps that were historically reasons to choose one over the other.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by tpetry on Sunday February 28, 2021 @03:02AM (#26291557)
Mysql 8 supports atomic ddl, which means if you drop two tables in a single DROP TABLE query either both are deleted or none. But it does not support postgresql‘s transactional ddl feature where you can start a transaction change the structure of multiple tables, change some data etc. and commit at the end.

Mysql 8 males ddl changes atomic, but does not support transactional mode where you could rollback them.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by paulryanrogers on Saturday February 27, 2021 @01:23PM (#26286872)
Are Mysql 8 DDL changes transactional across tables? If not then in what sense are they transactional?

Because to this day I still have to deal with locking problems unless my changes are within a narrow window: last column, no on-update or on-delete, not changing type, etc.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by darksaints on Saturday February 27, 2021 @09:41AM (#26285128)
Postgres was my first, and the Postgres docs were foundational for someone like me.

Tried MySQL a couple years later, and every day I used it I found a new reason to never use it again.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by axegon_ on Saturday February 27, 2021 @10:28AM (#26285423)
If you liked the docs, you should definitely check out the source code. It's not just poetry by C standards, it's poetry by Shakespearean standards. Hands down the best code I've ever seen. What makes it more astonishing is that it's a product of relatively small community scattered around the globe.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by spiralx on Saturday February 27, 2021 @03:37PM (#26287925)
I remember reading the MySQL developers justifications for why transactions weren't important along with articles such as these back in the 00s

https://sql-info.de/mysql/gotchas.html

https://fromdual.com/mysql-limitations

and deciding a database that treats 1/0 as NULL and allows inserting February 31st as a valid date wasn't worth bothering with.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by bombcar on Saturday February 27, 2021 @07:45AM (#26284511)
I’ve wanted to try post geese but have never really had a chance - everything I do is “prepackaged” and things like Wordpress or Confluence really don’t seem to care if it is MySQL or Postgres.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by noir_lord on Saturday February 27, 2021 @08:03AM (#26284603)
You should definitely have a gander.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by rrauenza on Saturday February 27, 2021 @10:16AM (#26285323)
Especially at the possibilities of a lack of down time.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by dismalpedigree on Saturday February 27, 2021 @08:17AM (#26284653)
This really goosed my energy levels this morning!
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by blowski on Saturday February 27, 2021 @08:08AM (#26284626)
I feel that pain! I knew MySQL so well that it felt risky to use a different database, yet if I used it on a non-serious project, how would I get real-world experience?

I learned a lot from a book from one of the core contributors to Postgres - https://theartofpostgresql.com/. It has actual real world examples with realistic datasets to experiment with.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by Proziam on Saturday February 27, 2021 @08:14AM (#26284644)
I wish this resource existed (or that I knew it existed if it did) years ago.

I always seem to learn about the things that would have made my life easier after I've already done things the hard way.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by denysvitali on Saturday February 27, 2021 @08:22AM (#26284687)
No pain no gain I guess. Learning things in a difficult way is not a waste of time tbh
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by fakedang on Sunday February 28, 2021 @07:04AM (#26292789)
There is often little distinction between difficult and suboptimal though.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by mmcgaha on Saturday February 27, 2021 @09:38AM (#26285114)
Arrays in Postgres are my guilty pleasure. I know I shouldn’t use them but I just cannot help myself.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by lucian1900 on Saturday February 27, 2021 @09:45AM (#26285150)
They’re great for denormalisation, which is often an appropriate trade off.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by bitexploder on Saturday February 27, 2021 @09:52AM (#26285193)
Even storing JSON blobs isn’t the hit it used to be. It’s not relational, but you can index on fields in the JSON and query it effectively. The Postgres array and other types are great for stuff at the edges of your database or when you know you won’t need to build relations into or on that data. RDBMS like Postgres really provide a powerful, powerful technology for managing data.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by colanderman on Saturday February 27, 2021 @10:04AM (#26285255)
Yes! Have one-to-many data that you know you will usually always grab all in one go, and won't ever participate in a relation? Arrays are the way to go. JSONB can be similarly highly appropriate. They can greatly reduce the number of disk reads needed for certain workloads.

Don't forget that both can be indexed in Postgres! And the indexes are more powerful than what you can do with the equivalent relational layout, as they support efficient subset queries.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by sk5t on Saturday February 27, 2021 @01:03PM (#26286700)
Same goes for hstore and the @> style operators.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by EdwardDiego on Saturday February 27, 2021 @11:50PM (#26290779)
I've recently used them to replace a many to many relation from table A to table B where the primary query was "give me all As associated with B", so the new table had a schema of unique B, Array<A>, so what was a 20ish second query over several hundred million rows became a 0.3 second query over 600K rows.

We did have a use case that wanted to find Bs for a given A, and using a GIN index on the array column along with the PG array contains operator served that up remarkably fast also.

Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by dcposch on Saturday February 27, 2021 @12:58PM (#26286660)
MySQL has window functions and native JSON now.
Re:Why Uber Engineering Switched from Postgres to MySQL (2016) by nkozyra on Saturday February 27, 2021 @08:53AM (#26284818)
My experience as well. I first got the itch when mysql lagged on postgis. Found so many features that have saved me so much time. More sensible defaults, too.