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?
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.
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).
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.
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.
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.
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.
> Replication can be a bit of a pain to set up compared to anything in the MySQL
This is a very large understatement.
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.
Can you please elaborate in what respect?
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.
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).
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).
* <all the usual caveats about attempting to tune query performance>