Running PostgreSQL 18 with pgvector on Rootless Quadlet

Introduction

I wanted a single-node PostgreSQL setup that was not just fast on paper, but also maintainable as part of a local infrastructure stack. For this pass, I organized PostgreSQL 18 around a rootless Podman + Quadlet runtime, enabled LLVM JIT, added pgvector, and pushed both configuration and persistent data into explicit filesystem locations instead of leaving everything implicit inside the container.

The main point was not merely to start a database. I wanted a layout that I could rebuild, inspect, and operate without guessing which part lived in the image, which part lived on disk, and which part was handled by systemd --user.

Background and Motivation

The target was a high-performance single-node PostgreSQL environment with two concrete requirements:

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

At the same time, I wanted the runtime model to stay simple: Podman + Quadlet in rootless mode, with configuration and data persisted on NVMe under /mnt/data. That gave me a setup that could support heavier local development workloads without turning into an opaque one-off container.

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/
  

That separation is intentional. The Dockerfile and postgresql.container file are build and runtime definitions. postgresql.conf and pg_hba.conf are operator-managed configuration. The actual database cluster stays on NVMe-backed storage. This keeps upgrades and rebuilds from being tangled with day-to-day 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
  

I pinned pgvector to v0.8.1, which keeps the build reproducible. Installing postgresql-server-dev-18 ensures the extension build matches PostgreSQL 18 rather than relying on whatever happens to be present.

I also enabled JIT directly in the sample config inside the image and appended jit_above_cost = 10000. Even though I override settings later through an external config file, baking this into the image keeps the intent visible at build time too.

The init SQL is intentionally minimal:

  CREATE EXTENSION IF NOT EXISTS vector;
  

That one line is enough to make the database ready for vector workloads immediately after 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'
  

shared_buffers = 1GB and effective_cache_size = 3GB reflect a straightforward split for a small but capable node. work_mem = 32MB and maintenance_work_mem = 256MB are conservative enough to stay practical without being trivial defaults.

The storage assumptions show up in random_page_cost = 1.1 and effective_io_concurrency = 200, which clearly point to NVMe rather than slower disks. On the WAL side, checkpoint_completion_target = 0.9 is there to smooth checkpoint writes instead of allowing them to bunch up.

JIT is explicitly enabled again here, along with thresholds for optimize and inline behavior. So this is not just “turn JIT on”; it is a deliberate cost-based configuration.

pg_hba.conf

The access policy is intentionally simple:

  # 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 socket access stays on trust, while TCP clients use scram-sha-256. For local development that split is practical, though the broader 192.168.0.0/16 and 10.10.0.0/16 ranges are something I would want to document carefully in a longer-lived shared 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
  

This part matters because Quadlet expects the environment file in the container section, not just anywhere inside the unit. The note later calls this out explicitly in the troubleshooting table, and that is one of the more useful operational details in the whole document.

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
  

I used slirp4netns:allow_host_loopback=true so the rootless container can still be tested cleanly from the host. PublishPort=5432:5432 keeps the access pattern simple, while Tmpfs=/dev/shm:size=4g makes shared memory less of a bottleneck for database work.

The explicit mounts for postgresql.conf and pg_hba.conf under /etc/postgresql/ are also important. They avoid ambiguity about which config files are active, and they help avoid conflicts with data-directory-oriented 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
  

That ownership step matters. Rootless does not remove the need to line up on-disk permissions for persistent volumes. If the directory is wrong, initialization and normal writes can fail in ways that look unrelated at first glance.

The note also makes a subtle but correct point: I should enable the .container unit, not the generated .service.

Verification

The verification step stays focused:

  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

The Read-only file system issue is a good example of why the config mount points matter. Mounting configuration under a path that PostgreSQL treats as data-related can trigger ownership behavior you do not want.

The environment-file issue is another easy one to miss. In Quadlet, [Container] EnvironmentFile= is the right place. Putting it under [Service] makes the configuration look reasonable while still failing to do what was intended.

The final point about initdb variables is also important: once the database has already been initialized, those bootstrap values stop applying. At that stage the fix is either to reset the data directory or make the change in SQL.

Results

The outcome I wanted was a PostgreSQL 18 environment with LLVM JIT and pgvector running reliably through rootless Quadlet, and the note captures all the required pieces for that. Build-time dependencies, externalized configuration, NVMe-backed persistence, startup management, verification commands, and known failure modes are all connected in one place.

What I especially like about this setup is that it is not just a Dockerfile snippet. It is an operational layout. It explains where the files live, how the service starts, how it is validated, and what tends to break first.

Future Work

The first thing I would tighten next is the initialization path: either rely on COPY 010-create-vector.sql as the single mechanism, or formalize use of the optional init-script volume mount. Leaving both available is flexible, but it can also make rebuild behavior harder to reason about.

I would also revisit the access ranges in pg_hba.conf if this moves beyond a strictly local environment. 192.168.0.0/16 and 10.10.0.0/16 are broad enough that documentation and network intent should stay explicit.

Finally, POSTGRES_PASSWORD=localdev is obviously suitable for local development, not for shared or long-lived infrastructure. If this setup graduates into a more persistent environment, secret management should be separated from the runtime note. Even so, for the stated goal of getting a reproducible and practical local PostgreSQL stack running, this configuration is solid.