Postgres optimal setup
- set timeouts
- collect query stats
- log slow queries
- pgbouncer for connection pooling
- optimize indexes
https://blog.crunchydata.com/blog/five-tips-for-a-healthier-postgres-database-in-the-new-year
Distributed architectures
- Cloud storage
- Read replicas
- Active-active
- Sharding (Citus)
- K-V stores with SQL
https://www.crunchydata.com/blog/an-overview-of-distributed-postgresql-architectures
UUID for primary key
https://maciejwalkowiak.com/blog/postgres-uuid-primary-key/
Upserts
https://antonz.org/sql-upsert/
Locking
https://leontrolski.github.io/pglockpy.html
Operating PG
Renaming tables with no downtime
https://brandur.org/fragments/postgres-table-rename
Performance tuning
Performance : column order DOES matter
https://www.cybertec-postgresql.com/en/column-order-in-postgresql-does-matter/
Performance: IN preferred to OR
Performance of large text / TOAST
https://hakibenita.com/sql-medium-text-performance
DELETE and its intricacies
https://notso.boringsql.com/posts/deletes-are-difficult/
deleted-record-insert method
- instead of soft delete (deleted_at)
https://brandur.org/fragments/deleted-record-insert
Advanced : hstore and HyperLogLog for “seen by”
https://supabase.com/blog/seen-by-in-postgresql
RETURNING data after the query processed it
https://sqlfordevs.com/returning-modified-rows
Sample / exercise datasets
https://github.com/lorint/AdventureWorks-for-Postgres
https://www.postgresqltutorial.com/postgresql-sample-database/
https://github.com/devrimgunduz/pagila
https://postgrespro.com/education/demodb
(And many more at https://wiki.postgresql.org/wiki/Sample_Databases )
PG as message queue / job queue
https://webapp.io/blog/postgres-is-the-answer/
DB tools
Postico - UI to query and edit DB
https://eggerapps.at/postico2/
Upgrading Postgres
https://lipanski.com/posts/upgrading-postgres
Stretching Postgres to do “Big Data” OLAP
- Parquet files as foreign data
- DB + data lake hybrid
https://www.crunchydata.com/blog/parquet-and-postgres-in-the-data-lake