When I started this series, I showed you a single posts table with five columns. By now, working through relationships, indexes, transactions, views, security, and backups, that humble table has grown into a production-grade schema.
But databases don't spring into existence fully formed. They evolve — and how you manage that evolution is just as important as how you design the initial schema.
This final article is about the design decisions, naming conventions, advanced PostgreSQL features, and monitoring practices that separate a hobby project database from a production system. It's a synthesis of everything we've covered, plus the operational wisdom I accumulated through years of running real systems.
Schema Design Principles
1. Design for Queries, Not Just Storage
The most common mistake is designing a schema that "makes sense" conceptually but performs poorly for actual queries. Before finalising your schema, list the top 10 queries your application will run. Design around them.
2. Stable Primary Keys
Use surrogate keys (SERIAL, BIGSERIAL, UUID) as primary keys, not natural keys (email, username, slug). Natural keys change; surrogate keys don't.
3. UUID vs BIGSERIAL
Recommendation: Use BIGSERIAL for single-server applications. Use UUID v7 for distributed systems where IDs must be globally unique.
4. Soft Deletes vs Hard Deletes
Soft deletes are essential when:
You need an audit trail of deletions
Users may want to "undo" a deletion
Regulatory compliance requires data retention
Naming Conventions That Save You Pain
Consistent naming is invisible when you get it right and excruciating when you get it wrong.
Tables and Columns
Indexes and Constraints
Data Types: Choosing Correctly the First Time
Poor type choices are expensive to fix later. Get them right upfront.
Text Columns
Numeric Columns
Date and Time
JSONB: When to Use It
Schema Evolution and Migrations
Your schema will change. Managing migrations safely is crucial.
Safe Migration Patterns
Zero-Downtime Index Creation
Migration Tooling
Structure SQL migrations as numbered, sequential files:
Use a migration tool to track which have run:
Flyway or Liquibase for Java ecosystems
Alembic for Python
Prisma Migrate for Node.js/TypeScript (see ORM 101 series)
golang-migrate for Go
dbmate for a database-agnostic choice
PostgreSQL Advanced Features in Practice
Generated Columns
Table Partitioning
For very large tables (millions of rows), partitioning splits the physical storage:
CTEs with RETURNING
UPSERT with ON CONFLICT
Connection Pooling and Resource Management
Each PostgreSQL connection is an OS process consuming ~5–10 MB of RAM. Connection pooling is essential for web applications.
PgBouncer Configuration
Configure postgresql.conf for Your Workload
Use PGTune to generate settings tuned to your hardware.
Monitoring and Observability
Key Metrics to Track
Autovacuum Tuning
Common Anti-Patterns to Avoid
1. The EAV (Entity-Attribute-Value) Trap
2. Storing Comma-Separated Values
3. SELECT * in Application Code
4. Missing Updated_at
5. Not Using Transactions for Multi-Step Operations
This was covered in the Transactions article, but it bears repeating as the #1 source of data corruption in hobby applications. Any operation that touches more than one table must be wrapped in BEGIN...COMMIT.
The Production-Ready Blog Schema
Here is the complete, production-ready blog schema incorporating everything from this series:
Bridging to ORMs
At this point, you understand what's happening "under the hood" when an ORM like Prisma generates SQL.
Knowing the SQL behind ORM calls means you can:
Spot N+1 query problems in ORM code
Know when to use raw SQL for complex queries
Understand and design migrations correctly
Debug performance issues intelligently
The ORM 101 series covers Prisma in depth — you're now fully prepared for it.
What I Learned Designing Databases
Looking back at that first five-column posts table, the distance travelled is remarkable. Here are the lessons that mattered most:
Design for your queries first — EXPLAIN ANALYZE before you ship any query
Name things consistently — you'll thank yourself in 6 months
Choose types precisely — TIMESTAMPTZ not TIMESTAMP, NUMERIC not FLOAT for money
Migrations are not optional — every schema change needs a migration script
Transactions for multi-step operations — no exceptions
Index foreign keys and WHERE columns — lean on pg_stat_statements to find what else needs indexing
JSONB for genuinely flexible data — not as a replacement for proper columns
Test your backups — a backup you haven't tested doesn't exist
Least privilege always — your application user should never be superuser
Monitor, then optimise — never guess; measure
Your Database Journey Continues
You've completed the Database 101 series. Here's where to go next:
Immediate Next Steps
ORM 101 → — Prisma with PostgreSQL: type-safe database access from Node.js/TypeScript
Advanced PostgreSQL — Window functions at scale, logical replication, pg_partman for partition management
Intermediate Topics
Database Administration: pgAdmin, pg_activity, connection pooling at scale
-- If your most common query is:
SELECT p.*, a.name FROM posts p JOIN authors a ON p.author_id = a.id
WHERE p.status = 'published' ORDER BY p.published_at DESC LIMIT 20;
-- Your schema needs:
-- • status column with an index (or partial index WHERE status='published')
-- • published_at with a descending index
-- • author_id with a foreign key index
-- • Fast author name lookup
-- Good: surrogate primary key
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY, -- stable, never changes
email TEXT UNIQUE NOT NULL, -- natural key — unique but not the PK
slug TEXT UNIQUE NOT NULL -- another natural key
);
-- Avoid: natural key as PK (email changes cause cascading FK updates)
CREATE TABLE authors (
email TEXT PRIMARY KEY -- ❌ painful when email changes
);
-- BIGSERIAL: simple, fast, sequential (but reveals row count)
id BIGSERIAL PRIMARY KEY
-- UUID v4: globally unique, no ordering guarantee, larger (16 bytes)
id UUID DEFAULT gen_random_uuid() PRIMARY KEY
-- UUID v7 (PostgreSQL 17+ or uuid-ossp extension): time-ordered UUID
-- Best of both: globally unique + sortable
COMMENT ON COLUMN posts.id IS 'UUID v7: ordered for index efficiency';
-- Soft delete pattern
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
-- Partial index: only index non-deleted rows (most queries ignore deleted)
CREATE INDEX idx_posts_active ON posts(published_at DESC)
WHERE deleted_at IS NULL AND status = 'published';
-- View for application to use (hides deleted rows by default)
CREATE VIEW active_posts AS
SELECT * FROM posts WHERE deleted_at IS NULL;
-- Tables: lowercase, plural, snake_case
posts, authors, post_categories, activity_logs
-- Columns: lowercase, snake_case, explicit
author_id -- not: author, authorId, AuthorID
published_at -- not: publishDate, publish_date, publishedAt
is_featured -- not: featured, isFeatured, FEATURED
-- Standard columns on every table
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-- Booleans: use is_ or has_ prefix
is_published BOOLEAN NOT NULL DEFAULT FALSE
has_comments BOOLEAN NOT NULL DEFAULT TRUE
-- TEXT: preferred for variable-length strings (no padding, no limts to worry about)
title TEXT NOT NULL
content TEXT NOT NULL
email TEXT NOT NULL
-- VARCHAR(n): use only when you have a hard business rule for max length
-- (e.g., "our API only accepts slugs up to 255 characters")
slug VARCHAR(255) UNIQUE NOT NULL
-- CHAR(n): almost never use — pads with spaces, wastes space
-- status CHAR(10) -- ❌ avoid
-- BIGINT: IDs, counts (safer than INT for anything that might grow large)
id BIGINT
view_count BIGINT DEFAULT 0
-- INTEGER: acceptable for columns guaranteed to stay under 2 billion
age INTEGER
-- NUMERIC(p, s): exact decimal arithmetic (money, measurements)
price NUMERIC(10, 2) -- up to 99,999,999.99
rating NUMERIC(3, 1) -- e.g. 4.5
-- FLOAT / DOUBLE: scientific calculations only (imprecise for money!)
-- NEVER use FLOAT for financial values
-- TIMESTAMPTZ: almost always the right choice
-- Stores in UTC, displays in session timezone
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
published_at TIMESTAMPTZ
-- TIMESTAMP (without timezone): only for truly timezone-agnostic data
-- (e.g., "open at 9:00 AM local time, wherever that is")
-- DATE: for date-only values (birthdays, event dates)
birth_date DATE
-- INTERVAL: for durations
reading_time INTERVAL -- e.g. '5 minutes'
-- Bad use of JSONB (schema-less data that should be columnar)
-- post_metadata JSONB -- contains title, author_id, status → should be columns
-- Good use of JSONB (genuinely flexible, extensible metadata)
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL, -- structured → column
status TEXT NOT NULL, -- structured → column
extra_metadata JSONB DEFAULT '{}'::JSONB -- flexible, extensible → JSONB
);
-- Index JSONB for query performance
CREATE INDEX idx_posts_metadata ON posts USING GIN(extra_metadata);
-- Query JSONB
SELECT * FROM posts WHERE extra_metadata @> '{"featured": true}';
SELECT * FROM posts WHERE extra_metadata->>'canonical_url' IS NOT NULL;
SELECT extra_metadata->>'reading_time' AS reading_time FROM posts WHERE id = 42;
-- ✅ SAFE: Adding a nullable column (no table lock)
ALTER TABLE posts ADD COLUMN subtitle TEXT;
-- ✅ SAFE: Adding a column with a default (PostgreSQL 11+)
ALTER TABLE posts ADD COLUMN is_featured BOOLEAN NOT NULL DEFAULT FALSE;
-- ⚠️ RISKY: Adding a NOT NULL column without a default on large tables
-- (rewrites entire table — causes long lock)
-- Do it in steps:
ALTER TABLE posts ADD COLUMN slug TEXT; -- step 1: add nullable
UPDATE posts SET slug = generate_slug(title) WHERE slug IS NULL; -- step 2: populate
ALTER TABLE posts ALTER COLUMN slug SET NOT NULL; -- step 3: add constraint
-- ✅ SAFE: Renaming via view (zero downtime)
-- 1. Add new column
ALTER TABLE posts ADD COLUMN body TEXT;
-- 2. Create trigger to keep both in sync
-- 3. Backfill: UPDATE posts SET body = content;
-- 4. Update app to use new column name
-- 5. Drop old column after all references updated
-- Standard CREATE INDEX takes a lock that blocks writes
-- For production: use CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_posts_new_column ON posts(new_column);
-- Note: CONCURRENTLY is slower and cannot run inside a transaction block
-- Check for invalid indexes after creation:
SELECT indexname, indisvalid
FROM pg_indexes
JOIN pg_class ON pg_class.relname = pg_indexes.indexname
WHERE schemaname = 'public' AND NOT indisvalid;
-- Computed column, always derived from other columns
ALTER TABLE posts ADD COLUMN word_count INT
GENERATED ALWAYS AS (
ARRAY_LENGTH(STRING_TO_ARRAY(TRIM(content), ' '), 1)
) STORED;
-- Full-text search vector, always kept up to date
ALTER TABLE posts ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
TO_TSVECTOR('english', COALESCE(title, '') || ' ' || COALESCE(content, ''))
) STORED;
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);
-- Query immediately available
SELECT * FROM posts WHERE search_vector @@ TO_TSQUERY('english', 'postgresql & performance');
-- Range partition posts by year
CREATE TABLE posts (
id BIGSERIAL,
title TEXT NOT NULL,
published_at TIMESTAMPTZ,
-- other columns
PRIMARY KEY (id, published_at)
) PARTITION BY RANGE (published_at);
CREATE TABLE posts_2024 PARTITION OF posts
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE posts_2025 PARTITION OF posts
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE posts_2026 PARTITION OF posts
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
-- Queries that include a date range filter only scan relevant partitions
SELECT * FROM posts
WHERE published_at BETWEEN '2025-01-01' AND '2025-12-31';
-- Only scans posts_2025 partition!
-- Insert a post and log it in one statement
WITH new_post AS (
INSERT INTO posts (title, content, author_id, status)
VALUES ('Advanced PostgreSQL', 'Content here...', 5, 'draft')
RETURNING id, title, author_id
),
logged AS (
INSERT INTO activity_log (action, entity_type, entity_id)
SELECT 'post_created', 'post', id FROM new_post
)
SELECT id, title FROM new_post;
-- Insert or update post view count atomically
INSERT INTO post_view_counts (post_id, view_count, last_viewed_at)
VALUES (42, 1, NOW())
ON CONFLICT (post_id) DO UPDATE
SET view_count = post_view_counts.view_count + EXCLUDED.view_count,
last_viewed_at = EXCLUDED.last_viewed_at;
-- Insert or ignore (e.g., deduplication)
INSERT INTO post_tags (post_id, tag_id)
VALUES (42, 7)
ON CONFLICT (post_id, tag_id) DO NOTHING;
# postgresql.conf tuning for a typical web application
max_connections = 100 # Use PgBouncer to handle more clients
shared_buffers = 256MB # 25% of RAM (start here)
effective_cache_size = 1GB # 75% of RAM (planner hint)
work_mem = 4MB # Per-sort/hash operation
maintenance_work_mem = 64MB # For VACUUM, CREATE INDEX
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1 # For SSD storage (default 4.0 is for HDD)
effective_io_concurrency = 200 # For SSD storage
-- 1. Slow queries
SELECT
LEFT(query, 80) AS query,
calls,
ROUND(mean_exec_time::NUMERIC, 2) AS avg_ms,
ROUND(total_exec_time::NUMERIC / 1000, 1) AS total_seconds
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- 2. Table bloat (dead tuples that need VACUUM)
SELECT
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- 3. Cache hit ratio (should be > 99%)
SELECT
SUM(heap_blks_hit) AS heap_hits,
SUM(heap_blks_read) AS heap_reads,
ROUND(100.0 * SUM(heap_blks_hit) /
NULLIF(SUM(heap_blks_hit) + SUM(heap_blks_read), 0), 2) AS hit_ratio_pct
FROM pg_statio_user_tables;
-- 4. Lock waits
SELECT
pid,
wait_event_type,
wait_event,
LEFT(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;
-- 5. Index usage efficiency
SELECT
indexrelname,
idx_scan AS scans,
idx_tup_read AS rows_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Per-table autovacuum settings for high-traffic tables
ALTER TABLE posts SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum after 1% dead tuples
autovacuum_analyze_scale_factor = 0.005, -- analyze after 0.5% changes
autovacuum_vacuum_cost_delay = 2 -- less aggressive throttling
);
-- Check autovacuum activity
SELECT
relname,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'posts';
-- ❌ EAV: flexible but catastrophically slow to query
CREATE TABLE post_properties (
post_id INTEGER,
prop_name TEXT,
prop_value TEXT
);
-- Querying "posts where author = X AND status = published" requires multiple SELFs JOINs
-- Use JSONB instead if you truly need flexible attributes
-- ✅ Better: JSONB with appropriate indexes
ALTER TABLE posts ADD COLUMN extra JSONB DEFAULT '{}';
-- ❌ Never do this
ALTER TABLE posts ADD COLUMN tag_names TEXT; -- 'postgresql,tutorial,backend'
-- ✅ Use a proper junction table
CREATE TABLE post_tags (
post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
-- Or PostgreSQL arrays with GIN index (acceptable for simple lookups)
ALTER TABLE posts ADD COLUMN tag_ids BIGINT[];
CREATE INDEX idx_posts_tag_ids ON posts USING GIN(tag_ids);
-- ❌ Fetching all columns including large TEXT fields
SELECT * FROM posts WHERE status = 'published';
-- ✅ Only fetch what you need
SELECT id, title, slug, published_at, author_id
FROM posts WHERE status = 'published';
-- Every mutable table should have updated_at
-- Apply the trigger from the Views article to all tables:
CREATE TRIGGER trg_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER trg_authors_updated_at
BEFORE UPDATE ON authors
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- Repeat for every table that will be updated
-- ============================================
-- BLOG SYSTEM: Production-Ready Schema
-- Last updated: 2026
-- ============================================
-- Trigger function for updated_at (applied to all tables)
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at := NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
-- AUTHORS
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
bio TEXT,
slug TEXT NOT NULL,
avatar_url TEXT,
published_post_count INTEGER NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
CONSTRAINT uq_authors_email UNIQUE (email),
CONSTRAINT uq_authors_slug UNIQUE (slug)
);
CREATE INDEX idx_authors_active ON authors(slug) WHERE deleted_at IS NULL AND is_active;
CREATE TRIGGER trg_authors_ua BEFORE UPDATE ON authors FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- CATEGORIES
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL,
description TEXT,
parent_id BIGINT REFERENCES categories(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_categories_slug UNIQUE (slug)
);
CREATE TRIGGER trg_categories_ua BEFORE UPDATE ON categories FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- TAGS
CREATE TABLE tags (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_tags_slug UNIQUE (slug)
);
-- POSTS
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
author_id BIGINT NOT NULL REFERENCES authors(id),
title TEXT NOT NULL,
slug TEXT NOT NULL,
excerpt TEXT,
content TEXT,
status TEXT NOT NULL DEFAULT 'draft',
views BIGINT NOT NULL DEFAULT 0,
is_featured BOOLEAN NOT NULL DEFAULT FALSE,
extra JSONB NOT NULL DEFAULT '{}',
search_vector TSVECTOR GENERATED ALWAYS AS (
TO_TSVECTOR('english',
COALESCE(title, '') || ' ' || COALESCE(content, ''))
) STORED,
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
CONSTRAINT uq_posts_slug UNIQUE (slug),
CONSTRAINT chk_post_status CHECK (status IN ('draft','published','archived','scheduled'))
);
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at DESC) WHERE status = 'published' AND deleted_at IS NULL;
CREATE INDEX idx_posts_featured ON posts(published_at DESC) WHERE is_featured AND status = 'published';
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);
CREATE INDEX idx_posts_extra ON posts USING GIN(extra);
CREATE TRIGGER trg_posts_ua BEFORE UPDATE ON posts FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- JUNCTION TABLES
CREATE TABLE post_categories (
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, category_id)
);
CREATE INDEX idx_post_categories_cat ON post_categories(category_id);
CREATE TABLE post_tags (
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);
-- COMMENTS
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
parent_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,
author_name TEXT NOT NULL,
author_email TEXT,
body TEXT NOT NULL,
is_approved BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_comments_post ON comments(post_id) WHERE is_approved;
CREATE INDEX idx_comments_parent ON comments(parent_id);
CREATE TRIGGER trg_comments_ua BEFORE UPDATE ON comments FOR EACH ROW EXECUTE FUNCTION set_updated_at();
// Prisma Query
const posts = await prisma.post.findMany({
where: { status: 'published' },
include: { author: true },
orderBy: { publishedAt: 'desc' },
take: 20
});
// Maps exactly to:
// SELECT p.*, a.*
// FROM posts p
// JOIN authors a ON p.author_id = a.id
// WHERE p.status = 'published'
// ORDER BY p.published_at DESC
// LIMIT 20;
