6 ms·

I haven't worked with databases in a while, at my employer we are moving to MariaDB (from MySQL) - is there some reason why we wouldn't be considering PostgreSQ

I haven't worked with databases in a while, at my employer we are moving to MariaDB (from MySQL) - is there some reason why we wouldn't be considering PostgreSQL? Is there some drawback to P?

jeffdavis 9y ago
Technology choices are complex, so there are always some plausible reasons. But at this point, I think PostgreSQL should be the default choice for most people for new systems, and then move away if you have some real problem with it. The chance of regret is a lot lower with postgres.
- ngrilly 9y ago
  I concur. I was hit (again) today by a choice of MySQL made a few years ago: https://medium.com/@ngrilly/dont-waste-your-time-with-mysql-full-text-search-61f644a54dfa https://medium.com/@ngrilly/dont-waste-your-time-with-mysql-....
deleted 9y ago
[deleted]
fusiongyro 9y ago
Migrating to Postgres will certainly be significantly more work than going to MariaDB for you. But I would still recommend you consider it, because Postgres is safer, more powerful and has fewer edge-case behaviors. If your systems are highly-wedded to MySQL's idiosyncrasies, the difference in cost may be too high for you to do it right now, but I would seriously consider it.
ngrilly 9y ago
PostgreSQL is really better at executing arbitrarily complex queries, and the documentation is so much better. PostgreSQL also provides additional features like LISTEN/NOTIFY, Row Level Security, transactional DDL, table functions (like generate_series), numbered query parameters ($1 instead of ?), and many others. In my opinion, the only reasons for choosing MySQL/MariaDB are if you absolutely need clustered indexes (also known as index organized tables — PostgreSQL uses heap organized tables) or if you architecture relies on specific MySQL replication features (for example using Vitess to shard your database).
- SomeHacker44 9y ago
  Transactional DDL, by the way, is one of those things that once you use it, you can't imagine how you lived without it.
YorickPeterse 9y ago
Not so much a reason to not use it, but something to keep in mind: queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB. This doesn't necessarily mean they're always slower, but it's something you should take into account. Another thing to take into account is that updating between minor versions (major versions per 10.x) is a bit tricky since IIRC the WAL format can change. This means that upgrading from e.g. 10.x to 11.0 requires you to either take your cluster offline, or use something like pg_logical. This is really my only complaint, but again it's not really a reason to _not_ use PostgreSQL.
- ZitchDog 9y ago
  With logical replication in PG 10 the wal issue shouldn't be a factor going forward :)
  - snuxoll 9y ago
    Here's hoping someone writes an alternative to pg_upgrade to handle this automatically. Hell, I'd be willing to throw some money in.
- ngrilly 9y ago
  > queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB As far as I know, that's true when you use the MyISAM storage engine (which is non transactional), but not when you use InnoDB (which has been the default for years now).
- wolf550e 9y ago
  `select count(star)` is fast only on non-transactional MyISAM storage engine which locks the whole table for a single writer and has no ACID support. It's not what people usually mean when they say they want an RDBMS. When ACID support is required, you use InnoDB, and that has the same `select count(star)` performance as other database engines. What workload do you have that you need exact count of rows in a big table? Because if the table is not big or if inexact count would suffice, there are solutions (e.g. HyperLogLog).
