5 ms·

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

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
  Logical replication really isn't going to fix the pain points I have with replication from a DevOps/Sysadmin perspective. The biggest issue with replication in PostgreSQL is restoring a failed master, pg_rewind provides the low-level plumbing to do this and it's somewhat integrated into repmgr now - but it's far from being easy to use compared to something like SQL Server Availability Groups. Being the sole Linux sysadmin / PostgreSQL DBA in my organization means I have to take responsibility for this since the tools are complex enough I can't easily throw them over to our Windows admins or SQL Server DBA's in an emergency. This is partially an issue with staffing, but if the tooling was a little easier to understand and robust enough in fully-automatic operation I could just leave common troubleshooting steps in a runbook, but right now when replication breaks it REALLY breaks.
  - X86BSD 9y ago
    You know what's even easier? Zfs snapshots and zfs send/recv.
    - snuxoll 9y ago
      ZFS snapshots don't handle the same use-case as streaming replication, I've got backups handled quite nicely with barman.
  - normaljoe 9y ago
    That's because they are Windows admins and not nix admins. They probably don't have a good understanding of the command line and if they do not the rich nix command line. I have a single pg admin in current project and he can throw most tasks to the Oracle (running on Linux) admins or even junior Linux admins. While "complex" they are still fairly basic to command line admins. Anything with less than 3 pipes or one regex should be simple to command line *nix person. Granted to excludes you including a complex AWK script or inline perl execution on the command line instead of in a file like a normal sane person.
- manigandham 9y ago
  The major issue with logical replication is the lack of DDL replication, meaning any changes to your table structure will cause issues until the replicas are updated to match. Also an all tables subscription will not automatically pickup new tables, it needs to be refreshed too. At this stage, I'd recommend against using logical replication unless absolutely necessary because it doesn't really do what people would normally expect it to.
phil21 9y ago
I typically say developers love PostgreSQL and sysadmins utterly despise it. The reverse holds true for MySQL. > Replication can be a bit of a pain to set up compared to anything in the MySQL This is a very large understatement.

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
Logical replication really isn't going to fix the pain points I have with replication from a DevOps/Sysadmin perspective. The biggest issue with replication in PostgreSQL is restoring a failed master, pg_rewind provides the low-level plumbing to do this and it's somewhat integrated into repmgr now - but it's far from being easy to use compared to something like SQL Server Availability Groups. Being the sole Linux sysadmin / PostgreSQL DBA in my organization means I have to take responsibility for this since the tools are complex enough I can't easily throw them over to our Windows admins or SQL Server DBA's in an emergency. This is partially an issue with staffing, but if the tooling was a little easier to understand and robust enough in fully-automatic operation I could just leave common troubleshooting steps in a runbook, but right now when replication breaks it REALLY breaks.
- X86BSD 9y ago
  You know what's even easier? Zfs snapshots and zfs send/recv.
  - snuxoll 9y ago
    ZFS snapshots don't handle the same use-case as streaming replication, I've got backups handled quite nicely with barman.
- normaljoe 9y ago
  That's because they are Windows admins and not nix admins. They probably don't have a good understanding of the command line and if they do not the rich nix command line. I have a single pg admin in current project and he can throw most tasks to the Oracle (running on Linux) admins or even junior Linux admins. While "complex" they are still fairly basic to command line admins. Anything with less than 3 pipes or one regex should be simple to command line *nix person. Granted to excludes you including a complex AWK script or inline perl execution on the command line instead of in a file like a normal sane person.
manigandham 9y ago
The major issue with logical replication is the lack of DDL replication, meaning any changes to your table structure will cause issues until the replicas are updated to match. Also an all tables subscription will not automatically pickup new tables, it needs to be refreshed too. At this stage, I'd recommend against using logical replication unless absolutely necessary because it doesn't really do what people would normally expect it to.

phil21 9y ago

I typically say developers love PostgreSQL and sysadmins utterly despise it. The reverse holds true for MySQL. > Replication can be a bit of a pain to set up compared to anything in the MySQL This is a very large understatement.