Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Anyone made the jump from MySQL to PostreSQL? It is worth it?

old.reddit.com

Post reply on HN
Print view View original

31–40 of 121 posts

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#31

Post by brightball »

I'm a huge PG fan but switching your database on an existing project is a non-small undertaking.

If you're starting from scratch, then yes, you take PG every time. There's a lot...a lot that goes into saying that and if you haven't made the jump, a lot of the reasons aren't going to seem important because you currently "don't do" the things that PG lets you do with your data.

Like this: https://www.brightball.com/articles/why-should-you-learn-pos...

So the surface level stuff that you're experiencing with your database is going to be MySQL relative.

Probably the one that connects the most with the people who are working with MySQL is that you no longer have to worry about TEXT, MEDIUMTEXT, LONGTEXT.

Without really thinking about it, most people and ORM's just use TEXT and every now and then you'll get some data to put into the field that doesn't fit...and it won't generate an error it will just truncate the data. You'll end up needing to go and increase the field size wherever the problem happens (and then hope it doesn't happen again).

With PostgreSQL it's just TEXT. Done. Doesn't matter how big or small it is, it will fit the whole document. If the data is larger than 8kb, it will be transparently compressed with TOAST.

I've tested by dropping in a 2mb XML document and it stored as 80kb.

That's just a tiny piece though.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#32

Post by 0815test »

toxik wrote: post #28
IMTDb wrote: post #9

If your project is already on MySQL and you have no issue => Don't change anything If your project is already on MySQL and you have issues => understand the issues you are facing and make sure that moving to Postgres would fix them (99% chance it won't) If you have a new project and have very precise informations about the constraint you will face (pretty rare) => Do your research and choose what's best for your use…

I think MySQL smells like poor engineering, and PostgreSQL like good engineering. That’s why my gut says PostgreSQL is the better choice, for the myriad issues you won’t face. It’s like Python vs PHP, in some ways.

> ...It’s like Python vs PHP, in some ways.

Uh, did you notice where this was posted? I don't exactly disagree, but it's kinda ironic to raise that point.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#34

Post by pmontra »

tonyarkles wrote: post #23

So there’s some nice things about Postgres, but by far the thing I miss the most when I go back to other systems (incl. MySQL/Maria) is transactional DDL. Knowing that a bad migration isn’t going to leave my DB in a half-changed fucked up state is worth it.

This and window functions and safety of data. I'm using MySQL and PostgreSQL in different projects (customers choice) and sometimes there are things I can't do in the MySQL one or would be much more complicated.

Sometimes MySQL silently slips bad data in the db because it truncates strings that don't fit varchars or those impossible 0000-00-00 00:00:00 dates. To be fair, the latter are not much of a problem.

I'd go PostgreSQL all the time but I'd be very wary about migrating a production database. It could be long and risky.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#35

Post by wgjordan »

leandrod wrote: post #21
collyw wrote: post #19

Earlier quoted context omitted.

Can you explain your comment in more detail as I am not really sure what you getting at with "the defacto standard wire protocol for relational databases" nor "grow into" something like CockroachDB".

PostgreSQL underlies at least a plurality of SQL DBMS developments nowadays, specially for scale-out (distributed) projects.

Any sources on that assertion? A quick bit of Google searching [1] shows me only ~10% market share for PostgreSQL, compared to ~46% for MySQL.

[1] https://www.datanyze.com/market-share/databases/postgresql-v...

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#36

Post by HugThem »

If you don't need transactions, then MySQL with the MyISAM engine has several aspects I love:

1: Blazingly fast. As it simply hands DB writes to the OS and benefits from all the caching and optimization the OS does. Depending on your storage medium, insert/updates/deletes often are 100x faster then with an ACID compliant engine.

2: Simple data structure. Want to see the sizes of your tables? "ls /var/lib/mysql/your_database" and you see each table as two files. One for the data, one for the indexes.

3: Easy to copy the data "cp -r /var/lib/mysql/your_database somewhere" and you are done.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#38

Post by cwyers »

pmontra wrote: post #34
tonyarkles wrote: post #23

So there’s some nice things about Postgres, but by far the thing I miss the most when I go back to other systems (incl. MySQL/Maria) is transactional DDL. Knowing that a bad migration isn’t going to leave my DB in a half-changed fucked up state is worth it.

This and window functions and safety of data. I'm using MySQL and PostgreSQL in different projects (customers choice) and sometimes there are things I can't do in the MySQL one or would be much more complicated. Sometimes MySQL silently slips bad data in the db because it truncates strings that don't fit varchars or those impossible 0000-00-00 00:00:00 dates. To be fair, the latter are not much of a problem. I'd go P…

MySQL now has window functions:

https://mysqlserverteam.com/mysql-8-0-2-introducing-window-f...

And MySQL has strict mode, which I believe is enabled by default on newer versions but available in any 5.x version:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#39

Post by luhn »

bitlax wrote: post #12

Here's some discussion from when Uber switched back from PostgreSQL to MySQL: https://news.ycombinator.com/item?id=12166585 FWIW I prefer PostgreSQL.

The linked article is a good dive into the internals of Postgres vs MySQL.

However, Uber didn't really switch from Postgres to MySQL, they switched from Postgres to their home rolled document database which uses MySQL as the storage layer. [1]

[1] https://eng.uber.com/schemaless-part-one/

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#40

Post by merb »

bhouston wrote: post #2

We use PostreSQL. We ran into the issue with Google Cloud SQL (PostreSQL-flavored) that we can not make it multi-region, only multi-zone. With Google Cloud SQL (MySQL-flavored) you can do multi-region. I would feel much better if we had multi-region failover with PostreSQL just in case a Google Data Center region (which contains the multi-zones) goes completely down (as it did in early June 2019.) We have some manual…

you can't do multi-region failover with mysql either. btw. multi-region postgres is also not that easy either.

i think the easiest solution would probably to maintain multiple clusters and put something in front of them, like pgpool and write to them simulatiously.

if one write fails rollback all clusters. and hope the read replica of cluster x will take over. probably something like pgpool + postgres cluster on k8s(zalando, crunchy).

at the moment there is no ledger that can span multiple regions. so your on your own for that.

if people really need multi-region they should probably just buy citus data.

Post reply on HN