PostgreSQL

Why it matters for UCP. PostgreSQL is the primary database for UCP services. ACID, isolation, and sharding are must-knows for a senior engineer at any maturity level. Part of the learning program.

Articles on how PostgreSQL is built and operated. Explanations use code examples on a consistent pair of tables (category + product), so it's easy to switch between articles.

Articles in this section

1. ACID and transaction isolation levels — what the four letters mean, how PostgreSQL implements them (MVCC, WAL, snapshots, SSI), the four isolation levels with examples of the phenomena (dirty / non-repeatable / phantom read, write skew).
2. Database size, partitioning, and sharding — how to estimate the size of a database and its tables, when to apply partitioning, when to shard, how to choose a partition key and a shard key.
3. Covering Index (INCLUDE) — INCLUDE vs simply extending the key, index-only scan, the visibility map subtlety, UNIQUE … INCLUDE for returning "extra" fields.
4. FTS: weights and ranking — setweight and the four levels A/B/C/D, a generated column with tsvector + GIN, ts_rank vs ts_rank_cd, common mistakes with dictionary language.

Related

• PG Style Guide — the rules for working with PostgreSQL in Java/Spring services (codes R-PG-*).
• jOOQ Style Guide — how to access PostgreSQL from Java via jOOQ, the repository pattern, multiset.
• Distributed Patterns Style Guide — saga, idempotency, outbox: how to ensure integrity beyond a single database.