From Installation to High‑Concurrency Tuning: A Hands‑On PostgreSQL Guide

Why Switch to PostgreSQL?

The author recounts a case where a MySQL deployment collapsed under high traffic and suggests PostgreSQL as a more robust alternative, citing stronger ACID support, richer data types (JSONB, arrays, geometric, IP, UUID), powerful extensions (PostGIS, pgvector, TimescaleDB), and advanced query features (window functions, CTEs, recursive queries).

Binary Installation (Recommended for Production)

For RHEL/CentOS/Rocky/AlmaLinux, add the official YUM repository, disable the built‑in module, install postgresql17-server, initialize the database, enable and start the service:

# Install official YUM repo
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable system PostgreSQL module
sudo dnf -qy module disable postgresql
# Install PostgreSQL 17 server
sudo dnf install -y postgresql17-server
# Initialise database
sudo /usr/pgsql-17/bin/postgresql-17-setup --initdb
# Enable and start service
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17

Verify the installation with psql --version (expected output: psql (PostgreSQL) 17.4).

For Debian/Ubuntu, the process is even shorter:

# Install repository key and packages
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# Install PostgreSQL 17
sudo apt install -y postgresql-17
# Service starts automatically; verify status
sudo systemctl status postgresql

Source Compilation (Deep Customisation)

Compile from source when a required version is unavailable, when special compile‑time options (e.g., DTrace, specific filesystem support) are needed, or when installing to a custom path.

Install build dependencies:

# RHEL
sudo yum groupinstall -y "Development Tools"
sudo yum install -y zlib-devel readline-devel libicu-devel openssl-devel
# Debian
sudo apt install -y gcc build-essential zlib1g-dev libreadline-dev libicu-dev libssl-dev pkg-config

Download, extract, configure, build, and install:

# Download source
wget https://ftp.postgresql.org/pub/source/v17.4/postgresql-17.4.tar.bz2
# Extract
tar -xvf postgresql-17.4.tar.bz2
cd postgresql-17.4
# Configure (custom prefix)
./configure --prefix=/usr/local/pgsql
# Build using all cores
make -j $(nproc)
# Install
sudo make install

Compile useful contrib extensions (e.g., pg_stat_statements, uuid-ossp, hstore) in the contrib directory:

cd contrib
make -j$(nproc)
sudo make install

Add the binary directory to PATH:

echo 'export PATH=/usr/local/pgsql/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

Initialisation and Security Hardening

Create the dedicated postgres system user (if not created automatically) and initialise the data cluster:

sudo useradd -m -s /bin/bash postgres
sudo mkdir -p /pgdata/data
sudo chown -R postgres:postgres /pgdata
sudo chmod 700 /pgdata/data
su - postgres
initdb -D /pgdata/data -U postgres -W

Start the server ( pg_ctl for source installs, systemctl for binary installs):

# Source install
pg_ctl -D /pgdata/data -l /pgdata/logfile start
# Binary install (RHEL)
sudo systemctl start postgresql-17

Secure the instance by editing postgresql.conf and pg_hba.conf:

Listen address : change from localhost to '*' or a specific IP.

pg_hba.conf : configure authentication methods. Example entries:

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres   peer
local   all       all        md5
host    all       all        192.168.1.0/24 md5
host    all       all        10.0.0.0/8      md5
host    all       all        0.0.0.0/0       reject

Set a strong password for the postgres user:

psql -c "ALTER USER postgres WITH PASSWORD 'YOUR_STRONG_PASSWORD';"

Restart to apply changes:

sudo systemctl restart postgresql-17
# or
pg_ctl -D /pgdata/data restart

High‑Concurrency Performance Tuning

On a 16‑core, 32 GB server, the default configuration yields ~800‑900 TPS with pgbench. The following parameters are tuned to raise TPS above 4 000 and increase cache‑hit ratio to >99%.

Memory‑related Settings

shared_buffers = 8GB          # 25% of RAM
work_mem = 16MB               # per operation; adjust per workload
effective_cache_size = 24GB   # ~75% of RAM
maintenance_work_mem = 512MB

Connection and WAL Settings

max_connections = 500        # increase with connection pool (PgBouncer)
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 512MB
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9

Additional Optimisations

random_page_cost = 1.1
effective_io_concurrency = 200
log_min_duration_statement = 500
autovacuum = on
autovacuum_vacuum_cost_delay = 20ms

Verification with pgbench

# Initialise 100‑scale (≈15 M rows)
pgbench -i -s 100 postgres
# Run 500 concurrent clients for 60 s
pgbench -c 500 -j 16 -T 60 postgres

After tuning, TPS exceeds 4 000 and cache‑hit ratio rises from ~89% to >99% (query:

SELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS cache_hit_ratio FROM pg_stat_database;

).

Conclusion

Binary installation covers ~90% of use cases – quick and reliable.

Source compilation is reserved for deep customisation.

Never skip security hardening; pg_hba.conf is the final defence line.

Adjusting shared_buffers, work_mem, and max_connections alone can deliver >5× performance gains.

Proper tuning turns PostgreSQL from a “can‑run” database into a high‑performance engine.