Configuration
- Postgresco.nf has recommendations for how to tune and configure various Postgres parameters, and documentation in a convenient readable format
- PGTune generates configs for various scenarios that can be used as a starting point
Connection Pooling
Gotchas / Tips
- PostgreSQL: Six Not-So-Easy Pieces
- 10 Things I Hate About PostgreSQL
- Problems with Postgres’s MVCC implementation
- How we made DISTINCT queries up to 8000x faster on PostgreSQL
PostgreSQL currently lacks the ability to efficiently pull a list of unique values from an ordered index. Even when you have an index that matches the exact order and columns for these “last point” queries, PostgreSQL is still forced to scan the entire index to find all unique values. As a table grows (and they grow quickly with time-series data), this operation keeps getting slower.
- HN comment on the planner randomly switching to using a bad plan, slowing down queries significantly
- Problems with subtransactions
- Index merges are much slower than composite indexes
- Postgres tips and tricks
- Notes on GitLab Postgres Schema Design
- When Postgres Indexing Went Wrong
Security
Soft Deletes
Text Vs Varchar
The text data type has no limits on string length and seems like an easy choice over using varchar, as there are no performance advantages to using varchar over text. However, using varchar can help enforce constraints on reasonable text lengths and prevents storage of too much data.
ZFS
- Everything I’ve seen on optimizing Postgres on ZFS: a collection of things to tweak when running Postgres on ZFS