PostgreSQL: Setup, Performance and Management for IT Pros

PostgreSQL is the database I reach for when I have a choice. Free, ACID-compliant, extensible, runs everywhere, and has a track record that goes back to 1996. If you've only worked with SQL Server or MySQL, the switch takes a weekend to get comfortable with.

Installation

Ubuntu/Debian:

sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql  # connect as superuser

Docker (development):

docker run -d --name postgres \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_USER=admin \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

postgresql.conf Tuning

The default postgresql.conf is tuned conservatively to run on anything. Adjust these for a dedicated database server:

# Memory (for a server with 16GB RAM)
shared_buffers = 4GB                  # 25% of RAM
effective_cache_size = 12GB           # 75% of RAM
work_mem = 64MB                       # per sort/hash operation
maintenance_work_mem = 1GB            # for VACUUM, CREATE INDEX

# WAL / Write Performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Parallelism
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Connection pooling (use PgBouncer instead of raising this)
max_connections = 100

Use PgTune to generate a baseline config for your hardware. It's faster than calculating values manually.

Management Tools

pgAdmin 4 — the official GUI. Runs in a browser, connects to multiple servers, query editor with execution plans. I already run it in Docker alongside my PostgreSQL container (you probably do too — see your docker ps).

DBeaver — cross-platform SQL client that handles PostgreSQL, MySQL, SQL Server, Oracle and 50 others from one tool. Better for writing complex queries than pgAdmin.

psql — the command-line client. Fast for quick checks and scripting. Learn enough of it to not need a GUI when you're SSH'd into a server.

# Useful psql one-liners
\l                      # list databases
\dt                     # list tables
\d tablename            # describe table structure
\timing                 # show query execution time
\copy t FROM 'file.csv' CSV HEADER   # bulk import CSV

Extensions Worth Installing

-- Vector similarity search (AI/ML use cases)
CREATE EXTENSION vector;

-- Time-series data (Timescale)
CREATE EXTENSION timescaledb;

-- Geospatial data
CREATE EXTENSION postgis;

-- Full-text search improvements
CREATE EXTENSION pg_trgm;

-- Statistics on query plans
CREATE EXTENSION pg_stat_statements;

pg_stat_statements is the one to enable first — it tracks query performance over time, equivalent to SQL Server's sys.dm_exec_query_stats.

Backup

# Single database backup
pg_dump -U admin mydb > mydb_backup.sql

# All databases
pg_dumpall -U postgres > all_databases.sql

# Binary format (faster restore for large DBs)
pg_dump -U admin -Fc mydb > mydb.dump
pg_restore -U admin -d mydb mydb.dump

Automate with a cron job and ship the output to your NAS. For continuous backup, pgBackRest handles incremental WAL archiving properly.

PostgreSQL vs SQL Server

Both are production-grade. The practical differences:

For new Linux-based infrastructure, PostgreSQL. For anything tied to Active Directory, .NET or Windows auth, SQL Server. For everything else, PostgreSQL runs cheaper and has a broader open-source extension ecosystem.