PostgreSQL 16 on Ubuntu 22.04

by

PostgreSQL 16 is the ideal choice for this environment because it delivers major improvements in performance, parallel query execution, index efficiency, and write-ahead logging throughput—all of which directly benefit Git metadata workloads and AI/ML-related query patterns. PG16 also includes enhanced vacuum performance, faster sorting, and better handling of high-concurrency workloads, making it extremely well-suited for VM deployments backed by fast RAID-10 SSD storage. It is currently the most stable and well-tested production release with long-term community support, providing the best balance between performance, reliability, and maturity.

Setup on RAID-10 Backed VM with THP Disabled (Host + Guest)

This PostgreSQL deployment runs on a dedicated VM whose virtual disk is backed by hardware RAID-10 SSD storage on the KVM host. RAID-10 provides high IOPS, low latency, and excellent random-read performance. The configuration and tuning recommendations in this guide are aligned specifically for SSD + RAID-10 platforms (Dell H730P controller), ensuring PostgreSQL can take advantage of fast disk access for WAL writes, random index scans, and bulk operations.

To ensure low-latency and stable performance for PostgreSQL, it is critically important to disable Transparent Huge Pages (THP) at both the host and guest VM levels. PostgreSQL does not use THP, and when enabled, THP can cause periodic latency spikes, stalls during memory compaction, and unpredictable planner slowdowns, especially under mixed workloads (Git metadata + AI/ML workloads).

This section documents the exact steps and validations used to disable THP on the host and inside the PostgreSQL VM.

Disable Transparent Huge Pages (THP)

PostgreSQL requires THP to be disabled to ensure consistent latency and avoid query jitter.
Because the database runs inside a VM, THP must be disabled inside the VM (guest kernel).
However, disabling THP on the host also improves the stability of qemu-kvm processes and other memory-intensive workloads.

Disable THP Inside the PostgreSQL Guest VM

Check THP status:

cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag

Expected result:

always madvise [never]

Disable THP immediately:

echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defrag

Create a persistent systemd service:

sudo nano /etc/systemd/system/disable-thp.service

[Unit]
Description=Disable Transparent Huge Pages
After=sysinit.target local-fs.target

[Service]
Type=oneshot
ExecStart=/bin/sh -c "echo never > /sys/kernel/mm/transparent_hugepage/enabled"
ExecStart=/bin/sh -c "echo never > /sys/kernel/mm/transparent_hugepage/defrag"

[Install]
WantedBy=multi-user.target

Enable service:

sudo systemctl daemon-reload
sudo systemctl enable --now disable-thp.service

Disable THP on the KVM Host (Optional but Recommended)

Even though PostgreSQL runs inside a VM, disabling THP on the host improves overall VM stability, including:

  • Reduced CPU steal due to memory compaction
  • More predictable performance for qemu-kvm
  • Better behavior for other workloads (Ceph, Redis, Mongo, etc.)

Already disabled as part of configuring management server.

PostgreSQL Installation Steps (PG 16)

Add the official PostgreSQL Apt repository:

sudo apt update
sudo apt install -y wget gnupg lsb-release
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16

Start & check:

sudo systemctl status postgresql

Optimized postgresql.conf for 8 vCPU / 16GB RAM / RAID-10 SSD

Because the VM is backed by hardware RAID-10 SSDs, we tune PostgreSQL to take advantage of high random I/O throughput and fast WAL sync performance.

Edit config:

sudo nano /etc/postgresql/16/main/postgresql.conf

Memory Tuning

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB

WAL & Checkpoint Tuning (RAID-10 Optimized)

wal_buffers = 64MB
checkpoint_timeout = 15min
max_wal_size = 16GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9

CPU Parallelism

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

Autovacuum (Git metadata workloads)

autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

IO Tuning (SSD + RAID-10)

random_page_cost = 1.1
effective_io_concurrency = 200

Logging

logging_collector = on
log_min_duration_statement = 500ms

Apply:

sudo systemctl restart postgresql

Install pgvector for PostgreSQL 16

apt install postgresql-16-pgvector -y

After installation, verify that vector.so exists:

ls /usr/lib/postgresql/16/lib/vector.so

You should see:

/usr/lib/postgresql/16/lib/vector.so

Create the extension

Enter PostgreSQL:

sudo -u postgres psql

Choose the target database (example: postgres):

\c postgres;

Create extension:

CREATE EXTENSION vector;

Expected:

CREATE EXTENSION

Configure Authentication (edit pg_hba.conf)

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add LAN or local access:

host    all     all     10.0.0.0/16       md5

Reload:

sudo systemctl reload postgresql
sudo systemctl restart postgresql
sudo systemctl enable postgresql

Summary

  • PostgreSQL 16 offers major performance upgrades ideal for Git, metadata-heavy applications, and AI/ML queries.
  • THP must be disabled inside the PostgreSQL VM and optionally on the host for consistent latency.
  • Storage is backed by fast hardware RAID-10 SSDs (Dell H730P), and the tuning parameters above are aligned for that layout.
  • The final configuration delivers a stable, high-performance PG16 deployment suitable for production workloads.