6 ms·

I'm thinking of using Postgres for a project, but a DBA friend told me operationally it's more challenging than MySQL. Unfortunately, he can't elaborate. Does a

I'm thinking of using Postgres for a project, but a DBA friend told me operationally it's more challenging than MySQL. Unfortunately, he can't elaborate. Does anyone have real work experience? Or is it based on outdated "PG must manually vacuum frequently"?

agustif 5y ago
Are you going to operate it our just rent out some cloud service? Postgres by itself doesn't have a great horitzontal scaling strategy as of now I think. You need Citus or somt like that on top, maybe your friend was referencing that?
yannoninator 5y ago
perhaps your DBA friend was operating PG themselves? nowadays postgres in the cloud does all of this for you.
unnouinceput 5y ago
Your DBA friend is stuck in 2000's. Let dinosaurs die and you go with PGSQL because is superior to MySQL on everything. And don't take my word for it, see for yourself here: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems https://en.wikipedia.org/wiki/Comparison_of_relational_datab... And MySQL is an Oracle product these days, go with MariaDB instead as this one is a MySQL fork made by the original papa of MySQL.
- tfigment 5y ago
  Lacks first class temporal tables. Maybe not important to you and not on that list so do we dismiss that.
CapriciousCptl 5y ago
You can fiddle with the autovacuum daemon[1,2] but we've never really had to. These days we just run AWS RDS when it counts or a dedicated VPS when it doesn't and things go fine-- [1,2] https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/current/planner-stats.html https://www.postgresql.org/docs/current/planner-stats.html The main issue we get is the 1 connection = 1 process issue although there are ways to mitigate that (namely pgbouncer).
ComputerGuru 5y ago
One thing is that Postgres doesn’t let you just upgrade to a new major version, as it doesn’t update the format of the on-disk binary database files; you must replicate from an existing node or dump/restore. MySQL upgrades the previous version when a new version is installed (which can cause problems, but is certainly “easier”).
- andruby 5y ago
  Pg_upgrade [0] is an official part of postgres and does the binary inplace upgrade for you. You should obviously test before running in production, but it has worked perfectly for us when upgrading a 10+TB cluster from pg11 to pg13 [0] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
  - jeffbee 5y ago
    10TB is basically zero. At that scale you can choose anything and get away with it.
    - tinus_hn 5y ago
      10 tb is definitely not zero if your project is of the ‘should I choose MySQL or Postgres’ variety.
    - tehbeard 5y ago
      Honestly this just seems like you're waving your e-peen. Care to give some more details around why and what size of disk usage cause issues for postgres / other db upgrades?
    - lilSebastian 5y ago
      A wonderfully constructive comment that keep people using this site, or not.
    - darkwater 5y ago
      How can someone with 10k karma write such a statement? Were you trolling? Are you serious? 10TB of relational database can be definetly troublesome to manage and pose challenges to many organizations.
masklinn 5y ago
I don’t know if there are distros which tune it, but the default configuration is usually… conservative.
offtop5 5y ago
I wouldn't imagine postgres is really much harder than MySQL. Both are a degree more difficult than NoSQL. The main issue is maintaining schema's
sigzero 5y ago
MySQL is for that aren't really DBAs and don't want to be (this doesn't mean DBAs don't use it). It's a lot easier to manage.
keeperofdakeys 5y ago
Postgres has some disadvantages that can pop up on certain workloads (eg. bloat) but so does MySQL. And most of those limitations are only when you've got long open transactions, trying to hammer it IO wise, or you're making really big databases (100GB-1TB or more). However for both Postgres and MySQL there is plenty of documentation about these problems, and how to resolve them. So you'll never be "stuck" with issues. In general I find postgres "just works" a lot more than MySQL. MySQL has a really bad habit of sticking with bad defaults for a long period, while having better configuration available. On the other hand postgres devs actively remove/change defaults so you're always getting the best it has to offer. If you pick one, and you don't like it there are plenty of tools to change between them. If you're curious you could even deploy both of them.

agustif 5y ago

Are you going to operate it our just rent out some cloud service? Postgres by itself doesn't have a great horitzontal scaling strategy as of now I think. You need Citus or somt like that on top, maybe your friend was referencing that?

yannoninator 5y ago

perhaps your DBA friend was operating PG themselves? nowadays postgres in the cloud does all of this for you.

unnouinceput 5y ago

Your DBA friend is stuck in 2000's. Let dinosaurs die and you go with PGSQL because is superior to MySQL on everything. And don't take my word for it, see for yourself here: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems https://en.wikipedia.org/wiki/Comparison_of_relational_datab... And MySQL is an Oracle product these days, go with MariaDB instead as this one is a MySQL fork made by the original papa of MySQL.

tfigment 5y ago
Lacks first class temporal tables. Maybe not important to you and not on that list so do we dismiss that.

CapriciousCptl 5y ago

You can fiddle with the autovacuum daemon[1,2] but we've never really had to. These days we just run AWS RDS when it counts or a dedicated VPS when it doesn't and things go fine-- [1,2] https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/current/planner-stats.html https://www.postgresql.org/docs/current/planner-stats.html The main issue we get is the 1 connection = 1 process issue although there are ways to mitigate that (namely pgbouncer).

ComputerGuru 5y ago

One thing is that Postgres doesn’t let you just upgrade to a new major version, as it doesn’t update the format of the on-disk binary database files; you must replicate from an existing node or dump/restore. MySQL upgrades the previous version when a new version is installed (which can cause problems, but is certainly “easier”).

andruby 5y ago
Pg_upgrade [0] is an official part of postgres and does the binary inplace upgrade for you. You should obviously test before running in production, but it has worked perfectly for us when upgrading a 10+TB cluster from pg11 to pg13 [0] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
- jeffbee 5y ago
  10TB is basically zero. At that scale you can choose anything and get away with it.
  - tinus_hn 5y ago
    10 tb is definitely not zero if your project is of the ‘should I choose MySQL or Postgres’ variety.
  - tehbeard 5y ago
    Honestly this just seems like you're waving your e-peen. Care to give some more details around why and what size of disk usage cause issues for postgres / other db upgrades?
  - lilSebastian 5y ago
    A wonderfully constructive comment that keep people using this site, or not.
  - darkwater 5y ago
    How can someone with 10k karma write such a statement? Were you trolling? Are you serious? 10TB of relational database can be definetly troublesome to manage and pose challenges to many organizations.

masklinn 5y ago

I don’t know if there are distros which tune it, but the default configuration is usually… conservative.

offtop5 5y ago

I wouldn't imagine postgres is really much harder than MySQL. Both are a degree more difficult than NoSQL. The main issue is maintaining schema's

sigzero 5y ago

MySQL is for that aren't really DBAs and don't want to be (this doesn't mean DBAs don't use it). It's a lot easier to manage.

keeperofdakeys 5y ago

Postgres has some disadvantages that can pop up on certain workloads (eg. bloat) but so does MySQL. And most of those limitations are only when you've got long open transactions, trying to hammer it IO wise, or you're making really big databases (100GB-1TB or more). However for both Postgres and MySQL there is plenty of documentation about these problems, and how to resolve them. So you'll never be "stuck" with issues. In general I find postgres "just works" a lot more than MySQL. MySQL has a really bad habit of sticking with bad defaults for a long period, while having better configuration available. On the other hand postgres devs actively remove/change defaults so you're always getting the best it has to offer. If you pick one, and you don't like it there are plenty of tools to change between them. If you're curious you could even deploy both of them.