3 ms·
A lot of major performance improvements. Some big ones: CTEs no longer act as an optimization barrier. JIT enabled by default. Speed and space improvements f
by eloff 7y ago
A lot of major performance improvements. Some big ones:
CTEs no longer act as an optimization barrier.
JIT enabled by default.
Speed and space improvements for many index types, including b-trees.
REINDEX CONCURRENTLY doesn't block table writes.
- jeltz 7y agoTo clarify: REINDEX CONCURRENTLY is a new feature. Previously there were only DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY, which meant that while you could first create a new index and then drop the old one renaming still required an exclusive lock (this is also fixed in 12), as did switching over all foreign keys to the new index. REINDEX CONCURRENTLY essentially does all this work for you in a single command while also making sure no reader or writer is locked out from the table.
- ioltas 7y agoAn advantage of REINDEX CONCURRENTLY is that it becomes much easier to handle dependencies of a rebuilt index with constraints, as what the feature roughly does it to add a swapping phase between the creation and the drop portions which switches the dependencies from the old index to the new fresh one. Table-level reindex also makes life easier. The feature is equivalent to external tools like pg_repack (formerly pg_reorg) in more performant, and has a couple of limitations to be aware of: https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REINDEX-CONCURRENTLY https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REIN...
- darksaints 7y ago> CTEs no longer act as an optimization barrier. I've been waiting for years for this, one of my last few complaints about Postgres that some other DBs did better. It's so frustrating to have to resort to worse composability in order to get reasonable performance.
- tyfon 7y agoWhat other DB does this better? At work I constantly have to create temporary tables instead of CTEs in MS SQL server to avoid crashing the server completely. I guess Oracle maybe but I've never used that db, or DB2. But regardless of who does it better, this is very very welcome news for postgres in any case :)
- darksaints 7y agoOracle does, as well as MySQL. I thought MSSQL did as well, but I guess not...never really used it though.
- swasheck 7y agolooks like CTEs also support materialization which most devs thought implicitly happened anyway, but didn't. by removing the fence _and_ supporting materialization CTEs are now useful.
- pgaddict 7y agoNot sure what you mean. Previously CTEs were always materialized (i.e. stashed into a buffer, somewhere), in PostgreSQL 12 it's optional and disabled by default for various cases that don't require it. The problem with materialization is that it prevents various optimizations (e.g. using index for a condition from outside the CTE). And while some devs might be aware of the materialization (and possibly even leveraged it intentionally, to block some optimizer behavior) many did not - it's widely used as if it was just a nice way to name the subqueries.
- yxhuvud 7y ago> JIT enabled by default. Only if you actually happened to compile the support for it, which is not the default.
- pgaddict 7y agoDepends on packaging. Most distros should have that compiled as a separate package, e.g. postgresql12-llvmjit. So you install that (along with the regular packages) and you'll have JIT.