PostgreSQL je nejpokročilejší open-source databáze. Tady je kompletní průvodce.
Installation¶
Docker¶
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’));
Full-text search¶
SELECT * FROM articles
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
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
Proč PostgreSQL¶
Relační data + JSONB + full-text search + GIS + time series. Jeden engine, mnoho use cases.