#PostgreSQL
10 posts
-
RDB Design Series Part 8: Zero-Downtime Migration and Multi-Tenant Design — ALTER TABLE Traps, Expand-Contract, and Tenant Isolation Strategies
Changing schemas on a live service is as hard as designing them. ALTER TABLE lock traps, zero-downtime column changes with Expand-Contract, Flyway vs Liquibase, rollback strategies, and three multi-tenant schema strategies. The final part of the series.
-
RDB Design Series Part 7: Anti-Patterns and Temporal Data Design — EAV, God Table, Temporal Table, Event Sourcing
Covers repeatedly failing schema design patterns. EAV, God Table, Polymorphic Association, Soft Delete traps, and then the patterns you should actively use — Temporal Tables, Slowly Changing Dimensions, and Event Sourcing basics. Knowing what NOT to do is a design skill.
-
RDB Design Series Part 6: Index Design and JOIN Strategy — All the Way to the Optimizer
Go beyond index basics into design-level thinking. Covering indexes, partial indexes, cardinality analysis, JOIN algorithms (Nested Loop, Hash, Merge), reading EXPLAIN ANALYZE, and the N+1 problem. Turn index creation into index design.
-
RDB Design Series Part 5: Order/Payment Domain Schema — Production-Grade
Design the e-commerce order domain at production quality. 3-level hierarchy (order → delivery → item), state machines, payment idempotency, split payments, partial refunds, and cancellation audit trails. One domain, all the way down.
-
RDB Design Series Part 4: Relationship Design Patterns — 1:1, 1:N, N:M and Beyond
Stop designing table relationships by gut feeling. From 1:1 split criteria to 1:N vs N:M judgment, self-referencing trees, and polymorphic associations — every relationship design pattern you'll face in practice.
-
RDB Design Series Part 3: Constraints and Data Integrity — Preventing Bugs at the Schema Level
CHECK, UNIQUE, FK, DEFAULT, Generated Columns — constraints aren't 'annoying overhead,' they're 'defense code baked into the schema.' This guide provides practical criteria for DB vs app validation, whether to use FKs or not, and defensive schema design patterns.
-
RDB Design Series Part 2: Normalization and Denormalization — Not Theory, but Judgment Calls
Normalization isn't 'always required,' and denormalization isn't 'a compromise for performance.' Using a single online store scenario, this guide explains 1NF through 3NF intuitively, then presents clear criteria for when breaking normalization is the right call — with MySQL/PostgreSQL differences.
-
RDB Design Series Part 1: Schema Basics — Naming, Data Types, PK Strategy, NULL
The first decisions you need to make when designing RDB tables. Naming conventions, data type selection, PK strategies (AUTO_INCREMENT vs UUID vs ULID), and NULL semantics — skip these now, pay for it later with painful migrations.
-
Transaction Isolation Levels Explained: From Read Uncommitted to Serializable
A practical guide to the 4 transaction isolation levels with real-world examples. Covers concurrency anomalies, MySQL vs PostgreSQL differences, and how to choose the right level.
-
AWS Database Selection Guide: EC2+Docker vs RDS vs Aurora
How to choose between running a DB directly on EC2 with Docker, RDS, Aurora, and Aurora Serverless v2 — a detailed comparison based on cost, performance, and operational burden