5 ms·

Woohoo I've been eagerly awaiting this. I use redshift postgres and SQL server at work and looking to migrate from postgres to MySQL for my personal ML projects

Woohoo I've been eagerly awaiting this. I use redshift postgres and SQL server at work and looking to migrate from postgres to MySQL for my personal ML projects. Window functions have been a long time coming and I personally can't wait!

orf 8y ago
There are a lot of postgres fans on HN, would you mind giving some more info on why you want to go from PG to MySQL?
- superice 8y ago
  There are some nice features that MySQL lacks. Personally, the thing I like best in Postgres is transactional DDL. In MySQL, if halfway through a database transformation your migration script fails, you are in a broken state and you are on your own. Postgres rolls the whole transaction back. Ofcourse there are some other features MySQL lacks, such as variable length character columns (varchar) where you don't need to specify a maximum length. The transactional DDL seems to be introduced in MySQL per 8.0 though
  - madenine 8y ago
    The advantages of Postgres (and MariaDB) over MySQL are pretty well traveled on HN. OP wants to move from Redshift Postgres & SQL Server to MySQL - which isn't something you see very often.
  - semiquaver 8y ago
    > The transactional DDL seems to be introduced in MySQL per 8.0 though I thought so as well from the release note wording, thanks to theptip for confirming in the docs that DDL is not transactional in 8.0, just crash-safe. see https://news.ycombinator.com/item?id=16876549 https://news.ycombinator.com/item?id=16876549.
- cdevnull 8y ago
  I did a project with postgres and symfony+doctrine. At 80% of completion, I moved to mySql because I needed spatial index, I installed Postgis but got a lot of problems with doctrine-migration.
- boznz 8y ago
  No explanation he could possibly give on HN will prevent him being down voted. Sad but true
trizinix 8y ago
As far as I am aware there is no good reason to not use MariaDB instead of MySQL. The creator of MariaDB, Monty Widenius, was on a episode of the changelog podcast if you want to hear some background as to why.
- morgo 8y ago
  Former MySQL Product Manager here (no longer affiliated). I've been to a Monty talk about this subject, and let me say it was interesting... Monty starts by saying that he does not acknowledge MySQL 5.6's existence since some new features are an abomination (usually citing GTIDs). He then compares MySQL 5.5 to MariaDB (of which MariaDB is based on MySQL 5.5; so it contains the superset of features). MySQL 5.5 was released in 2010. Support for it will end at the end of this year. Development has diverged a lot since 2010. For a more balanced comparison, I recommend this one: https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/ https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-rea...
- Murrawhip 8y ago
  We switched to MariaDB on freebsd 2 or 3 years ago. We ran into a couple performance-destroying bugs and while the devs acknowledged the issues, they made it clear they wouldn't be fixed any time soon. MySQL has been solid for us though. Just a personal experience.
- evanelias 8y ago
  Monty is also the CTO of the MariaDB Corporation, which is a VC-funded entity. I would fully expect him to sing MariaDB's praises in a podcast. Large companies have come to a different conclusion, at least so far. Among companies with very large database fleets, there's much greater usage of MySQL and Percona Server than MariaDB. Granted, Linux distributions have leaned more towards shipping MariaDB by default instead of MySQL. This makes sense given that Oracle is, well, Oracle.
purerandomness 8y ago
Redshift is forked off an ancient Postgres version, consider using a more recent (10 or 11 beta) real Postgres version.
- Something1234 8y ago
  Like AWS redshift the nosql database or what? What is redshift postgres?
  - reilly3000 8y ago
    AWS Redshift is their data warehouse database. It uses Postgres syntax. DynamoDB is their NoSQL offering.
  - jdub 8y ago
    Redshift is a columnar SQL database, based on an old version of PostgreSQL.
OldSchoolJohnny 8y ago
You want to go from Postgres TO MySQL‽ Why do you want to do that?
- dumbfounder 8y ago
  https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
  - manigandham 8y ago
    Uber really just needed a key/value store. MySQL works better when used that way since PostgreSQL has MVCC and can lead to table bloat and compaction issues. I doubt many others ever encounter this issue since picking the correct data store is step 1.
    - evanelias 8y ago
      InnoDB also has MVCC. Always has. It just uses a different strategy to avoid the table bloat and compaction issues.

