Database Setup for MVP: PostgreSQL Best Practices

Your database is the foundation of your application. Get it wrong early, and you'll pay for it forever.

Most startups make critical database mistakes in the MVP phase. They design schemas that don't scale, skip indexing, and leave security gaps.

Here's how to set up PostgreSQL correctly from day one.

---

Why PostgreSQL for MVPs

PostgreSQL is the database of choice for modern startups. Here's why:

• Reliability: Battle-tested for 25+ years
• Performance: Excellent for most workloads
• Flexibility: JSON support, full-text search, and more
• Scalability: Handles from 100 to 100M+ users
• Tooling: Excellent managed options (Supabase, Neon, Railway)
• Community: Huge ecosystem and expertise available

---

PostgreSQL Setup Checklist

1. Choose Your Hosting

Recommendation for most MVPs: Supabase or Neon

---

Schema Design Best Practices

1. Naming Conventions

Tables:

• Use snake_case: user_accounts not userAccounts
• Use plural: users not user
• Be consistent: All tables singular or all plural

Columns:

• Use snake_case: created_at not createdAt
• Use _id for foreign keys: user_id not userId
• Use is_ or has_ for booleans: is_active, has_subscription

2. Primary Keys

Always use UUIDs for production:

sql
-- Good: UUID primary key
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- Acceptable for simple cases: Serial
id SERIAL PRIMARY KEY

Why UUIDs:

• No collision across systems
• Safe for client-side generation
• Harder to guess (security)
• Works with any scale

3. Timestamps

Always include these:

sql
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

4. Soft Delete Pattern

Instead of deleting:

sql
-- Add deleted_at column
deleted_at TIMESTAMP WITH TIME ZONE

-- Query with filter
SELECT * FROM users WHERE deleted_at IS NULL;

-- Delete becomes update
UPDATE users SET deleted_at = NOW() WHERE id = 'uuid';

---

Indexing Strategy

1. Index Every Foreign Key

sql
-- Always index foreign keys
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);

2. Index Types

---

Security Best Practices

1. Enable Row Level Security (RLS)

Supabase/Neon have RLS by default:

sql
-- Enable RLS on table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Policy: Users can see their own data
CREATE POLICY "Users can view own data"
ON users FOR SELECT
USING (auth.uid() = id);

2. Use Least Privilege

Create user with minimal permissions:

sql
-- Create read-only user
CREATE USER readonly WITH PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Create app user with necessary permissions
CREATE USER app_user WITH PASSWORD 'password';
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public TO app_user;

---

Performance Optimization

1. Connection Management

javascript
// Use connection pooling
const pool = new Pool({
  host: process.env.DB_HOST,
  max: 20, // Pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

2. Query Optimization

Use EXPLAIN ANALYZE:

sql
EXPLAIN ANALYZE
SELECT * FROM users
WHERE email = '[email protected]';

3. Pagination

Use cursor-based pagination:

sql
-- Good: Cursor-based
SELECT * FROM posts
WHERE created_at < '2024-01-01T00:00:00Z'
ORDER BY created_at DESC
LIMIT 10;

---

Common Mistakes to Avoid

Mistake #1: Not Setting Up Foreign Keys

Problem: Orphaned records, data inconsistencies

Solution: Always use foreign key constraints

Mistake #2: Missing updated_at

Problem: No audit trail, caching issues

Solution: Always include updated_at

Mistake #3: Over-Indexing

Problem: Slower writes, increased storage

Solution: Index strategically

---

Monitoring Your Database

Key Metrics to Track

---

Quick Setup Scripts

1. Create Users Table

sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    metadata JSONB DEFAULT '{}'::jsonb,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;

-- Enable RLS if using Supabase
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own data" ON users FOR SELECT USING (auth.uid() = id);

2. Create Subscriptions Table

sql
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    plan VARCHAR(50) NOT NULL DEFAULT 'free',
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    stripe_customer_id VARCHAR(255),
    stripe_subscription_id VARCHAR(255),
    current_period_start TIMESTAMP WITH TIME ZONE,
    current_period_end TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);

---

The Bottom Line

PostgreSQL is the right choice for most MVPs.

Key takeaways:

• Use managed hosting (Supabase, Neon)
• Design schema with scaling in mind
• Index foreign keys and frequently queried columns
• Enable RLS for security
• Monitor performance from day one
• Test backup and restore regularly

At Startupbricks, we've designed dozens of database schemas for startups. We can help you set up PostgreSQL correctly from the start.