PostgreSQL Don'ts and Dos

on YouTube ↗59m

" summary

If backups take 48 hours with pg_dump expect a 5–6 day restore; switch to binary backups like pg_basebackup and a PITR-capable tool such as pg_backrest (parallel, retention, faster restores). Tune work_mem before shared_buffers, avoid thousands of DB connections—use a pool and size for worst-case (rolling upgrades add connections). Export metrics to Prometheus/Grafana and enable pg_stat_statements. Use SCRAM (SCRAM-SHA-256), prefer UUIDv7 or int keys, avoid OFFSET pagination, use RETURNING, range types, filter/window aggregates.

AI-written summary · about

" chapters (13)

1. 0:00Introduction
2. 3:30Backups and common mistakes
3. 12:22Tuning PostgreSQL parameters
4. 17:37Connection pooler best practices
5. 20:43Monitoring and baselines
6. 32:16Security password and encryption
7. 36:44Developer data types and UUIDs
8. 41:19Integer vs bigint
9. 43:52Enums and range types
10. 48:04Pagination and ORM usage
11. 51:10Advanced SQL features
12. 56:31Summary
13. 57:37Q&A on backups and pgBackRest

" related talks

• 50mPostgreSQL Backup and Recovery MethodsKevin Kempter · PostgresOpen 2013
• 57mPostgreSQL When It's Not Your JobChristophe Pettus · Postgres Open 2012
• 56mPostgreSQL Backup StrategiesMagnus Hagander · Postgres Open 2012
• 46mAn ultimate guide to upgrading your PostgreSQL installationIlya Kosmodemiansky · PostgresOpen Silicon Valley 2017
• 50mAn ultimate guide to upgrading your PostgreSQL installationIlya Kosmodemiansky · PostgreSQL Conference Europe 2024
• 02h 30mTutorialJosh Berkus · PGConf Silicon Valley 2015
• 51mHow Postgres is misused and abused in the wildKaren Jex · PGConf.dev 2024
• 37m​Postgres at Any Scale​Will Leinweber · RailsConf 2017