orf 8y ago

There are a lot of postgres fans on HN, would you mind giving some more info on why you want to go from PG to MySQL?

superice 8y ago
There are some nice features that MySQL lacks. Personally, the thing I like best in Postgres is transactional DDL. In MySQL, if halfway through a database transformation your migration script fails, you are in a broken state and you are on your own. Postgres rolls the whole transaction back. Ofcourse there are some other features MySQL lacks, such as variable length character columns (varchar) where you don't need to specify a maximum length. The transactional DDL seems to be introduced in MySQL per 8.0 though
- madenine 8y ago
  The advantages of Postgres (and MariaDB) over MySQL are pretty well traveled on HN. OP wants to move from Redshift Postgres & SQL Server to MySQL - which isn't something you see very often.
- semiquaver 8y ago
  > The transactional DDL seems to be introduced in MySQL per 8.0 though I thought so as well from the release note wording, thanks to theptip for confirming in the docs that DDL is not transactional in 8.0, just crash-safe. see https://news.ycombinator.com/item?id=16876549 https://news.ycombinator.com/item?id=16876549.
cdevnull 8y ago
I did a project with postgres and symfony+doctrine. At 80% of completion, I moved to mySql because I needed spatial index, I installed Postgis but got a lot of problems with doctrine-migration.
boznz 8y ago
No explanation he could possibly give on HN will prevent him being down voted. Sad but true

trizinix 8y ago

As far as I am aware there is no good reason to not use MariaDB instead of MySQL. The creator of MariaDB, Monty Widenius, was on a episode of the changelog podcast if you want to hear some background as to why.

morgo 8y ago
Former MySQL Product Manager here (no longer affiliated). I've been to a Monty talk about this subject, and let me say it was interesting... Monty starts by saying that he does not acknowledge MySQL 5.6's existence since some new features are an abomination (usually citing GTIDs). He then compares MySQL 5.5 to MariaDB (of which MariaDB is based on MySQL 5.5; so it contains the superset of features). MySQL 5.5 was released in 2010. Support for it will end at the end of this year. Development has diverged a lot since 2010. For a more balanced comparison, I recommend this one: https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/ https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-rea...
Murrawhip 8y ago
We switched to MariaDB on freebsd 2 or 3 years ago. We ran into a couple performance-destroying bugs and while the devs acknowledged the issues, they made it clear they wouldn't be fixed any time soon. MySQL has been solid for us though. Just a personal experience.
evanelias 8y ago
Monty is also the CTO of the MariaDB Corporation, which is a VC-funded entity. I would fully expect him to sing MariaDB's praises in a podcast. Large companies have come to a different conclusion, at least so far. Among companies with very large database fleets, there's much greater usage of MySQL and Percona Server than MariaDB. Granted, Linux distributions have leaned more towards shipping MariaDB by default instead of MySQL. This makes sense given that Oracle is, well, Oracle.

purerandomness 8y ago

Redshift is forked off an ancient Postgres version, consider using a more recent (10 or 11 beta) real Postgres version.

Something1234 8y ago
Like AWS redshift the nosql database or what? What is redshift postgres?
- reilly3000 8y ago
  AWS Redshift is their data warehouse database. It uses Postgres syntax. DynamoDB is their NoSQL offering.
- jdub 8y ago
  Redshift is a columnar SQL database, based on an old version of PostgreSQL.

OldSchoolJohnny 8y ago

You want to go from Postgres TO MySQL‽ Why do you want to do that?

dumbfounder 8y ago
https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
- manigandham 8y ago
  Uber really just needed a key/value store. MySQL works better when used that way since PostgreSQL has MVCC and can lead to table bloat and compaction issues. I doubt many others ever encounter this issue since picking the correct data store is step 1.
  - evanelias 8y ago
    InnoDB also has MVCC. Always has. It just uses a different strategy to avoid the table bloat and compaction issues.