4 ms·

I've worked with Postgres, MySQL, Oracle, and SQL Server, and Postgres is my go-to choice. I can think of a few cons though: - Replication is harder to set up

I've worked with Postgres, MySQL, Oracle, and SQL Server, and Postgres is my go-to choice. I can think of a few cons though: - Replication is harder to set up than MySQL. And there is no master-master replication like in MySQL. (Of course it also doesn't lose your data.... :-) - I wish there were support for SQL:2011's bitemporal stuff. (There are some tools/extensions/published patterns to add "transaction-time" aka "system-time" support, but none I know of for "valid-time" aka "application-time", let alone both at once.) But this is very niche and not really fair to expect so quickly from a free product. - I wish I could share a database between separate users, give them each their own schema, and not let them do `\dn` to see what other schemas exist. Again, very niche. . . . Okay I thought I would have a longer list but I've run out of ideas. :-) - EDIT: One more. You can't do `UPDATE ... ORDER BY ...`. This mattered to me once when I had multiple multi-row UPDATEs running at the same time, and if they touched the same rows but in different order, they would deadlock. You could solve that with `ORDER BY id` so that everyone obtained locks in the same order. Apparently this is not a big enough problem for anyone else in the world to care though. I think the lesson here is that there are no real gotchas, just small annoyances that are very unique to your own project. Unless you have quite unusual requirements, I doubt there is going to be a deal-breaker issue.

jfrisby 10y ago
PgCluster, BDR, rubyrep, and Postgres-XL all provide master-master (or analogous) replication schemes. I cannot attest to the robustness / production-readiness of any of them. And yeah, it really is a PITA to set it up with Postgres. I will note that I once[1] lost a whole cluster's data due to a combination of Slony's sharp corners and operational difficulty of Postgres. The schema visibility thing seems like it might be solvable with row-level security in 9.5? (I.E. apply constraints to the system catalogs, perhaps?) The UPDATE one is good to know. Did you work around it by doing `SELECT ... ORDER BY id FOR UPDATE`? [1] - Slony had a problem where global transaction IDs rolling over caused it to do Very Bad Things and we had had to turn vacuuming off because performance during a peak period -- but we forgot to turn it back ON and... So yeah, a combination of sharp corners in Slony and Postgres + user error == our cluster systematically ate itself.
- pjungwir 10y ago
  Ha, I thought about RLS on system catalogs, but it's not supported. Here is a long thread about it: http://postgresql.nabble.com/Multi-tenancy-with-RLS-td5862118.html http://postgresql.nabble.com/Multi-tenancy-with-RLS-td586211... It is also possible to revoke privileges on pg_namespace, but that breaks too many things for my taste (\dt e.g.). I think I worked around the deadlock issue by detecting the failure in application code and trying again. It was for background workers in a side-project SaaS I abandoned after a few months, so a sloppy fix was very tolerable. I like your SELECT FOR UPDATE idea though!
dragonwriter 10y ago
> I wish there were support for SQL:2011's bitemporal stuff. While there isn't direct support, PostgreSQL's support for range types seems to provide a more-general solution where much of what SQL:2011 temporal support does is a fairly direct application of the more general features (it would be nice to have standards-compliant syntax, though.)
- pjungwir 10y ago
  Have you read Developing Time-Oriented Database Applications in SQL? I am very fond of ranges (actually it's exclusion constraints that are the key addition IMO), but they are not even close to what I'd like to have. I want `PRIMARY KEY` to work on temporal tables. And `REFERENCES` to a temporal table. And `ON DELETE CASCADE` or `ON DELETE SET NULL`. And joins. And be able to do an `INSERT`/`UPDATE`/`DELETE` and have it do the right thing, without writing all the triggers myself. I don't know how well other databases support all this stuff. I'm not complaining, I'm just describing my vision for what temporal support would look like. There is a huge amount of stuff the database could be doing for you automatically.

jfrisby 10y ago

PgCluster, BDR, rubyrep, and Postgres-XL all provide master-master (or analogous) replication schemes. I cannot attest to the robustness / production-readiness of any of them. And yeah, it really is a PITA to set it up with Postgres. I will note that I once[1] lost a whole cluster's data due to a combination of Slony's sharp corners and operational difficulty of Postgres. The schema visibility thing seems like it might be solvable with row-level security in 9.5? (I.E. apply constraints to the system catalogs, perhaps?) The UPDATE one is good to know. Did you work around it by doing `SELECT ... ORDER BY id FOR UPDATE`? [1] - Slony had a problem where global transaction IDs rolling over caused it to do Very Bad Things and we had had to turn vacuuming off because performance during a peak period -- but we forgot to turn it back ON and... So yeah, a combination of sharp corners in Slony and Postgres + user error == our cluster systematically ate itself.

pjungwir 10y ago
Ha, I thought about RLS on system catalogs, but it's not supported. Here is a long thread about it: http://postgresql.nabble.com/Multi-tenancy-with-RLS-td5862118.html http://postgresql.nabble.com/Multi-tenancy-with-RLS-td586211... It is also possible to revoke privileges on pg_namespace, but that breaks too many things for my taste (\dt e.g.). I think I worked around the deadlock issue by detecting the failure in application code and trying again. It was for background workers in a side-project SaaS I abandoned after a few months, so a sloppy fix was very tolerable. I like your SELECT FOR UPDATE idea though!

dragonwriter 10y ago

> I wish there were support for SQL:2011's bitemporal stuff. While there isn't direct support, PostgreSQL's support for range types seems to provide a more-general solution where much of what SQL:2011 temporal support does is a fairly direct application of the more general features (it would be nice to have standards-compliant syntax, though.)

pjungwir 10y ago
Have you read Developing Time-Oriented Database Applications in SQL? I am very fond of ranges (actually it's exclusion constraints that are the key addition IMO), but they are not even close to what I'd like to have. I want `PRIMARY KEY` to work on temporal tables. And `REFERENCES` to a temporal table. And `ON DELETE CASCADE` or `ON DELETE SET NULL`. And joins. And be able to do an `INSERT`/`UPDATE`/`DELETE` and have it do the right thing, without writing all the triggers myself. I don't know how well other databases support all this stuff. I'm not complaining, I'm just describing my vision for what temporal support would look like. There is a huge amount of stuff the database could be doing for you automatically.