6 ms·

Looking forward to playing around with this. Native master-master replication is the only thing keeping me on MySQL.

dcosson 10y ago
Just curious, what Postgres features are you missing on MySQL? I had only used MySQL until a year or two ago, and wondered what I was missing since Postgres seems to get more love/hype from the developer community for whatever reason. Now using Postgres in production, there are few if any features that I notice our team using which don't exist in MySQL (maybe Json landed in Postgres first is one big one?). One thing I have noticed is I find the user/permissions model for Pg less intuitive. It's as if it's designed for use in a computer lab or something where there's one human who is the owner/dba and some things can only be done by them, which doesn't map well to a web app trying to follow "principle of least privilege". This combined with the fact that we're on RDS where MySQL/Aurora is the clear first class citizen makes me wish we were using MySQL.
- allan_s 10y ago
  transactional DDL => if your application often has schema update and you use a tool like Doctrine for PHP / Alembic for python, when a downgrade or upgrade fail on MySQL in the middle became the create index was already taken by someone who "hot-fixed" the database and that now you're in an inconsistent state and you have to clean stuff by hand you will regret to not be on PostgreSQL where it will have simply rollback the transaction, leaving you in a consistent state, you fix the migration, you hit again the command and you can go back home hstore/jsonb/array/composite types https://www.postgresql.org/docs/8.1/static/rowtypes.html https://www.postgresql.org/docs/8.1/static/rowtypes.html : array is often a good option to implement tag system partial index: imagine you have a lot of "soft deleted" rows (i.e with a flag deleted turned to true), you can create an index that ignore them index on expression: you often do request like "where date = today" , but you store timestamp precise to the second ? and you don't want to run date_trunc(your_column, 'day') , which it also a function not present in mysql..., everytime, nor you want to create a dedicated column for that only for the sake of performance, index on expression permit you to do that. integrated full text search: you have a smallteam, and you don't feel like maintaining one more service for indexing and keeping in sync your search engine, here you are (of course it's not perfect but better than the option provided by MySQL) table inheritance for partionning: you create one table "orders" , and you can easily partion them into "orders_2016" "orders_2015" etc. while still simply selecting things out of "orders" constraints: your column "event_start" must be before "event_end", you can enforce that at the table level in PostgreSQL text columns: in PostgreSQL don't worry with varchar(XXX) with XXX being the subject to flamewars (256 ? 500 ? 1000), the type "text" in postgresql is up to 2Go and as efficient as varchar() uuid support: postgresql support uuid natively as primary keys (without needing to resort to a varchar ofcourse...) And I've only talking about the advantage of PostgreSQL, not the strange defect of MySQL: for example that you can only have 1 column with a default timestamp, that your autoicrements will overflow silently taking back previous ids , a lot of things are only "warnings" (value not in an enum, fine I will insert null) that you will not see in your application code except if you really look hard for it. Edit: I've used MySQL extensively and only started for now 2 years to use PostgreSQL, and though I have more knowledge in MySQL optimization and internals, and I don't consider it "bad", it's just 'so so', you will definitely be able to do whatever you want with it and it will not betray you hard, but PostgreSQL is just from an other league and will actively help you.
  - dcosson 10y ago
    Great list, thanks for the thorough response
  - evanelias 10y ago
    A few things from this list are out of date. For example, MySQL 5.6 (GA release 3.5 years ago) added support for multiple columns with default timestamp. MySQL 5.7 (GA release 1 year ago) added generated virtual columns, which can be indexed, basically supporting index on expression. And for an extreme example, table partitioning was added in MySQL 5.1, 8 years ago. The default SQL mode also changed to be strict in 5.7, preventing silent overflows and other oft-complained-about write behaviors. Using a strict SQL mode has been recommended best practice for quite a long time anyway. MySQL certainly has its flaws, and there are a number of useful features that are present in pg but missing in MySQL. But the gap is smaller than many people realize.
  - ngrilly 10y ago
    A bunch of other relevant features of PostgreSQL: - table elimination/join removal (MariaDB supports it but MySQL doesn't) - asynchronous protocol (the new X Protocol supports it but not the standard protocol) - table functions (for example the generate_series function which is very useful in PostgreSQL) - LISTEN/NOTIFY (a built-in pub-sub mechanism) - WITH (Common Table Expressions) - Window aggregations (very useful for reporting and stats) - Row Level Security (introduced in PostgreSQL 9.5; very useful for multi-tenant applications)
- developer2 10y ago
  utf8 collations. mysql's utf8mb4_unicode_ci is fantastic for real-world multi-language support. postgresql's utf8 charsets require you to attach a language-specific collation. It makes real-world applications impossible to support on potgres.
- shabda 10y ago
  JSONFields for me. I want to use Aurora, but lack of JSONField makes it non-starter.
- lmm 10y ago
  MySQL has a bunch of irritating gotchas, most of which can be configured around or worked around, but they're just unpleasant by default. E.g. mysql "latin1" refers to a non-standard characterset that has only half its characters in common with the characterset commonly known as latin-1 (this is documented, but to find that documentation you have to first consider the posibility that "latin1" doesn't simply mean "latin1"). Likewise "utf8" refers to a non-standard characterset that has only 1/16th of its characters in common with the charcterset commonly known as UTF-8. A lot of its defaults are table-engine-specific, but the default table engine is set per-server, and even if you specify the engine you may silently get a table with a different engine (if the server doesn't have the specified engine enabled then it will use the default engine rather than giving an error). It uses non-standard quoting characters and non-standard table name equivalence by default. I can't remember what exactly went wrong with its timezone support but I remember it was bad enough that we didn't use it. A lot of its functions/columns will allow invalid values by default rather than failing to insert.

Just curious, what Postgres features are you missing on MySQL? I had only used MySQL until a year or two ago, and wondered what I was missing since Postgres seems to get more love/hype from the developer community for whatever reason. Now using Postgres in production, there are few if any features that I notice our team using which don't exist in MySQL (maybe Json landed in Postgres first is one big one?). One thing I have noticed is I find the user/permissions model for Pg less intuitive. It's as if it's designed for use in a computer lab or something where there's one human who is the owner/dba and some things can only be done by them, which doesn't map well to a web app trying to follow "principle of least privilege". This combined with the fact that we're on RDS where MySQL/Aurora is the clear first class citizen makes me wish we were using MySQL.

allan_s 10y ago
transactional DDL => if your application often has schema update and you use a tool like Doctrine for PHP / Alembic for python, when a downgrade or upgrade fail on MySQL in the middle became the create index was already taken by someone who "hot-fixed" the database and that now you're in an inconsistent state and you have to clean stuff by hand you will regret to not be on PostgreSQL where it will have simply rollback the transaction, leaving you in a consistent state, you fix the migration, you hit again the command and you can go back home hstore/jsonb/array/composite types https://www.postgresql.org/docs/8.1/static/rowtypes.html https://www.postgresql.org/docs/8.1/static/rowtypes.html : array is often a good option to implement tag system partial index: imagine you have a lot of "soft deleted" rows (i.e with a flag deleted turned to true), you can create an index that ignore them index on expression: you often do request like "where date = today" , but you store timestamp precise to the second ? and you don't want to run date_trunc(your_column, 'day') , which it also a function not present in mysql..., everytime, nor you want to create a dedicated column for that only for the sake of performance, index on expression permit you to do that. integrated full text search: you have a smallteam, and you don't feel like maintaining one more service for indexing and keeping in sync your search engine, here you are (of course it's not perfect but better than the option provided by MySQL) table inheritance for partionning: you create one table "orders" , and you can easily partion them into "orders_2016" "orders_2015" etc. while still simply selecting things out of "orders" constraints: your column "event_start" must be before "event_end", you can enforce that at the table level in PostgreSQL text columns: in PostgreSQL don't worry with varchar(XXX) with XXX being the subject to flamewars (256 ? 500 ? 1000), the type "text" in postgresql is up to 2Go and as efficient as varchar() uuid support: postgresql support uuid natively as primary keys (without needing to resort to a varchar ofcourse...) And I've only talking about the advantage of PostgreSQL, not the strange defect of MySQL: for example that you can only have 1 column with a default timestamp, that your autoicrements will overflow silently taking back previous ids , a lot of things are only "warnings" (value not in an enum, fine I will insert null) that you will not see in your application code except if you really look hard for it. Edit: I've used MySQL extensively and only started for now 2 years to use PostgreSQL, and though I have more knowledge in MySQL optimization and internals, and I don't consider it "bad", it's just 'so so', you will definitely be able to do whatever you want with it and it will not betray you hard, but PostgreSQL is just from an other league and will actively help you.
- dcosson 10y ago
  Great list, thanks for the thorough response
