5 ms·

I carried out a (quite ill-advised in retrospect) migration from MySQL to Postgres on a mature codebase with absolutely no automated test coverage and it went p

by munk-a 5y ago

I carried out a (quite ill-advised in retrospect) migration from MySQL to Postgres on a mature codebase with absolutely no automated test coverage and it went pretty smoothly. We had some bumpy performance directly after the production migration but we were able to reach parity after about two months of observing and adding targeted indices. I absolutely adore Postgres's tweaks to the SQL language, that dialect is amazing and has served us extremely well. And, since we passed pg10 a while back, the performance tuning you can do on it is pretty amazing.

lucideer 5y ago
I am very much an example of one of those previously MySQL devs that now uses Postgres for new projects, though I've never done a migration between the two.
code_duck 5y ago
I had an app with a fairly complex and large set of tables and data and migrated it from mySQL to pg with very little trouble. It was a few years back, but from what I recall a re-wrote a few queries in my data layer and that’s about it. Much easier than it was to setup nginx to take over some Apache duties.
tapoxi 5y ago
We also did a MySQL 5.7 to PG 9.6 migration (thanks to https://pgloader.io/ https://pgloader.io/) and we got some pretty immediate speed improvements (2-5x). We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.
- dijit 5y ago
  pgloader is great. Sadly for me when I tried to make a PoC of Postgres for our eCommerce platform I found that we had violated our constraints and lost data. That was the day I went from MySQL agnostic to MySQL hating. It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default.
  - evanelias 5y ago
    > It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default. Everyone agrees with this. That's why they changed the default nearly 6 years ago. Yes, the old default was terrible. But there's no way to change the past, so why complain about it for years and years? All versions with this bad default have hit end-of-life, what more can be done?
twic 5y ago
> I absolutely adore Postgres's tweaks to the SQL language Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or something else? The reason i ask is that i also enjoy writing SQL for PostgreSQL, but as far as i know, i am sticking to standard SQL. Perhaps there are things i'm missing, or things i like which i haven't realised are nonstandard!
- eatonphil 5y ago
  MySQL does not conform to the standard in a number of ways. On the one hand, thankfully you can turn on ANSI MODE. On the other hand if you're using MySQL you've probably got a bunch of devs used to their non-ANSI SQL and turning on ANSI MODE may break stuff they do. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
- jacobmischka 5y ago
  I assume they mean non-standard additions in PostgreSQL. A lot of the very handy pg functions are non-standard, as well as the rich text matching capabilities, many data types, and even the RETURNING clause, I believe.
- felixfbecker 5y ago
  There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS. But it's true that PSQL sticks very closely to the standard or only adds on top of it, while MySQL is very uncomformant. Funnily enough, I actually first used Postgres over MySQL a long time ago when I moved from PHP to NodeJS, and was annoyed that MySQL uses backticks for identifiers while PSQL uses SQL standard double quotes, because in JS multi-line strings (for SQL queries) are also use backticks and I was annoyed by escaping. Sometimes it's the little things...
  - petergeoghegan 5y ago
    It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation. You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking. I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.

by munk-a 5y ago

lucideer 5y ago

I am very much an example of one of those previously MySQL devs that now uses Postgres for new projects, though I've never done a migration between the two.

code_duck 5y ago

I had an app with a fairly complex and large set of tables and data and migrated it from mySQL to pg with very little trouble. It was a few years back, but from what I recall a re-wrote a few queries in my data layer and that’s about it. Much easier than it was to setup nginx to take over some Apache duties.

tapoxi 5y ago

We also did a MySQL 5.7 to PG 9.6 migration (thanks to https://pgloader.io/ https://pgloader.io/) and we got some pretty immediate speed improvements (2-5x). We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.

dijit 5y ago
pgloader is great. Sadly for me when I tried to make a PoC of Postgres for our eCommerce platform I found that we had violated our constraints and lost data. That was the day I went from MySQL agnostic to MySQL hating. It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default.
- evanelias 5y ago
  > It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default. Everyone agrees with this. That's why they changed the default nearly 6 years ago. Yes, the old default was terrible. But there's no way to change the past, so why complain about it for years and years? All versions with this bad default have hit end-of-life, what more can be done?

twic 5y ago

> I absolutely adore Postgres's tweaks to the SQL language Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or something else? The reason i ask is that i also enjoy writing SQL for PostgreSQL, but as far as i know, i am sticking to standard SQL. Perhaps there are things i'm missing, or things i like which i haven't realised are nonstandard!

eatonphil 5y ago
MySQL does not conform to the standard in a number of ways. On the one hand, thankfully you can turn on ANSI MODE. On the other hand if you're using MySQL you've probably got a bunch of devs used to their non-ANSI SQL and turning on ANSI MODE may break stuff they do. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
jacobmischka 5y ago
I assume they mean non-standard additions in PostgreSQL. A lot of the very handy pg functions are non-standard, as well as the rich text matching capabilities, many data types, and even the RETURNING clause, I believe.
felixfbecker 5y ago
There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS. But it's true that PSQL sticks very closely to the standard or only adds on top of it, while MySQL is very uncomformant. Funnily enough, I actually first used Postgres over MySQL a long time ago when I moved from PHP to NodeJS, and was annoyed that MySQL uses backticks for identifiers while PSQL uses SQL standard double quotes, because in JS multi-line strings (for SQL queries) are also use backticks and I was annoyed by escaping. Sometimes it's the little things...
- petergeoghegan 5y ago
  It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation. You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking. I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.