Demystifying Database Performance: Hard Lessons from Production

There is a classic myth among developers: "If your app is lagging, just scale up the database server."

At my previous company, I happened to be the developer who managed everything end-to-end, from the frontend to the initial server deployment. So yes, database and infrastructure management fell into my hands as well.

I was tempted by this shortcut too. When my PostgreSQL database at that company started choking under the load of Kubernetes pods and automation workflows, my first reaction was to open the cloud dashboard, click on the resources, and double the CPU cores from 2 to 4, and RAM from 2GB to 4GB.

The result? The app was still slow, and occasionally threw FATAL: too many clients already errors. Meanwhile, the database CPU usage was sitting comfortably under 10%. That was the exact moment I realized I had just performed a foolish over-provisioning workaround to mask a flawed database architecture.

Database optimization isn't about how much RAM you throw at the server; it's about how efficiently your database engine runs under the hood. Here are some of the "engineering scars" and expensive lessons I gathered from tuning database performance in the production environments of my previous company.

1. The Connection Pooling Drama (When Kubernetes Attacks Postgres)

This was the first architectural mistake I made. In my company's Kubernetes cluster, I used Drizzle ORM for several microservices. By default, every time an application pod spins up, Drizzle spawns its own connection pool (usually defaulting to 10-20 connections per instance).

As traffic grew and Kubernetes scaled my application pods from 2 replicas to 10, the PostgreSQL database instantly choked due to connection exhaustion (connection limit exceeded).

The Lazy Workaround: Scaling Up `max_connections`

My second mistake was simply raising max_connections in Postgres config from 100 to 500. Do not do this. Every active connection in Postgres spawns a physical backend process (forked process) in the OS, consuming around 10MB of RAM per connection. With 300+ active connections, your database will spend more time handling CPU context switching between processes than executing the actual queries.

The Real Fix: Setting Up PgBouncer (Not as Simple as Medium Tutorials)

Eventually, I deployed PgBouncer as a connection pooler in front of Postgres. PgBouncer keeps a pool of warm connections to the database, and our application only talks to PgBouncer.

However, setting up PgBouncer has its own traps:

Transaction Mode vs. Session Mode: PgBouncer supports different pooling modes. Initially, I configured Transaction Mode because it's the most connection-efficient. However, my Drizzle schema migrations immediately broke because Drizzle requires Session Mode (which locks a connection per session to support temporary tables and ad-hoc commands).
My Solution: I ended up exposing two ports on PgBouncer. One port uses Session Mode exclusively for migrations and deployments, while the other port uses Transaction Mode for daily API traffic. It was a bit of setup overhead, but our database memory footprint dropped by 40% and we never saw connection limit errors again.

2. Indexing: From Zero to Overkill (The Write Tax)

Everyone knows that if a SELECT query is slow, you should throw an index at it. But I once got burned by being too index-happy.

On a transaction log table that ingests thousands of rows daily, I confidently created B-Tree indexes on almost every column I filtered (status, created_at, user_id, amount, gateway).

The Consequence: Bulk Inserts Choked

Read queries became blazingly fast (under 5ms). However, when the system started running workflows that bulk-inserted thousands of transaction records at once, the database server CPU spiked to 100% and the insert rate slowed to a crawl.

Why? The Write Tax. Every time a new row is inserted, Postgres has to update the B-Tree structure for every single index I created.

Lessons Learned:

Drop Unused Indexes: Run audit queries to find indexes that are never touched by the query planner but are still updated on every write. I found 3 redundant indexes and dropped them immediately.
Trust EXPLAIN ANALYZE: Stop guessing. Always run EXPLAIN ANALYZE SELECT ... before creating an index. Check whether the query planner actually performs an Index Scan or if it defaults to a Seq Scan (Sequential Scan) because the table is too small or the column cardinality is too low.

3. SQL Antipatterns That Make Your DB Cry

Beyond infrastructure, how we write queries often makes the database suffer. Here are two antipatterns I regularly find (and fix):

1. The `SELECT *` Disease

I used to be lazy and write SELECT * in raw queries or ORM models to avoid writing column names one by one. In production, this is dangerous.

If your table has large columns (like JSONB or long TEXT fields), Postgres has to read this data from TOAST storage (separate physical storage), causing disk I/O to spike.
SELECT * also prevents the query planner from performing an Index-Only Scan (retrieving data directly from the index without reading the actual table row).

2. The N+1 Query Problem

This is a common issue with modern ORMs. You want to fetch a list of 50 transactions and display their user details. If you don't use Eager Loading or Joins, your ORM will run 1 query to fetch the transactions, followed by 50 separate queries to fetch the user details for each transaction. Your database spends unnecessary resources handling network roundtrips.

4. Caching: The Database's Shield (Watch Out for Stale Data)

The fastest database query is the one you never make. At my previous company, I set up Redis as a caching layer in front of the database for data that is frequently read but rarely changed (like product catalogs or system configurations).

I implemented the Cache-Aside pattern: the app checks Redis -> if found (Cache Hit), return immediately -> if not found (Cache Miss), query the database -> write to Redis -> return to user.

The Stale Balance Tragedy (Cache Invalidation Failure)

But caching has a major pitfall: Cache Invalidation. I once forgot to set a TTL (Time-To-Live) and failed to clear the Redis cache when a new transaction occurred that updated the user's balance.

The result? The user paid via Midtrans, and their balance was updated in Postgres, but their dashboard still showed zero because the application was reading stale data from Redis. The user panicked and complained to customer support.

Lesson: Always set a realistic TTL (never cache indefinitely unless you have a robust cache invalidation pipeline), and make sure you run a DEL command or update the Redis cache in the same database transaction where the data changes.

Quick Production Readiness Checklist

To save yourself from a 3:00 AM server crash, here is a quick database optimization checklist:

Audit Connections: Use PgBouncer or similar if your app runs on serverless/K8s with multiple replicas.
Check Indexes: Don't index everything. Ensure indexes only exist on foreign keys or high-cardinality filter columns.
Profile Queries: Identify queries taking > 100ms and analyze them using EXPLAIN ANALYZE.
Explicit Columns: Eliminate all SELECT * from your production queries.
Cache Strategy: Use Redis for heavy aggregation queries, but make sure your cache invalidation logic is thoroughly tested to prevent stale data.

Your database is the heart of your application. Treat it with efficient queries and a clean connection architecture, rather than blindly scaling up the RAM capacity on your cloud provider's dashboard. wkwk