PostgreSQL on FreeBSD: The Definitive Review
PostgreSQL and FreeBSD have a long shared history. PostgreSQL was born at UC Berkeley, the same institution that produced BSD Unix. Both projects share a commitment to correctness, standards compliance, and engineering rigor. Running PostgreSQL on FreeBSD is not just possible -- it is one of the most natural pairings in the open-source infrastructure world. FreeBSD's ZFS integration, stable network stack, and predictable performance characteristics make it an excellent platform for PostgreSQL workloads.
This review covers why PostgreSQL and FreeBSD pair well, installation and initial configuration, ZFS integration strategies, performance tuning, WAL-based backup and recovery, monitoring with pg_stat views, streaming replication, and a comparison with MySQL on FreeBSD.
Why PostgreSQL and FreeBSD Pair Well
Several technical factors make this combination stronger than the sum of its parts.
ZFS and PostgreSQL data integrity: Both ZFS and PostgreSQL are designed around data integrity guarantees. ZFS provides checksumming, copy-on-write semantics, and atomic transactions at the filesystem level. PostgreSQL provides MVCC, WAL, and crash recovery at the database level. Together, they create two independent layers of data protection.
Stable ABI and long support cycles: FreeBSD releases are supported for years, and the ABI stability means PostgreSQL binaries built for a FreeBSD release continue to work throughout its lifecycle. This reduces the operational burden of database maintenance.
Network stack performance: FreeBSD's network stack is mature and well-tuned for high-throughput, low-latency workloads. For database servers handling thousands of client connections, this matters.
jails for isolation: FreeBSD jails allow running PostgreSQL in isolated environments without the overhead of full virtualization. Multiple PostgreSQL instances can run in separate jails on the same host with strong isolation.
Installation
Package Installation
shpkg install postgresql16-server postgresql16-contrib
This installs PostgreSQL 16, the current stable release, along with contrib modules (pg_stat_statements, btree_gist, pg_trgm, and dozens more).
Initialize the database cluster:
shsysrc postgresql_enable="YES" /usr/local/etc/rc.d/postgresql initdb service postgresql start
The data directory defaults to /var/db/postgres/data16/. The PostgreSQL superuser is postgres, mapped to the postgres system user.
Verify the installation:
shsu - postgres -c "psql -c 'SELECT version();'"
From Ports (When Needed)
If you need custom build options -- such as a specific ICU version, GSSAPI support, or LLVM JIT compilation:
shcd /usr/ports/databases/postgresql16-server make config make install clean
For most deployments, the binary package is sufficient.
ZFS Integration
ZFS is the recommended filesystem for PostgreSQL on FreeBSD. Proper ZFS configuration can significantly improve database performance and simplify backup operations.
Dataset Layout
Create separate ZFS datasets for PostgreSQL data and WAL:
shzfs create -o mountpoint=/var/db/postgres zroot/postgres zfs create -o recordsize=8K -o primarycache=metadata -o logbias=throughput zroot/postgres/data zfs create -o recordsize=128K -o primarycache=metadata -o logbias=latency zroot/postgres/wal
The key tuning decisions:
- recordsize=8K for data: PostgreSQL uses 8K pages. Matching the ZFS recordsize eliminates read amplification and write amplification for random I/O workloads.
- recordsize=128K for WAL: WAL writes are sequential and benefit from larger records.
- primarycache=metadata: PostgreSQL has its own buffer cache (shared_buffers). Letting ZFS ARC cache data pages creates double caching. Setting primarycache to metadata avoids this waste.
- logbias=throughput for data: Reduces ZIL overhead for data writes since PostgreSQL WAL already provides crash recovery.
- logbias=latency for WAL: WAL writes are latency-sensitive; keep ZIL behavior default.
Disable atime on both:
shzfs set atime=off zroot/postgres/data zfs set atime=off zroot/postgres/wal
Moving WAL to a Separate Dataset
After creating the datasets, configure PostgreSQL to use the WAL dataset. Stop the service first:
shservice postgresql stop mkdir -p /var/db/postgres/wal/pg_wal chown postgres:postgres /var/db/postgres/wal/pg_wal mv /var/db/postgres/data16/pg_wal/* /var/db/postgres/wal/pg_wal/ rmdir /var/db/postgres/data16/pg_wal ln -s /var/db/postgres/wal/pg_wal /var/db/postgres/data16/pg_wal service postgresql start
ZFS Snapshots for Backup
ZFS snapshots provide instant, consistent point-in-time copies:
sh# Create a snapshot before maintenance zfs snapshot zroot/postgres/data@pre-upgrade zfs snapshot zroot/postgres/wal@pre-upgrade # Rollback if needed zfs rollback zroot/postgres/data@pre-upgrade zfs rollback zroot/postgres/wal@pre-upgrade
For production backups, combine ZFS snapshots with pg_basebackup or WAL archiving for point-in-time recovery.
Performance Tuning
postgresql.conf Key Parameters
Edit /var/db/postgres/data16/postgresql.conf:
sh# Memory shared_buffers = '4GB' # 25% of system RAM for dedicated DB server effective_cache_size = '12GB' # 75% of system RAM work_mem = '64MB' # Per-operation sort/hash memory maintenance_work_mem = '1GB' # VACUUM, CREATE INDEX, ALTER TABLE # WAL wal_buffers = '64MB' max_wal_size = '4GB' min_wal_size = '1GB' checkpoint_completion_target = 0.9 # Query Planning random_page_cost = 1.1 # ZFS with SSD: set close to seq_page_cost effective_io_concurrency = 200 # SSD-backed ZFS seq_page_cost = 1.0 # Connections max_connections = 200
Kernel Tuning for PostgreSQL
FreeBSD kernel parameters in /etc/sysctl.conf:
sh# Shared memory -- PostgreSQL needs large SysV shared memory segments kern.ipc.shmmax=17179869184 kern.ipc.shmall=4194304 # Semaphores kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 # Network tuning for database connections net.inet.tcp.sendbuf_max=16777216 net.inet.tcp.recvbuf_max=16777216 kern.ipc.maxsockbuf=16777216
Apply without reboot:
shsysctl -f /etc/sysctl.conf
Connection Pooling with PgBouncer
For applications with many short-lived connections, PgBouncer is essential:
shpkg install pgbouncer sysrc pgbouncer_enable="YES"
Configure /usr/local/etc/pgbouncer.ini:
sh[databases] myapp = host=127.0.0.1 port=5432 dbname=myapp [pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = md5 auth_file = /usr/local/etc/pgbouncer.userlist pool_mode = transaction default_pool_size = 20 max_client_conn = 1000
WAL-Based Backup and Recovery
Continuous Archiving
Enable WAL archiving in postgresql.conf:
sharchive_mode = on archive_command = 'cp %p /var/db/postgres/wal_archive/%f'
Create the archive directory:
shmkdir -p /var/db/postgres/wal_archive chown postgres:postgres /var/db/postgres/wal_archive
Base Backup with pg_basebackup
shsu - postgres -c "pg_basebackup -D /var/db/postgres/backups/base_$(date +%Y%m%d) -Ft -z -P --wal-method=stream"
This creates a compressed tar backup with streaming WAL inclusion. Combined with continuous archiving, you can restore to any point in time.
Point-in-Time Recovery
To restore to a specific time:
sh# Stop PostgreSQL service postgresql stop # Restore base backup rm -rf /var/db/postgres/data16/* tar xzf /var/db/postgres/backups/base_20260409/base.tar.gz -C /var/db/postgres/data16/ # Create recovery signal and configure cat > /var/db/postgres/data16/postgresql.auto.conf << 'RECOVERY' restore_command = 'cp /var/db/postgres/wal_archive/%f %p' recovery_target_time = '2026-04-09 14:30:00' RECOVERY touch /var/db/postgres/data16/recovery.signal chown -R postgres:postgres /var/db/postgres/data16/ service postgresql start
Monitoring with pg_stat
PostgreSQL provides extensive statistics views. Enable the statistics collector and pg_stat_statements:
sh# In postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all track_io_timing = on track_activity_query_size = 4096
Create the extension:
shsu - postgres -c "psql -c 'CREATE EXTENSION pg_stat_statements;'"
Essential Monitoring Queries
Active queries and locks:
shsu - postgres -c "psql -c \" SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC LIMIT 10; \""
Table hit ratio (should be above 99%):
shsu - postgres -c "psql -c \" SELECT relname, round(heap_blks_hit::numeric / greatest(heap_blks_hit + heap_blks_read, 1), 4) AS hit_ratio FROM pg_statio_user_tables ORDER BY heap_blks_hit + heap_blks_read DESC LIMIT 20; \""
Slowest queries:
shsu - postgres -c "psql -c \" SELECT calls, mean_exec_time, total_exec_time, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; \""
Replication lag:
shsu - postgres -c "psql -c \" SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, (sent_lsn - replay_lsn) AS replay_lag FROM pg_stat_replication; \""
Streaming Replication
Primary Configuration
On the primary server, edit postgresql.conf:
shwal_level = replica max_wal_senders = 5 wal_keep_size = '2GB'
Create a replication user:
shsu - postgres -c "psql -c \"CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';\""
Add to pg_hba.conf:
sh# /var/db/postgres/data16/pg_hba.conf host replication replicator 10.0.0.0/24 scram-sha-256
Reload:
shservice postgresql reload
Standby Configuration
On the standby server:
shservice postgresql stop rm -rf /var/db/postgres/data16/* su - postgres -c "pg_basebackup -h primary.example.com -U replicator -D /var/db/postgres/data16 -P --wal-method=stream -R" service postgresql start
The -R flag creates standby.signal and sets primary_conninfo automatically.
Verify replication from the primary:
shsu - postgres -c "psql -c 'SELECT * FROM pg_stat_replication;'"
PostgreSQL vs MySQL on FreeBSD
Data Integrity
PostgreSQL is stricter about data integrity by default. It rejects invalid dates, enforces constraints during transactions, and has full ACID compliance across all storage engines. MySQL with InnoDB has improved, but historical permissiveness (silent data truncation, invalid date acceptance) means legacy behavior can still surface.
SQL Compliance
PostgreSQL supports window functions, CTEs, lateral joins, JSON/JSONB operators, full-text search, and range types natively. MySQL has added many of these features over time but PostgreSQL's implementation is generally more complete and performant.
FreeBSD Integration
Both are available via pkg install. PostgreSQL's rc script integration on FreeBSD is slightly more mature. MySQL on FreeBSD works well, but some MySQL features (like Group Replication) receive less testing on FreeBSD than on Linux.
ZFS Considerations
Both benefit from ZFS. The tuning approach is similar -- match recordsize to the database page size (8K for PostgreSQL, 16K for InnoDB). PostgreSQL's double-write protection via WAL means you can safely set full_page_writes = off on ZFS (since ZFS provides atomic writes), which reduces WAL volume. MySQL's doublewrite buffer serves the same purpose and can similarly be disabled on ZFS.
Performance
For read-heavy analytical workloads, PostgreSQL's query planner and parallel query support are superior. For simple key-value lookups and write-heavy web workloads, MySQL with InnoDB can be faster due to simpler query planning overhead. In practice, the difference is small and depends heavily on schema design and query patterns.
When to Choose Each
- PostgreSQL: Complex queries, JSONB document storage, GIS data (PostGIS), strict data integrity requirements, analytical workloads, or when you need advanced SQL features.
- MySQL: Legacy application compatibility, simple web application workloads, when the application framework expects MySQL, or when InnoDB's clustered index performance benefits your access patterns.
FAQ
What version of PostgreSQL should I run on FreeBSD?
Run the latest stable major version available in the FreeBSD package repository. As of this writing, PostgreSQL 16 is the current stable release. FreeBSD typically packages the last three to four major versions, so PostgreSQL 14, 15, and 16 are all available. Use the latest unless your application has a specific version requirement.
How do I upgrade PostgreSQL major versions on FreeBSD?
Use pg_upgrade for in-place major version upgrades:
shpkg install postgresql17-server service postgresql stop /usr/local/bin/pg_upgrade \ --old-datadir=/var/db/postgres/data16 \ --new-datadir=/var/db/postgres/data17 \ --old-bindir=/usr/local/bin \ --new-bindir=/usr/local/bin
Always take a ZFS snapshot before upgrading, and test the upgrade on a standby first.
Should I disable PostgreSQL full_page_writes on ZFS?
Yes, it is safe to set full_page_writes = off on ZFS. ZFS guarantees atomic block writes, which eliminates the partial-page write problem that full_page_writes protects against. Disabling it reduces WAL volume by 20-50%, which improves write performance and reduces replication bandwidth. This is one of the concrete benefits of running PostgreSQL on ZFS.
How much shared_buffers should I configure?
Start with 25% of total system RAM for a dedicated database server. On a machine with 64 GB of RAM, set shared_buffers = '16GB'. If running PostgreSQL on ZFS with primarycache=metadata, you can increase shared_buffers to 40-50% of RAM since you are not competing with ARC for data caching.
How do I monitor PostgreSQL performance on FreeBSD?
Enable pg_stat_statements (shown above) for query-level monitoring. For system-level monitoring, use Prometheus with postgres_exporter:
shpkg install postgres_exporter sysrc postgres_exporter_enable="YES" sysrc postgres_exporter_args="--web.listen-address=:9187" service postgres_exporter start
This exposes PostgreSQL metrics at port 9187 for Prometheus to scrape.
Can I run multiple PostgreSQL instances on one FreeBSD server?
Yes. Use jails for isolation, with each jail running its own PostgreSQL instance on a separate port and data directory. Alternatively, use pg_lsclusters-style management by initializing multiple data directories and running separate instances with distinct ports. Jails are the preferred approach on FreeBSD for security isolation.
How does PostgreSQL handle backups on ZFS?
ZFS snapshots provide instant filesystem-level backups. For application-consistent backups, combine ZFS snapshots with pg_backup_start() and pg_backup_stop() (PostgreSQL 15+) or pg_start_backup()/pg_stop_backup() (older versions). For point-in-time recovery, use continuous WAL archiving with pg_basebackup as described above.