Postgres: JIT --with-llvm

[agsimeonov]

Postgres 11 comes with the new JIT compilation feature:

https://www.postgresql.org/about/news/1855/
https://www.postgresql.org/docs/devel/static/jit-reason.html

PostgreSQL 11 introduces support for Just-In-Time (JIT) compilation to optimize the execution of code and other operations at run time. Using components of the LLVM project, the introduction of JIT support in PostgreSQL accelerates queries that utilize expressions (e.g. WHERE clauses), target lists, aggregates, projections, as well as some internal operations.

The JIT framework in PostgreSQL is designed to allow future work for optimizations that can be added in the future. If you are building PostgreSQL 11 from source, you can enable JIT compilation using the --with-llvm flag.

Also from:

https://www.postgresql.org/docs/devel/static/install-procedure.html#CONFIGURE-WITH-LLVM

Build with support for LLVM based JIT compilation (see Chapter 32). This requires the LLVM library to be installed. The minimum required version of LLVM is currently 3.9.

LLVM support requires a compatible clang compiler (specified, if necessary, using the CLANG environment variable), and a working C++ compiler (specified, if necessary, using the CXX environment variable).

Would be great to have JIT in Postgres 11 Docker images.

[tianon]

The Debian variants (non-Alpine) should already support this (https://salsa.debian.org/postgresql/postgresql/blob/6fc4a5a131f20c0d2979345d527c34c50cda26e2/debian/rules#L33-36 and https://salsa.debian.org/postgresql/postgresql/blob/6fc4a5a131f20c0d2979345d527c34c50cda26e2/debian/control#L33). Do you have a simple way we could confirm?

For Alpine variants, I think https://pkgs.alpinelinux.org/package/v3.8/main/x86_64/llvm5-dev probably bodes well for the possibility of implementing this. :+1:

[tianon]

I tried using SHOW jit as a query to determine whether JIT is supported, but it returns on for all versions of PostgreSQL 11, even the Alpine variant where I know JIT support is definitely not compiled in. :disappointed:

[tianon]

Ok, using SET jit_above_cost = 0; EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class, I've managed to confirm that postgres:11 already does support (and use) JIT. :+1:

I've also managed to verify that my Alpine changes work to do the same, but the size difference is pretty significant; ~72.9MB up to ~132MB with JIT support compiled in.

Edit: for reference, here's the full diff I was testing with:

diff --git a/11/alpine/Dockerfile b/11/alpine/Dockerfile
index 52e3cf5..5702501 100644
--- a/11/alpine/Dockerfile
+++ b/11/alpine/Dockerfile
@@ -44,6 +44,7 @@ RUN set -ex \
    \
    && apk add --no-cache --virtual .build-deps \
        bison \
+       clang llvm5-dev g++ \
        coreutils \
        dpkg-dev dpkg \
        flex \
@@ -110,6 +111,7 @@ RUN set -ex \
        --with-libxml \
        --with-libxslt \
        --with-icu \
+       --with-llvm \
    && make -j "$(nproc)" world \
    && make install-world \
    && make -C contrib install \

[agsimeonov]

That's great, I will switch to Debian version for now. I suppose a note in the Readme would be nice for this in terms of alpine, or a separate image tag alpine-llvm or something of the sort.

[tianon]

I guess a note in the documentation (https://github.com/docker-library/docs/tree/master/postgres) would probably be a good idea for now, although I'm not sure what we want to do with this longer-term. PostgreSQL obviously is going all-in behind LLVM (postgres:10 is ~228MB, postgres:11 is ~311MB, presumably thanks in large part to libllvm), so perhaps it would be appropriate for us to just bite the bullet and follow suit, but for Alpine it'll roughly double the size of the image.

I'm wary of adding a new variant because I'm not convinced it would stay the appropriate solution long-term. :confused:

[tianon]

Ah, given https://github.com/docker-library/postgres/issues/484#issuecomment-415003251 ("The problem with versions earlier than 6.0 is that they don't cope with C++ exceptions, so certain PostGIS queries make the server crash.") combined with https://pkgs.alpinelinux.org/packages?name=llvm&branch=v3.8&arch=x86_64 (nothing newer than LLVM 5.0.1, and even Alpine Edge doesn't seem to have 6+ yet), I'm going to close this. Without compiling LLVM from source, we can't currently get a new enough version in Alpine to actually support what PostgreSQL needs, so this point is actually currently moot (compiling LLVM from source is a no-go -- that's _way_ too heavyweight :sweat_smile:).

[deepj]

@tianon Is there any chance to add variants PG standard/alpine with enabled JIT even it means an increased size of the final docker image?

[hc-codersatlas]

alpine 3.10 has llvm7 and llvm8 now. Does this help with adding this feature?

[J0WI]

alpine 3.10 has llvm7 and llvm8 now.

And JIT is enabled by default for PostgreSQL 12.

@tianon is it worth to reconsider this?

[tianon]

Yes, thanks for the prod. :+1: