The Complete Guide to PostgreSQL¶
PostgreSQL is the most advanced open-source database. Here is a complete guide.
Installation¶
Docker¶
docker run -d –name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16
psql¶
psql -h localhost -U postgres
Data Types¶
- INTEGER, BIGINT, NUMERIC
- TEXT, VARCHAR
- BOOLEAN
- TIMESTAMP, TIMESTAMPTZ
- JSONB
- UUID
- ARRAY
JSONB — The Best of Both Worlds¶
CREATE TABLE events (id serial, data jsonb); INSERT INTO events (data) VALUES (‘{“type”: “click”, “page”: “/home”}’); SELECT data->>’type’ FROM events; – operator CREATE INDEX idx_events_type ON events ((data->>’type’));
Full-Text Search¶
SELECT * FROM articles WHERE to_tsvector(‘english’, title || ’ ’ || body) @@ to_tsquery(‘english’, ‘kubernetes & docker’);
Replication¶
- Streaming replication — async/sync, for HA
- Logical replication — per-table, for migrations
Backup¶
Logical backup¶
pg_dump -Fc dbname > backup.dump pg_restore -d dbname backup.dump
Physical backup¶
pg_basebackup -D /backup/path
Performance¶
EXPLAIN (ANALYZE, BUFFERS) SELECT …; SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC;
Extensions¶
- pg_stat_statements — query statistics
- PostGIS — geospatial data
- pg_trgm — fuzzy search
- TimescaleDB — time series
Why PostgreSQL¶
Relational data + JSONB + full-text search + GIS + time series. One engine, many use cases.