PostgreSQL Reading Checklist
I'm not a database expert, in my career I've faced so many issues and bottlenecks, most of the come from database. My crystal ball 🔮 told me that it's just a matter of time until the next database issues occur, so I decided to gather as much practical database learning sources as I can in the past years, especially the ones that I think (based on experience) will most likely be used.
We're using PostgreSQL at work so it's going to be tailored specifically to it, the checklist should be read in order but nothing stops you to jump around.
# Basic PostgreSQL
- Discovering the Computer Science Behind Postgres Indexes
- Following a Select Statement Through Postgres Internals
- A Look at How Postgres Executes a Tiny Join
- Is Your Postgres Query Starved for Memory?
- Understanding
bitmap heap scanandbitmap index scan - Understanding bitmap indexes in postgresql
# Slow Queries
- Reading a Postgres
EXPLAIN ANALYZEQuery Plan - How to Interpret PostgreSQL
EXPLAIN ANALYZEOutput - 3 Ways to Detect Slow Queries in PostgreSQL
# Postgres Locks
# Tuning postgres
- On the impact of full-page writes
- Basics of Tuning Checkpoints
- Tuning PostgreSQL for High Write Workloads
- Full text search (psql 12)
# Data Partitioning
As of this writing, there are some changes in data partitioning on different version of postgres, so please always refer back to the official postgres documentation.
- How to use table partitioning to scale PostgreSQL
- Automatic Partition Creation in PostgreSQL
- PostgreSQL 14: Automatic hash and list partitioning?
# Replication
Replication is a method to do data redundancy, there are several reasons why you want to have redundancy:
- Having a live replica in different location as a backup just incase the primary DB is down. There might be regulatory needs for data redundancy.
- Offload data ingestion workload to another database.
Checklist:
# Other References
Not related directly to postgres, more of a general knowledge: