The Monolith’s Weakest Link

You can scale your Frontend servers infinitely (just adds more nodes behind the Load Balancer). You can scale your API servers infinitely (Serverless functions). You cannot easily scale your Primary Database. There is (usually) only one source of truth. One disk. One master process. When traffic spikes, the CPU hits 100%, and query latency goes from 10ms to 10,000ms. The site dies. Scaling the Database is the hardest problem in backend engineering.

Why Maison Code Discusses This

At Maison Code, we see valid startups die because of SQL. They write code like SELECT * FROM Orders. It works fine with 100 orders. It crashes the server with 1,000,000 orders. We optimize databases for High Cardinality and High Throughput. We know that “Adding Indexes” is not enough. You need architecture changes (Caching, Replication, Partitioning).

Strategy 1: Indexing (The Low Hanging Fruit)

Most performance problems are missing indexes. Scenario: Searching for a user by email. Query: SELECT * FROM users WHERE email = 'alex@example.com'. Without Index: The DB scans 1,000,000 rows (Sequential Scan). O(N). With Index (B-Tree): The DB jumps directly to the email. O(log N). Cost: Indexes slow down Writes (INSERT/UPDATE), because the index tree must be updated. Rule: Index columns used in WHERE, JOIN, and ORDER BY.

Strategy 2: Caching (Redis)

The fastest query is the one you don’t make. SQL is slow (Disk I/O, CPU math). Redis is fast (In-Memory, Key-Value). Pattern: Look-Aside Cache.

async function getProduct(id) {
  // 1. Check Cache
  const cached = await redis.get(`product:${id}`);
  if (cached) return JSON.parse(cached);

  // 2. Query DB (Slow)
  const product = await db.query('SELECT * FROM products WHERE id = ?', [id]);
  
  // 3. Save to Cache (TTL 5 mins)
  await redis.set(`product:${id}`, JSON.stringify(product), 'EX', 300);
  
  return product;
}

This offloads 90% of read traffic from the DB.

Strategy 3: Read Replicas

Even with caching, reads can overwhelm the CPU. Solution: Create copies of the database (Replicas).

Primary Node: Handles Writes (INSERT, UPDATE, DELETE). Syncs changes to Replicas (Async).
Replica Nodes: Handle Reads (SELECT). Trade-off: Replication Lag. The user updates their profile. They refresh the page immediately. They hit a Replica that hasn’t received the update yet. They see the old profile. Fix: “Read your own writes”. For the current user, force read from Primary. For public data, read from Replica.

Strategy 4: Connection Pooling (PgBouncer)

Postgres has a limit on concurrent connections (e.g., maintain 100). Serverless functions (Lambda) spawn 1,000 instances. If 1,000 instances try to open a connection, Postgres crashes. Solution: A Proxy (PgBouncer / Supabase Pooler). The Proxy holds 100 open connections to the DB. The 1,000 Lambdas talk to the Proxy. The Proxy queues the queries and executes them using the 100 connections. This is mandatory for Serverless architectures.

Strategy 5: Vertical Partitioning (Splitting Tables)

Table Users has bio (text, huge) and last_login (date, small). If you frequently query last_login, but bio makes the row size huge, the DB reads too much data from disk. Split it: Table User_Core (id, email, password, last_login). Table User_Profile (id, bio, avatar). Now SELECT last_login FROM User_Core is blazing fast because more rows fit in RAM pages.

6. Materialized Views: Pre-Calculating Success

“Show me the total revenue for 2024.” SELECT SUM(amount) FROM orders WHERE year = 2024. This scans 10 million rows. It takes 5 seconds. Solution: Materialized View. CREATE MATERIALIZED VIEW annual_revenue AS SELECT ... Postgres calculates the result and saves it to disk as a physical table. Querying it takes 1ms. Trade-off: The data is stale. You must REFRESH MATERIALIZED VIEW periodically (e.g., every hour). Perfect for Dashboards where “Real Time” isn’t strictly required.

7. Database Autoscaling (Serverless Aurora)

What if you don’t want to manage replicas? Amazon Aurora Serverless v2. It automatically adds CPU/RAM when traffic spikes, and shrinks when traffic drops. It scales in fractional increments (ACUs). It effectively gives you the “Infinite Scaling” promise of NoSQL, but with full SQL compatibility. It is expensive, but cheaper than hiring a DBA to manually provision read replicas at 3 AM.

9. Time-Series Data (TimescaleDB)

“Log every page view.” “Log every thermometer reading.” Standard Postgres chokes on inserting 10,000 rows/second into a single table. Indexes become fragmented. Solution: TimescaleDB (Postgres Extension). It automatically partitions data by time (“Hypertables”). orders_2024_01, orders_2024_02. You query it as one table orders, but physically it is small chunks. Dropping old data is instant (DROP TABLE orders_2020). DELETE FROM orders WHERE year=2020 takes hours.

10. Advanced Partitioning Strategies

Vertical Partitioning splits columns. Horizontal Partitioning splits rows. By Region: users_eu, users_us. This helps with GDPR (Data Residency). “European data never leaves the EU server.” Postgres Declarative Partitioning makes this manageable. This is how we scale SaaS apps to millions of tenants without buying a Mainframe.

11. The Skeptic’s View

“Just use DynamoDB / NoSQL. It scales infinitely.” Counter-Point: NoSQL scales writes, but it fails at Relational Data. “Show me all orders by users in Paris who bought red shoes.” In SQL: One query. In NoSQL: A nightmare of application-side joins and multiple fetches. Most e-commerce data is relational. Stick to SQL (Postgres) until you reach Google scale.

FAQ

Q: When to use Sharding? A: Almost Never. Sharding (splitting data across multiple servers based on User ID) is extremely complex. You lose ACID transactions across shards. Don’t shard until you have > 10 TB of data. Vertical Scaling (Bigger Server) works surprisingly well up to that point.

Q: ORM vs Raw SQL? A: Prisma/Drizzle (ORM) for productivity. Raw SQL for complex reports or super-optimized queries. Modern ORMs are good enough for 99% of queries.

Conclusion

The database is the heart of your stack. If it stops beating, the app dies. Treat it with respect. Index your foreign keys. Cache your hot paths. And never, ever, run DROP TABLE on Friday.

Database choking?

If your queries are timing out or your CPU is redlining, Maison Code can optimize your schema. We analyze query plans, implement caching strategies, and architect replica sets.

Queries too slow?

We optimize Database Architecture using Indexing, Caching, and Read Replicas to handle massive scale. Hire our Architects.