Database Dating

Let’s face it: as developers, we love the idea of simplicity. We dream of single-file databases, zero-config deployments, and architectures so flat you could slide them under a door.

That’s why we all have a soft spot for SQLite. It’s the "comfortable hoodie" of databases—easy to wear, requires zero effort, and feels great... until you have to wear it to a black-tie gala. Or, in this case, a production environment with concurrent users.

We recently found ourselves at this exact crossroads on a greenfield internal application—let's call it "The Inventory Service."

The Temptation

The initial requirements were modest:

• Users: "Maybe 10 internal users."
• Scope: "Just a simple CRUD app."

The temptation to slap a production.sqlite3 file into a volume and call it a day was strong. It would have saved setup time, cloud costs, and complexity. But we’ve been burned before by "temporary" solutions that become permanent legacy nightmares.

Instead of going with our gut (or our laziness), we decided to let the data drive the car. We ran a brutal load testing gauntlet to see if our comfortable hoodie could actually survive a rigorous workout.

Spoiler alert: It didn't. But the way it failed was fascinating.

Why You Should Care (Beyond "Postgres is Better")

You might be thinking, "I know Postgres is better for production, tell me something new."

The value here isn't just the conclusion—it's the process of architectural validation. The difference between "I think we need Postgres" and "I know we need Postgres because this graph shows a 96% failure rate at 10 users" is the difference between a philosophical debate and a solved problem.

• Evidence Beats Arguments: When you have a test report showing response times spiking by 4,000% (from 900 ms to 8,000 ms), you don't need to argue with stakeholders about "best practices." The data provides an objective deadline for migration.
• Discovering Hidden Thresholds: We learned that performance degraded not just with more concurrent users, but critically as the database size grew (e.g., beyond 60,000 records).
• Preventing "Success Disaster": Validating your foundation before the house is built saves you from trying to swap the foundation while people are sitting in the living room.

Stress Testing the Hoodie

We set up a custom load testing suite (using Ruby with concurrent-ruby and httparty, though tools like k6 or Gatling work just as well) to simulate realistic behavior: authenticated user sessions, reading heavy datasets, and creating new records.

We tested two main variables:

1. Concurrency: 1 user up to 50 concurrent users.
2. Data Volume: A fresh DB (2,500 records) vs. a mature DB (90,000 records).

The SQLite Meltdown: A Dual-Variable Failure

At a small volume (2,500 records), SQLite held up well. But once we filled the database with 90,000 records—a realistic number for a few months of usage—the wheels came off.

The underlying issue is SQLite’s architecture: it relies on file-level locking. When one user initiates a write operation, others are blocked. As the database file grows, the time required to acquire and release those locks under load explodes.

Test Results: SQLite

We observed a 96.67% database error rate at just 10 concurrent users on a mature database. This failure mode—where response times jump from a slow crawl to a catastrophic crash—confirmed that SQLite was the wrong tool for any application requiring even modest, sustained concurrency.

The PostgreSQL Redemption

We ran the exact same gauntlet against PostgreSQL (using the postgres:15-alpine image). Unlike SQLite, Postgres uses row-level locking, allowing multiple concurrent operations to proceed efficiently.

Test Results: PostgreSQL

PostgreSQL maintained a 100.0% success rate across all scenarios. While the response time climbed under extreme load (physics is still physics), the database never crashed or returned a concurrency-related error.

The Toolbox: The "Best of Both Worlds" Architecture

Having made the data-driven decision to choose PostgreSQL, we didn't want to lose the deployment simplicity that made SQLite so attractive in the first place. We realized we didn't need the overhead of a complex, managed cloud instance (like RDS) just yet.

The solution wasn't a hack—it was a strategic implementation of the Sidecar Architecture. This approach gave us the robustness of a production-grade database with the lightweight operational footprint of a local file.

Instead of provisioning external infrastructure, we ran the PostgreSQL container inside the same Kubernetes Pod as our application container.

 1spec:
 2  containers:
 3  - name: inventory-service-app
 4    image: my-company/inventory-service:latest
 5    env:
 6      - name: DATABASE_URL
 7        # The magic is localhost
 8        value: "postgres://user:pass@localhost:5432/app_db" 
 9  
10  - name: postgres-sidecar
11    image: postgres:15-alpine
12    volumeMounts:
13      - mountPath: /var/lib/postgresql/data
14        name: postgres-data

Pragmatic Value:

• Low Latency: The application talks to the DB over localhost:5432, eliminating network hops.
• Dev/Prod Parity: Docker Compose runs the exact same way locally, reducing "it works on my machine" issues.
• Future-Proofing: We get the reliability of Postgres immediately. When we eventually outgrow the single-pod resource limits, we simply change the DATABASE_URL to point to a managed cloud service without rewriting any application code.

Pro Tips from Someone Who's Been Burned

1. Don't Trust "Baseline" Tests

Testing with 1 concurrent user tells you nothing. Testing with an empty database tells you less than nothing. Always fill your test DB with "Future You" levels of data (we used 90k records) before running load tests. The concurrency issue often only appears when both load and volume are high.

2. Set Latency, Not Just Error, Thresholds

A "Success Rate: 100%" with an "Avg RT: 8,000 ms" is a graceful failure. It didn't crash, but every user rage-quit 7 seconds ago. Define an acceptable latency SLA (e.g., 95% of requests must be under 500 ms) and treat exceeding it as an official test failure.

3. The WAL Mode Illusion (SQLite)

While SQLite's Write-Ahead Logging (WAL) mode helps mitigate some locking issues, it does not fundamentally change the file-level lock mechanism. Our heavy concurrent write tests confirmed that WAL mode merely delays the inevitable performance cliff when concurrent writes stack up.

Parting Wisdom

In software architecture, sometimes the fastest path is a shortcut to a technical debt mountain. We took the extra week to set up the two databases and run the load tests. That investment saved us from an emergency database migration months down the road—the kind of 3AM firefight that burns out engineers and costs thousands in lost productivity.

Don't guess which database to choose; measure it. Let the data tell you which one to swipe left on.

Now, go forth and stress test your assumptions.