- 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.
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.
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!
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.)
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.
- Tables cannot be organized as a clustered index (this can be useful to control rows ordering at the storage layer).
- No builtin query cache (but I'm not sure it's very useful).
- It's easier to find hosted MySQL services like Amazon RDS Aurora and Google Cloud SQL.
- YouTube Vitess for MySQL is really great. I don't know if there is something similar for PostgreSQL.
I won't write about the endless list of PostgreSQL's advantages since it wasn't the question ;-)
Although not actively maintained by the engine (you have to recluster on a schedule): https://www.postgresql.org/docs/current/static/sql-cluster.h...
1. Tooling is not on par with other databases. If you want nice GUI that holds your hand and does everything with one click - you may find offer for postgres lacking here. I don't care much about that, but some people do.
2. There is no "in memory" storage (but you can use in memory fs to get the same effect).
3. Extensions often define their own operators (that's good), which are made of some combination of +-*/>=~!@#%^&|`?. Yes. Your code may look like its perl. You can make "select foo @@>&!|~ bar from baz" to be valid query.
4. Finding hosting for postgres in not a problem, but its not something as universal as mysql.
Overall, it really is solid database. I've never been disappointed by postgres, while mysql is always a walk through a forest of wtf for me.
# Wouldn't this work?
fsync=off
synchronous_commit=off
full_page_writes=off
bgwriter_lru_maxpages=0DataGrip, DBeaver (free), HeidiSQL (free)
There are so many upsides to PostgreSQL. I build my food app on redis as I did not know what my schema was going to be. That worked great, but simulating the power of what you get with PostgreSQL is that much harder.
I am in the process of converting things back to PostgreSQL now that my schema has settled. The one thing I will probably keep in Redis is the geo location code as this feature works super well.
https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9....
CREATE OR REPLACE FUNCTION bitcount(i integer) RETURNS integer AS $$
DECLARE n integer;
DECLARE amount integer;
BEGIN
amount := 0;
FOR n IN 1..16 LOOP
amount := amount + ((i >> (n-1)) & 1);
END LOOP;
RETURN amount;
END
$$ LANGUAGE plpgsql;There were some attempts but none made it so far. See also: https://wiki.postgresql.org/images/6/64/Fosdem20150130Postgr...
Clearly, it doesn't have support for SQL:2011 temporal syntax, but with range types and exclusion constraints, it seems to have fairly good support for doing the things you can do with SQL:2011's temporal features.
(And, having dealt with business data where multiple business [application] date ranges of relevance applied, I think that SQL:2011's "bitemporal" approach which provides special syntax for a single each system of system and application date/time range is in some respects creating an overly-specialized solution to a too-narrow subset of the temporal data problem.)
Oracle's installed base is the main reason my current gig is highly dependent on it. In so many cases, we have to integrate using Oracle "datapump" configuration. We'd look silly not running Oracle.
The commercial PostrgreSQL derivative from EnterpriseDB does support CONNECT BY, but since Oracle compatibility seems to be a big part of what they sell and SQL standards compliance is a big focus of the core Postgres product, I don't see it likely that that would get contributed back to the core. (And CONNECT BY is, IIRC, substantially more limited than standard recursive CTEs, so not having it, while it does hurt migration from Oracle's proprietary dialect, avoids people heading down the CONNECT BY path and then being forced to burn it down and start over with standard CTE syntax when they run into a wall. So there's a case that its better just to have standard CTEs.)
I simplify the selection to the more general architectural value. Do I need SQL, NoSQL, or graph? What about deep JSON queries, embeddability, specific performance requirements, scalability?
Given that, I use PostgreSQL as the default choice for SQL/JSON db in all cases that don't require imbedding. SQLite for imbedding.
There is one big disadvantage, the same for all fixed schema index based databases. Scalability. Engineering effort rapidly goes to infinity as we asymptotically approach the throughput event horizon.
Except for that the only thing that was a problem after switching is that Postgres is a bit more strict about some stuff, such as dividing by zore, which throws an error in Postgres but results in NULL in MySQL.
The main reason I love Postgres, and which is also why I switched, are the Database Definition language statements that support transactions (ALTER TABLE). This allows for much better migrations, if for some reason something fails you will not be in a state that is undefined.
Some people making just one app might look at SQLite, simply because it's simpler to set up. But those are my only two suggestions: PostgreSQL or SQLite.
It's a great db though, with very few bugs, high quality code, responsive devs, and relatively cheap support plans (like maybe $25k/year to get 24x7 support? I don't remember the numbers exactly) available from enterprisedb.com who have a bunch of core devs. In my one experience they knew their stuff.
Because of that, upgrades are either stressfull and cumbersome (ie. slony + switchover to a promoted and upgraded slave), or imply a large downtime (ie. pg_upgrade).
Also, because of that (WAL format), you can't use native replication between different major versions of PostgreSQL.
Other than that, Postgres is (IMHO, as always) the perfect RDBMS.
Doesn't allow you to force the use of an index in querying.
Materialized views always do a full rebuild, instead of applying deltas.
No estimates for when a long-running query will complete.
But this is for all of the right reasons.