postgresql

PostgreSQL

Administer, optimize, and secure PostgreSQL databases in development and production environments.

When to Use

• You need a reliable, ACID-compliant relational database.
• Your application requires advanced features such as JSONB, full-text search, or CTEs.
• You are setting up streaming replication or point-in-time recovery.
• You need to tune an existing PostgreSQL deployment for better throughput.

Prerequisites

• Linux server (Debian/Ubuntu or RHEL-based) or Docker.
• Root or sudo access for package installation.
• Familiarity with SQL fundamentals.

Installation and Setup

# Debian / Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# RHEL / Amazon Linux
sudo dnf install -y postgresql15-server postgresql15-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Verify
psql --version
sudo systemctl status postgresql

Initial User and Database Setup

# Switch to the postgres system user
sudo -u postgres psql

-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;

psql Commands Reference

\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY

Configuration Tuning

Edit /etc/postgresql/15/main/postgresql.conf (path varies by OS and version).

# Connection settings
listen_addresses = '*'
max_connections = 200

# Memory — adjust to ~25% of total RAM for shared_buffers
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL / write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
min_wal_size = 1GB
max_wal_size = 4GB

# Planner
random_page_cost = 1.1          # lower for SSD
effective_io_concurrency = 200  # for SSD

# Logging
log_min_duration_statement = 250   # log queries slower than 250 ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on