- 9y ago
ComputerGuru 9y ago
While the other comments talk about the benefits of switching to pgsql (and I concur whole heartedly) it seems no one has addressed your specific case. Your company isn’t really “switching” to anything, MariaDB was a fork of MySQL when the license kerfuffle was going on and there were issues with the stewardship of the project. It’s more of upgrading to a newer release of MySQL than switching to a different database engine. i.e. “switching” to MariaDB is probably just a sysadmin upgrading the software and no changes to your code or database queries (unless replication is involved) but pgsql will certainly require more involved changes to the software.
snuxoll 9y ago
There's a drawback with any choice of technology, but PostgreSQL's are pretty well known. Replication can be a bit of a pain to set up compared to anything in the MySQL family since the tooling to manage it isn't part of the core project (there are tools out there, like repmgr from 2ndQuadrant). Similar story with backups, bring your own tooling - again, 2ndQuadrant has a great solution with barman, there's also WAL-E if you want to backup to S3 along with many others. Uber certainly presented a valid pain-point with the way indexes are handled compared to the MySQL family, any updates to an indexed field require an update to all indexes on the table (compared to MySQL which uses clustered indexes, as a result only specific indexes need to be updated). If you have a lot of indexes on your tables and update indexed values frequently you're going to see an increase in disk I/O. Someone else can probably come up with a more exhaustive list, but the first two are things I've personally been frustrated with - even with the tooling provided by 2ndQuadrant I still have to admit other solutions (namely Microsoft SQL Server) have better stories around replication and backup management, though the edge is in user-friendly tooling and not so much underlying technology. On the other hand, PostgreSQL has a lot of great quality of life features for database developers. pl/pgsql is really great to work with when you need to do heavy lifting in the database; composite types, arrays and domains are extremely useful for complex models and general manipulation; full-fat JSON support can be extremely useful for a variety of reasons, as can the XML features; PostGIS is king when it comes to spatial data; and a whole hell of a lot more. PostgreSQL is hands-down my favorite database because it focuses on making my life, when wearing the database developer hat, a lot nicer. With the DBA hat on it complicates things some compared to other products, but the tooling out there is at least decent so it's not a huge deal.
- jimktrains2 9y ago
  Regarding replication, the logical replication in pg10 should make this significantly easier. Between that and "Quorum Commit for Synchronous Replication" I'm really excited to see how these pan out in production. Pg doesn't normally hype or talk about (or let into production) things that don't work.
  - snuxoll 9y ago
mathnode 9y ago
MariaDB is a great choice over mysql, any day. MariaDB compared to postgres is a difficult choice, both have a multitude of features. MariaDB 10 was a game changer for the mysql landscape. To make postgres 10 as easy to scale as mariadb 10, and for query semantics in mariadb 10 to allow for the complexity that postgres 10 allows; both require tradeoffs in management tooling, development practices and understanding. Neither of them are a silver bullet. They both require effort, like any RDBMS. But both are great choices.
- shlomi-noach 9y ago
  > MariaDB 10 was a game changer for the mysql landscape Can you please elaborate in what respect?
  - mathnode 9y ago
    It's not Oracle for starters Shlomi. You should know that ...

I haven't worked with databases in a while, at my employer we are moving to MariaDB (from MySQL) - is there some reason why we wouldn't be considering PostgreSQL? Is there some drawback to P?

jeffdavis 9y ago

Technology choices are complex, so there are always some plausible reasons. But at this point, I think PostgreSQL should be the default choice for most people for new systems, and then move away if you have some real problem with it. The chance of regret is a lot lower with postgres.

ngrilly 9y ago
I concur. I was hit (again) today by a choice of MySQL made a few years ago: https://medium.com/@ngrilly/dont-waste-your-time-with-mysql-full-text-search-61f644a54dfa https://medium.com/@ngrilly/dont-waste-your-time-with-mysql-....

deleted 9y ago

[deleted]

fusiongyro 9y ago

Migrating to Postgres will certainly be significantly more work than going to MariaDB for you. But I would still recommend you consider it, because Postgres is safer, more powerful and has fewer edge-case behaviors. If your systems are highly-wedded to MySQL's idiosyncrasies, the difference in cost may be too high for you to do it right now, but I would seriously consider it.

ngrilly 9y ago

PostgreSQL is really better at executing arbitrarily complex queries, and the documentation is so much better. PostgreSQL also provides additional features like LISTEN/NOTIFY, Row Level Security, transactional DDL, table functions (like generate_series), numbered query parameters ($1 instead of ?), and many others. In my opinion, the only reasons for choosing MySQL/MariaDB are if you absolutely need clustered indexes (also known as index organized tables — PostgreSQL uses heap organized tables) or if you architecture relies on specific MySQL replication features (for example using Vitess to shard your database).

SomeHacker44 9y ago
Transactional DDL, by the way, is one of those things that once you use it, you can't imagine how you lived without it.

YorickPeterse 9y ago

Not so much a reason to not use it, but something to keep in mind: queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB. This doesn't necessarily mean they're always slower, but it's something you should take into account. Another thing to take into account is that updating between minor versions (major versions per 10.x) is a bit tricky since IIRC the WAL format can change. This means that upgrading from e.g. 10.x to 11.0 requires you to either take your cluster offline, or use something like pg_logical. This is really my only complaint, but again it's not really a reason to _not_ use PostgreSQL.

