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

21–30 of 121 posts

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

#21

Post by leandrod »

collyw wrote: post #19
outside1234 wrote: post #15

The best part of moving to PostgreSQL is that it is sort of the defacto standard wire protocol for relational databases now and that enables you to "grow into" something like CockroachDB down the line when you need that sort of scale.

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.

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

#22

Post by bobviolier »

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 also can't do multi-region on Google Cloud SQL (MySQL). The only thing you can do is run your replica on your own VM and configure it to read from the Master in Google Cloud SQL.

(Or I am completely missing something)

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

#23

Post by tonyarkles »

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.

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

#24

Post by cwyers »

kd5bjo wrote: post #14
cwyers wrote: post #10

This strikes me as bike shedding. If you have a specific problem that you believe will be solved by the migration, do it. Otherwise, users will benefit more if you focus on changes based on their value, not what part of your tech stack looks better on Reddit.

If you have a specific problem that you believe will be solved by the migration, seriously investigate the possibility of migrating . This is a big change, and it's likely to both solve and create problems; take it slow and steady, and keep your abort options open as long as possible.

This is a fair response.

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

#25

Post by ansible »

collyw wrote: post #19
outside1234 wrote: post #15

The best part of moving to PostgreSQL is that it is sort of the defacto standard wire protocol for relational databases now and that enables you to "grow into" something like CockroachDB down the line when you need that sort of scale.

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".

In the case of CockroachDB (a distributed database), it has the same wire-level protocol as Postgresql. So if your programming language has a Postgresql driver (and it likely does), then you can also talk to a CDB database as well.

The SQL dialects are not the same, though they are similar, so you will have to go through your code to see if you need to fix anything. Moving the data itself over is fairly straight-forward:

https://www.cockroachlabs.com/docs/stable/migrate-from-postg...

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

#26

Post by mey »

collyw wrote: post #19
outside1234 wrote: post #15

The best part of moving to PostgreSQL is that it is sort of the defacto standard wire protocol for relational databases now and that enables you to "grow into" something like CockroachDB down the line when you need that sort of scale.

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".

Not parent. No idea about the defacto standard comment. CockroachDB client protocol is* compatible with PostgreSQL. Edit: as well as SQL syntax, that's the important bit.

Meaning you could in theory mirror data to CockroachDB and the point your clients to it instead of PostgreSQL. CockroachDB is aiming to solve horizontal scaling, hence the "grow" comment.

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

#27

Post by ars »

I'm made the jump, it's not better exactly, it just has a different set of problems.

For example there is no unsigned or tinyint, advisory locks in PG are much worse than those in MySQL, true and false "t", "f" instead of "1", "0", and some other things.

Yes, each item can be dealt with - but it's like I said, it's just a different set of problems.

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

#28

Post by toxik »

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.

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

#29

Post by [deleted] »

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.

[deleted]

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

#30

Post by matthewmacleod »

h1d wrote: post #20
Cymen wrote: post #13

I have on all my personal projects but I wouldn't bother with an existing project unless I had a specific need that PostgreSQL met (and MySQL did not). I'm actually all in on PostgreSQL at this point -- I'm not bothering with trying to use vanilla SQL and worrying about switching databases but I have that luxury on my side projects. PostgreSQL has a lot of interesting features that I am happy to use and I frankly nev…

Are you talking about ORM? ORM is such a waste of time to learn as you switch to another language at one point, your time spent on ORM is gone and you don't even learn the underlying tech that is SQL. Stick with SQL and you can keep that skill mostly the same across different SQL products. And obviously you will sooner or later hit some performance penalty when you start writing complex ORM and ORM starts spitting ou…

This is bad advice. An ORM is a useful tool for smoothing over the generation of queries and their conversion into object graphs, and make sense for many use cases.

It is true that using them effectively still requires knowing the underlying database technology, but that is a given - they are not meant to supplant that knowledge.

I will say though that every time I have encountered a codebase developed by a fanatically anti-ORM developer, it has contained a bug-riddled implementation of a half of an application-specific ORM anyway…

Post reply on HN