Manuál
Kompletní průvodce PostgreSQL
PostgreSQL je nejpokročilejší open-source databáze. Tady je kompletní průvodce.
Instalace
# Docker
docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16
# psql
psql -h localhost -U postgres
docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16
# psql
psql -h localhost -U postgres
Datové typy
- INTEGER, BIGINT, NUMERIC
- TEXT, VARCHAR
- BOOLEAN
- TIMESTAMP, TIMESTAMPTZ
- JSONB
- UUID
- ARRAY
JSONB — nejlepší z obou světů
CREATE TABLE events (id serial, data jsonb);
INSERT INTO events (data) VALUES ('{"type": "click", "page": "/home"}');
SELECT data->>'type' FROM events; -- operátor
CREATE INDEX idx_events_type ON events ((data->>'type'));
INSERT INTO events (data) VALUES ('{"type": "click", "page": "/home"}');
SELECT data->>'type' FROM events; -- operátor
CREATE INDEX idx_events_type ON events ((data->>'type'));
Full-text search
SELECT * FROM articles
WHERE to_tsvector('czech', title || ' ' || body) @@ to_tsquery('czech', 'kubernetes & docker');
WHERE to_tsvector('czech', title || ' ' || body) @@ to_tsquery('czech', 'kubernetes & docker');
Replikace
- Streaming replication — async/sync, pro HA
- Logical replication — per-table, pro migrace
Backup
# Logical backup
pg_dump -Fc dbname > backup.dump
pg_restore -d dbname backup.dump
# Physical backup
pg_basebackup -D /backup/path
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;
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
Proč PostgreSQL
Relační data + JSONB + full-text search + GIS + time series. Jeden engine, mnoho use cases.