ZitchDog 9y ago
With logical replication in PG 10 the wal issue shouldn't be a factor going forward :)
- snuxoll 9y ago
  Here's hoping someone writes an alternative to pg_upgrade to handle this automatically. Hell, I'd be willing to throw some money in.
ngrilly 9y ago
> queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB As far as I know, that's true when you use the MyISAM storage engine (which is non transactional), but not when you use InnoDB (which has been the default for years now).
wolf550e 9y ago
`select count(star)` is fast only on non-transactional MyISAM storage engine which locks the whole table for a single writer and has no ACID support. It's not what people usually mean when they say they want an RDBMS. When ACID support is required, you use InnoDB, and that has the same `select count(star)` performance as other database engines. What workload do you have that you need exact count of rows in a big table? Because if the table is not big or if inexact count would suffice, there are solutions (e.g. HyperLogLog).
9y ago

ComputerGuru 9y ago

While the other comments talk about the benefits of switching to pgsql (and I concur whole heartedly) it seems no one has addressed your specific case. Your company isn’t really “switching” to anything, MariaDB was a fork of MySQL when the license kerfuffle was going on and there were issues with the stewardship of the project. It’s more of upgrading to a newer release of MySQL than switching to a different database engine. i.e. “switching” to MariaDB is probably just a sysadmin upgrading the software and no changes to your code or database queries (unless replication is involved) but pgsql will certainly require more involved changes to the software.

snuxoll 9y ago

There's a drawback with any choice of technology, but PostgreSQL's are pretty well known. Replication can be a bit of a pain to set up compared to anything in the MySQL family since the tooling to manage it isn't part of the core project (there are tools out there, like repmgr from 2ndQuadrant). Similar story with backups, bring your own tooling - again, 2ndQuadrant has a great solution with barman, there's also WAL-E if you want to backup to S3 along with many others. Uber certainly presented a valid pain-point with the way indexes are handled compared to the MySQL family, any updates to an indexed field require an update to all indexes on the table (compared to MySQL which uses clustered indexes, as a result only specific indexes need to be updated). If you have a lot of indexes on your tables and update indexed values frequently you're going to see an increase in disk I/O. Someone else can probably come up with a more exhaustive list, but the first two are things I've personally been frustrated with - even with the tooling provided by 2ndQuadrant I still have to admit other solutions (namely Microsoft SQL Server) have better stories around replication and backup management, though the edge is in user-friendly tooling and not so much underlying technology. On the other hand, PostgreSQL has a lot of great quality of life features for database developers. pl/pgsql is really great to work with when you need to do heavy lifting in the database; composite types, arrays and domains are extremely useful for complex models and general manipulation; full-fat JSON support can be extremely useful for a variety of reasons, as can the XML features; PostGIS is king when it comes to spatial data; and a whole hell of a lot more. PostgreSQL is hands-down my favorite database because it focuses on making my life, when wearing the database developer hat, a lot nicer. With the DBA hat on it complicates things some compared to other products, but the tooling out there is at least decent so it's not a huge deal.

jimktrains2 9y ago
Regarding replication, the logical replication in pg10 should make this significantly easier. Between that and "Quorum Commit for Synchronous Replication" I'm really excited to see how these pan out in production. Pg doesn't normally hype or talk about (or let into production) things that don't work.
- snuxoll 9y ago

mathnode 9y ago

MariaDB is a great choice over mysql, any day. MariaDB compared to postgres is a difficult choice, both have a multitude of features. MariaDB 10 was a game changer for the mysql landscape. To make postgres 10 as easy to scale as mariadb 10, and for query semantics in mariadb 10 to allow for the complexity that postgres 10 allows; both require tradeoffs in management tooling, development practices and understanding. Neither of them are a silver bullet. They both require effort, like any RDBMS. But both are great choices.

shlomi-noach 9y ago
> MariaDB 10 was a game changer for the mysql landscape Can you please elaborate in what respect?
- mathnode 9y ago
  It's not Oracle for starters Shlomi. You should know that ...