database_design

Start by identifying access patterns and drawing an ER diagram of entities and relationships. Then normalize tables up to 3NF, define primary keys, foreign keys, and constraints, and choose column types appropriate for your database engine.

Run EXPLAIN ANALYZE on the slow query to inspect the execution plan, then add B-Tree indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Avoid functions on indexed columns and replace SELECT * with only the columns you need.

Use embedded MongoDB documents for one-to-few, read-heavy relationships and referenced collections for one-to-many, write-heavy workloads. Choose relational databases like PostgreSQL when you need strict constraints, joins, and normalized transactional data.

Add the new column as nullable, start dual-writing to both old and new columns, backfill the data, then enforce NOT NULL and remove the old column. Avoid dropping columns or tables directly in production.

Indexes on low-cardinality columns like booleans provide little selectivity, so the database often ignores them while still paying write and storage costs. Index only frequently filtered, joined, or sorted columns with meaningful cardinality.