Hacker News Reader

Hacker News

Comment by eloff | Hacker News Reader

Full thread

eloff

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.

View on HN

To 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.

An 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-REIN...

> 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.

What 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 :)

Oracle does, as well as MySQL. I thought MSSQL did as well, but I guess not...never really used it though.

looks 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.

Not 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.

> JIT enabled by default.

Only if you actually happened to compile the support for it, which is not the default.

Depends 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.

Reply on news.ycombinator.com