That being said, I would not recommend Oracle without all of the above factors already in place. For most use cases, Oracle is more pain than it’s worth.
I wouldn't use it on any of my own projects though, but only because I wouldn't want to pay for it, and because I have enough faith in myself to be able to manage the trickier bits of Postgres.
> Historically we've always materialized the full output of a CTE query
Do you know if this means that CTEs are written to disk? I didn't think this was the case. (In the case of materialized views that qualifier means the view is written to disk).
Search for "Materialize node" in https://www.postgresql.org/docs/12/using-explain.html
On the other hand, if the same query were written with subqueries instead of with CTEs, then the query optimizer would not treat it as an optimization fence. If it could utilize indexes or rewrite the query to be relationally equivalent, it would do so.
Note that sometimes that optimization fence is beneficial. There are situations where it's better to create temp tables and run smaller simpler queries instead of running an extremely complex monolithic query because the query planner isn't perfect even with hints. You can still enable that optimization fence functionality in PostgreSQL if you need to, but it's generally pretty rare that it happens like this. Still, you'll see stored procedures for reports still using temp tables and even cursors sometimes because they can be made to perform better in certain situations.
with mycte as (
select a, count(b)
from foo
group by a
)
select *
from mycte
where a = 10;
with mycte as (
select a, count(b)
from foo
where a = 10;
group by a
)
select *
from mycte;
0: https://www.postgresql.org/message-id/507ac540ec7c20136364b5...
https://medium.com/@rbranson/10-things-i-hate-about-postgres...
https://www.cybertec-postgresql.com/en/things-could-be-impro...
Also JIT compilation, while very nice and a step in the right direction, is very barebones at the moment, hardly achieving its potential. Here's a long todo list of what and how to make efficient use of JIT in postgres, by the main author of the feature. https://twitter.com/AndresFreundTec/status/10025899696161996... Postgres release 12 did not add any JIT related improvements, and as far as I know no work has been done on it on 13 either.
You should check the latest commitfest. Improvements are coming, at a steady pace.
I just made a very casual search of the most recently completed commitfest and I saw one entry ("JIT expression evaluation improvements") marked as "Moved to next CF" and in the upcoming one the only item planned was the one that was moved.
Maybe there's more there with less obvious names?
Anyway, to our surprise, not only did it provide little boost, but other queries that were simpler and previously caused no issues became inexplicably and intolerably slow.
We did not have time to go down the rabbit hole of query planning and explains, and being a highly dynamically queriable app, we didn't know what else was in store for other queries based on some combination of user inputs down the road.
So we rolled back. PG has otherwise been great for us. It really is the performance issue in certain situations that has caused pain.
One more note is with regard to what I would call extreme performance variability based on row counts. What I mean is there seems to be some threshold that, once crossed, causes some queries to go from perfectly fine to near-interminable. You expect some degradation as row counts go up, but here the performance behavior suddenly degrades in a nonlinear fashion. That kind of issue is difficult to tune.
Though, most of our team's expertise as far as databases went was in Postgres (including our DBA).
The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always.
The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.
(Most recent version I've used was SQL Server 2008.)
As an example, say you have a database of all cars ever produced with indexes on model and production date. If you are looking for the latest Ford F-150 then the best plan is to just start looking backwards by date and you will find one soon enough. Much faster than looking up all F-150s and picking the latest one. On the other hand, if you are looking for latest Ford Model-T, that plan is going to be catastrophically terrible, going through 93 years of car production before finding the correct one.
In my estimation, if you're not already intimately familiar with the intricacies of performant SQL, chances are you're not playing in a space where a no-SQL architecture is an appropriate fit anyway. And you're certainly not in a position to make an informed decision between SQL and no-SQL. It's a much better use of this hypothetical person's time to set up Mariadb or Sqlite and do a deep dive into the fundamentals query performance.
But I do agree that PostgreSQL has way too little tools to nail down the performance even though they have downsides. Tools like pinning execution plans would be nice, as it has less severe worst case behaviors. As would be the ability to just pass the execution plan directly, although that would have severe cross-version compatibility implications and security will also be hard to nail down after the fact because of all the "can't happen" assumptions sprinkled around in executor code. And even just plain plan hints would be great to have, be it the heavy handed "join in this order", "use this index", or the more graceful "this clause is way less selective than you think", "this clause is functionally dependent on that one" or "assume there is correlation between ordering and predicates".
Every single time the SQL Server query optimiser did something obviously wrong, rebuilding statistics fixed it. The problem I had with SQL Server wasn't its reliance of statistics—it's not like they could work any other way—it's that it failed to maintain its statistics correctly. Any time that statistics stop being correct is a bug. It should be able to maintain them itself and trigger rebuilds whenever there's any doubt about them.