PostgreSQL 18 and pgvector on rootless Quadlet

Configuration overview

I organized PostgreSQL 18 as a rootless Podman + Quadlet service with LLVM JIT and pgvector. Configuration and persistent data have separate locations.

systemd --user manages startup. The layout separates image rebuilds, external configuration and database storage.

Uses and requirements

The single-node database has two requirements:

• LLVM JIT for query optimization
• pgvector for vector search support

Configuration and data are persisted on NVMe under /mnt/data. This provides a database layout for business application development and vector search for LLM integration.

Directory Layout

I split the runtime definition, configuration, and persistent storage like this:

  /opt/containers/runtime/postgresql/
 ├── etc/
 │    ├── postgresql.conf
 │    ├── pg_hba.conf
 │    └── docker-entrypoint-initdb.d/
 │         └── 010-create-vector.sql
 ├── Dockerfile
 └── postgresql.container
  

Persistent data lives separately here:

  /mnt/data/postgresql/data/
  

Dockerfile and postgresql.container define the image and runtime. postgresql.conf and pg_hba.conf remain outside the container. The database cluster is stored on NVMe, separating rebuilds from data management.

Dockerfile (pg18 + LLVM + pgvector)

The image starts from postgres:18-trixie and adds the packages needed to build pgvector.

  FROM postgres:18-trixie

RUN set -eux; \
    apt-get update; \
    apt-get install -y --no-install-recommends \
      build-essential clang llvm-dev git ca-certificates pkg-config make postgresql-server-dev-18; \
    rm -rf /var/lib/apt/lists/*

ARG PGVECTOR_VERSION=v0.8.1
RUN git clone --depth 1 --branch "$PGVECTOR_VERSION" https://github.com/pgvector/pgvector.git /tmp/pgvector && \
    make -C /tmp/pgvector && make -C /tmp/pgvector install && rm -rf /tmp/pgvector

RUN sed -i 's/#jit = off/jit = on/' /usr/share/postgresql/postgresql.conf.sample \
    && printf '\njit_above_cost = 10000\n' >> /usr/share/postgresql/postgresql.conf.sample

COPY 010-create-vector.sql /docker-entrypoint-initdb.d/010-create-vector.sql
  

pgvector is pinned to v0.8.1. postgresql-server-dev-18 supplies the headers for a PostgreSQL 18 extension build.

The image enables JIT in its sample configuration and adds jit_above_cost = 10000. The external postgresql.conf also sets JIT explicitly.

Initialization SQL creates the vector extension:

  CREATE EXTENSION IF NOT EXISTS vector;
  

This makes vector available immediately after database initialization.

postgresql.conf (assuming 4 GB RAM)

The runtime tuning is built around a 4 GB memory assumption.

  listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
work_mem = 32MB
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# WAL
wal_buffers = 16MB
min_wal_size = 512MB
max_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
log_checkpoints = on

# Logging
log_destination = 'stderr'
logging_collector = on
log_min_duration_statement = 200ms
log_line_prefix = '%m [%p] %q%u@%d '

# JIT
jit = on
jit_above_cost = 10000
jit_optimize_above_cost = 50000
jit_inline_above_cost = 100000

# Locale
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
  

The configuration sets shared_buffers = 1GB, effective_cache_size = 3GB, work_mem = 32MB and maintenance_work_mem = 256MB.

For NVMe, random_page_cost = 1.1 and effective_io_concurrency = 200 are set. checkpoint_completion_target = 0.9 spreads checkpoint writes.

JIT is enabled with explicit optimization and inline cost thresholds.

pg_hba.conf

Authentication settings:

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             192.168.0.0/16          scram-sha-256
host    all             all             10.10.0.0/16            scram-sha-256
  

Local UNIX sockets use trust; TCP clients use scram-sha-256. The allowed ranges are 192.168.0.0/16 and 10.10.0.0/16 and need review for the intended environment.

Environment File

The runtime variables are stored in ~/.config/containers/systemd/.postgresql.env.

  POSTGRES_USER=postgres
POSTGRES_PASSWORD=localdev
POSTGRES_DB=appdb
  

Permissions are restricted like this:

  chmod 600 ~/.config/containers/systemd/.postgresql.env
  

Quadlet reads EnvironmentFile from [Container]. Putting it under [Service] does not apply it as intended.

Quadlet: postgresql.container

The Quadlet definition is:

  [Unit]
Description=PostgreSQL 18 (LLVM/JIT + PG Vector)
Wants=network-online.target
After=network-online.target

[Container]
Image=compute.home.arpa/pg18-jit-vec:latest
ContainerName=postgresql
Network=slirp4netns:allow_host_loopback=true
PublishPort=5432:5432
Tmpfs=/dev/shm:size=4g

Volume=/mnt/data/postgresql/data:/var/lib/postgresql/data:rw
Volume=/opt/containers/runtime/postgresql/etc/postgresql.conf:/etc/postgresql/postgresql.conf:ro
Volume=/opt/containers/runtime/postgresql/etc/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro

EnvironmentFile=%h/.config/containers/systemd/.postgresql.env

Exec=postgres -c config_file=/etc/postgresql/postgresql.conf -c hba_file=/etc/postgresql/pg_hba.conf

Ulimit=nofile=1048576:1048576

[Service]
Restart=always

[Install]
WantedBy=default.target
  

slirp4netns:allow_host_loopback=true supports host access, and PublishPort=5432:5432 exposes the port. Tmpfs=/dev/shm:size=4g allocates shared memory.

postgresql.conf and pg_hba.conf are mounted under /etc/postgresql/ so the active settings are explicit and separate from data-directory mount points.

Startup Procedure

Before starting the service, I prepare the data directory and align ownership with the PostgreSQL container user:

  mkdir -p /mnt/data/postgresql/data
sudo chown -R 999:999 /mnt/data/postgresql/data

systemctl --user daemon-reload
systemctl --user enable --now postgresql.container
systemctl --user status postgresql
  

Persistent volume ownership must match the PostgreSQL container user. Rootless operation still requires correct permissions for initialization and writes.

The recorded procedure enables the .container unit rather than the generated .service.

Verification

Check JIT and the vector extension:

  psql -h 127.0.0.1 -U postgres -d appdb -c "SHOW jit;"
psql -h 127.0.0.1 -U postgres -d appdb -c "SELECT * FROM pg_extension;"
  

Expected output:

  jit
-----
on
  

   name   | version | schema     | description
--------+---------+------------+----------------------------------------
 plpgsql | 1.0    | pg_catalog | PL/pgSQL procedural language
 vector  | 0.8.1  | public     | vector data type and ivfflat and hnsw access methods
  

Those two checks validate the key outcomes: JIT is active, and pgvector is available inside the initialized database.

Tuning and Troubleshooting

A read-only configuration mount under a data-related path can trigger chown: Read-only file system. Mounting under /etc/postgresql/ avoids that conflict.

If environment variables do not apply, check [Container] EnvironmentFile=.

initdb variables stop applying after the database has been initialized. Changes then require SQL or resetting the data directory.

Results

The layout provides the image build, external configuration, NVMe persistence, startup and verification steps for PostgreSQL 18 + LLVM JIT + pgvector on rootless Quadlet.

Future Work

I plan to choose one initialization route: COPY 010-create-vector.sql or the optional init-script volume mount.

For a shared environment, I would review the allowed 192.168.0.0/16 and 10.10.0.0/16 ranges in pg_hba.conf.

POSTGRES_PASSWORD=localdev is for local development. A shared or long-lived environment needs separate secret management.