- evanelias 10y ago
  A few things from this list are out of date. For example, MySQL 5.6 (GA release 3.5 years ago) added support for multiple columns with default timestamp. MySQL 5.7 (GA release 1 year ago) added generated virtual columns, which can be indexed, basically supporting index on expression. And for an extreme example, table partitioning was added in MySQL 5.1, 8 years ago. The default SQL mode also changed to be strict in 5.7, preventing silent overflows and other oft-complained-about write behaviors. Using a strict SQL mode has been recommended best practice for quite a long time anyway. MySQL certainly has its flaws, and there are a number of useful features that are present in pg but missing in MySQL. But the gap is smaller than many people realize.
- ngrilly 10y ago
  A bunch of other relevant features of PostgreSQL: - table elimination/join removal (MariaDB supports it but MySQL doesn't) - asynchronous protocol (the new X Protocol supports it but not the standard protocol) - table functions (for example the generate_series function which is very useful in PostgreSQL) - LISTEN/NOTIFY (a built-in pub-sub mechanism) - WITH (Common Table Expressions) - Window aggregations (very useful for reporting and stats) - Row Level Security (introduced in PostgreSQL 9.5; very useful for multi-tenant applications)
developer2 10y ago
utf8 collations. mysql's utf8mb4_unicode_ci is fantastic for real-world multi-language support. postgresql's utf8 charsets require you to attach a language-specific collation. It makes real-world applications impossible to support on potgres.
shabda 10y ago
JSONFields for me. I want to use Aurora, but lack of JSONField makes it non-starter.
lmm 10y ago
MySQL has a bunch of irritating gotchas, most of which can be configured around or worked around, but they're just unpleasant by default. E.g. mysql "latin1" refers to a non-standard characterset that has only half its characters in common with the characterset commonly known as latin-1 (this is documented, but to find that documentation you have to first consider the posibility that "latin1" doesn't simply mean "latin1"). Likewise "utf8" refers to a non-standard characterset that has only 1/16th of its characters in common with the charcterset commonly known as UTF-8. A lot of its defaults are table-engine-specific, but the default table engine is set per-server, and even if you specify the engine you may silently get a table with a different engine (if the server doesn't have the specified engine enabled then it will use the default engine rather than giving an error). It uses non-standard quoting characters and non-standard table name equivalence by default. I can't remember what exactly went wrong with its timezone support but I remember it was bad enough that we didn't use it. A lot of its functions/columns will allow invalid values by default rather than failing to insert.