Database Design Mistakes in Every Codebase, and Their Fixes

-- ❌ No index — this join does a full table scan on orders
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
 
-- ✅ Add indexes to foreign key columns
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_order_items_order_id ON order_items(order_id);

-- ❌ Arbitrary limits that don't reflect real constraints
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255),         -- Why 255? Email addresses can be 320 chars per RFC 5321
  username VARCHAR(255),      -- Should this really be the same limit as email?
  bio VARCHAR(255)            -- Bios are often longer; users will hit this
);
 
-- ✅ Use TEXT with CHECK constraints for actual business rules
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL CHECK (length(email) <= 320),
  username TEXT NOT NULL CHECK (length(username) BETWEEN 3 AND 30),
  bio TEXT CHECK (length(bio) <= 2000)
);

-- ❌ "Flexible" JSON that creates hidden structure
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT,
  attributes JSONB  -- {"color": "red", "size": "M", "weight_kg": 0.5}
);
 
-- Problem: Can't efficiently query, can't enforce types, can't join
SELECT * FROM products WHERE attributes->>'color' = 'red'; -- No index, slow
 
-- ✅ Proper columns for structured, queryable data
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  color TEXT,
  size TEXT CHECK (size IN ('XS', 'S', 'M', 'L', 'XL')),
  weight_kg NUMERIC(6,3)
);
 
-- ✅ JSON is great for genuinely unstructured, rarely-queried metadata
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  color TEXT,
  metadata JSONB  -- supplier notes, custom fields, etc.
);
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- ❌ Standard index includes deleted rows — queries filter them at runtime
CREATE INDEX idx_users_email ON users(email);
 
-- Query: WHERE email = 'user@example.com' AND deleted_at IS NULL
-- Has to scan all matching emails, then filter out deleted ones
 
-- ✅ Partial index — only indexes non-deleted rows
CREATE INDEX idx_users_email_active ON users(email)
  WHERE deleted_at IS NULL;
 
-- Same query now hits only the partial index — dramatically smaller scan

-- ❌ This locks the entire users table while running
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;
 
-- ✅ In PostgreSQL, adding a nullable column is safe (instant)
-- The lock is held briefly, not for the duration of backfill
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;
 
-- ❌ This rewrites the entire table, locks for minutes/hours
ALTER TABLE orders ALTER COLUMN status TYPE TEXT;
 
-- ✅ Zero-downtime column type change
-- Step 1: Add new column
ALTER TABLE orders ADD COLUMN status_new TEXT;
-- Step 2: Backfill in batches (outside a transaction)
UPDATE orders SET status_new = status::TEXT WHERE id BETWEEN x AND y;
-- Step 3: Swap and drop (with a short lock window)
BEGIN;
ALTER TABLE orders RENAME COLUMN status TO status_old;
ALTER TABLE orders RENAME COLUMN status_new TO status;
COMMIT;
DROP COLUMN status_old; -- Safe, no lock needed

// ❌ N+1 hidden in a loop — 1 query to get orders + 1 per order for user
const orders = await db.order.findMany({ take: 50 });
for (const order of orders) {
  const user = await db.user.findUnique({ where: { id: order.userId } });
  console.log(user.name, order.total);
}
// Result: 51 database round trips
 
// ✅ Eager loading with include — 2 queries total
const orders = await db.order.findMany({
  take: 50,
  include: { user: true },
});
// Result: 2 database round trips, regardless of order count