Horizontal Scaling: Solving One Problem, Creating Another

When learning system design, we are almost always fed the exact same doctrine: start with one API + one Database, then as traffic increases, add a Load Balancer, Redis, a Separate Database Server, and eventually Read Replicas.

The problem is that this standard pattern is built on a single assumption: every user must crowd into the same shared database. However, if your application’s data characteristics allow it to be segregated per user from day one, blindly following this pattern becomes a complexity trap that makes your system increasingly fragile.

The Sin of Mainstream Architecture: Compounding New Problems

In conventional architecture, all users share the same database tables. As server load increases, a developer’s first reflex is usually to add more infrastructure layers:

[User] 
  └─► [Load Balancer]
        ├─► [API Instance 1] ──┐
        │                      ├─► [Redis Cache]
        └─► [API Instance 2] ──┴─► [DB Server (Postgres)]

Enter fullscreen mode Exit fullscreen mode

While the initial intent is to solve scaling challenges, the execution creates a dramatic contradiction:

1. Seeking Fault Tolerance, but Adding a Single Point of Failure

To distribute traffic, we introduce a Load Balancer and spin up additional API instances. However, because user requests now bounce between instances, their session state is lost.

The Solution?
We are forced to introduce Redis for centralized session storage.

The Absurd Result: We added a Load Balancer to make the system fault-tolerant, but now, if Redis crashes, the entire application goes down.

2. Seeking Easy Scaling, but Paying with Complex Bugs

In theory, spinning up another API server is just a click of a button. However, once a single business transaction touches multiple servers simultaneously, standard database transactions (BEGIN...COMMIT) no longer work.

We are forced to implement complex patterns like Two-Phase Commit or the Saga Pattern. We chose horizontal scaling in pursuit of simplicity, but ended up trapped in race conditions and data inconsistency issues that are notoriously hard to debug in production.

A Radical Solution: The File-per-User Architecture

Instead of forcing all data into one giant database, this approach offers a remarkably simple concept: give every user their own separate SQLite file.

/data
  ├── user_01.db
  ├── user_02.db
  └── global_index.db  <-- For public metadata/aggregates only

Enter fullscreen mode Exit fullscreen mode

So, what happens when the application needs public features like a feed, global search, or cross-user data aggregation?

The Answer: Separate the read path.

Private source data remains safely stored in each user’s individual file. Whenever a user publishes something, the application simply writes a lightweight metadata entry (write-on-publish) to a dedicated aggregate database. If this aggregate database becomes corrupted, its data can easily be re-indexed from the users’ primary files.

By eliminating a separate database server and embedding the SQLite engine directly inside the application process, system mechanics shift naturally:

• Data Leaks Become Structurally Impossible: In a shared database, a single bug forgetting a WHERE user_id = ? clause can leak data between users. In this approach, the application connection strictly opens the target user’s file. The risk of data leakage is eliminated not by developer vigilance, but by physical file boundaries.
• Radically Simple Backups: You no longer need to execute gigabyte-sized database dumps or manage complex log replication pipelines. To back up a user’s data, you simply stream or copy their single .db file to Object Storage (e.g., using Litestream).
• Zero Network Latency: Because the database engine runs inside the application process itself, every query is processed directly in memory and local disk. Network calls from the API server to the DB server—which typically consume milliseconds—vanish entirely.

The Rationality of Vertical Scaling: Why It Is More Than Enough

A reasonable question arises: “If the database lives as local files, how do we handle traffic spikes? We can’t just slap a Load Balancer in front of it.”

The Answer: Use Vertical Scaling (scale up the machine’s resources).

In the modern cloud era, scaling CPU and RAM on a single server is often overlooked because it is deemed less fashionable than running dozens of small nodes. Yet, the logic behind vertical scaling is stronger than ever: a single modern machine can handle tens of thousands of requests per second provided its performance isn’t bogged down by database network latency and distributed state synchronization.

The physical capacity of a single server today has reached almost absurd levels. On Google Cloud Platform (GCP), a standard Virtual Machine can offer up to 224 vCPUs. In Memory-Optimized categories (such as the M or X4 series), limits reach up to 1,920 vCPUs and a massive 32 Terabytes (32,768 GB) of RAM on a single machine. These specs prove that the physical ceiling of a single server far exceeds the compute needs of 99.9% of web applications on the market.

Based on this reality, the architecture divides infrastructure responsibilities pragmatically:

• Compute (CPU/RAM): Scaled vertically. You leverage the capacity of a single machine to execute all application logic and embedded database operations in one place without network overhead.
• Storage (Disk): Scaled horizontally without limits. Storage capacity is not constrained by the machine itself; you can attach new block storage volumes (e.g., up to hundreds of Terabytes) instantly with zero downtime.

Ultimately, instead of stressing over a complex architecture built for hypothetical hyper-scale traffic, you choose a simple, rational system: one application, one server, and zero network overhead.

When to Use This Pattern?

Highly Recommended If:

• Most user activity takes place within their own isolated data context (SaaS, E-commerce, Social Platforms, Productivity Tools).
• You prioritize operational simplicity and low maintenance overhead with a small engineering team.
• You want to ship and deploy applications rapidly without managing server clusters.

Avoid If:

• Your application is dominated by ad-hoc, cross-user queries with unpredictable read patterns—making it difficult to structure a lightweight aggregate database.
• The total traffic and compute load of a single application has been empirically proven to exceed the physical limits of the largest single server available on the market.

Conclusion

Load balancers, Redis, and separate database clusters are not badges of system maturity—more often than not, they are technical debt stemming from poor architectural choices made early on.

If your user data can be isolated per file, stop bundling it into one giant shared database. Separate the data, offload global search to a lightweight aggregate index, drop the separate database server, and enjoy a system that is significantly faster, more stable, and easier to maintain.

Q&A: Addressing Operational and Security Concerns

“A single server is a Single Point of Failure (SPOF) & hard to Failover!”

That mindset belongs to an era when SQLite was viewed merely as a passive local disk file. The modern SQLite ecosystem has solved this:

• For Disaster Recovery (DR): Use Litestream. It performs byte-level streaming replication in real-time to Object Storage (S3/Cloudflare R2). If the primary server is completely destroyed, you can restore the latest state to a fresh server in minutes.
• For Automatic Failover (High Availability): Use LiteFS. LiteFS creates a multi-node SQLite cluster where read requests are handled locally and write requests are automatically forwarded to the primary node. (To be intellectually honest: adopting LiteFS means re-introducing a degree of distributed system complexity that was initially avoided. However, this aligns with the core philosophy: add infrastructure complexity only when HA requirements are genuinely proven, not on day one).

“How do you handle Zero-Downtime Deployments?”

You do not need Kubernetes just to achieve zero-downtime deployments! There are two straightforward approaches:

• At the Server Level: Use a reverse proxy like Caddy, Nginx, or Envoy to execute hot-reloads. Alternatively, leverage Linux’s systemd Socket Activation—the OS buffers incoming requests for a few milliseconds at the kernel level while the application process switches over, resulting in zero dropped requests.
• At the Edge Level: Utilize Cloudflare (via Cloudflare Tunnel). You can re-route traffic to a new port or binary version via API, get free DDoS protection, and serve automatic maintenance pages if the server ever requires a full reboot.

“Complexity is just shifted to the Application Layer!”

That is actually a net positive!

Application-layer complexity can be tested automatically using standard unit and integration tests on your local machine (localhost). In contrast, distributed infrastructure complexity (such as split-brain scenarios in a DB cluster or subtle Redis race conditions) manifests as ghost bugs that are nearly impossible to reproduce locally and only surface when production breaks.

Would you rather deal with complex test suites or be paged at 3 AM because production is down?

“What about Analytics / Data Warehouse Queries?”

No one should run heavy analytical queries directly against production SQLite files. Even in conventional distributed architectures, best practices strictly dictate never pointing OLAP/analytics queries at a production OLTP database.

Because all .db files are backed up automatically to S3 via Litestream, the Data team can point query engines like DuckDB or ClickHouse directly at those S3 snapshots. DuckDB can query thousands of SQLite files in parallel with exceptional speed, leaving the production database completely unburdened.

“Hard to pass Compliance Audits (GDPR/ISO) & handle Backups!”

The File-per-User architecture actually simplifies data isolation and compliance governance:

• Right to be Forgotten (GDPR Erasure): In a shared database, deleting a user’s data requires cascading DELETE statements across dozens of tables, running the risk of table locks or orphaned records. In this approach, a user’s private data is completely isolated. You simply delete their user.db file and clear their entry in the aggregate database. (Ensure you configure proper **Lifecycle / Retention Policies* on your Object Storage where Litestream streams backups, so historical copies are permanently purged according to regulatory windows).*
• Data Portability: If a user requests a full export of their personal data, you hand them a copy of their single .db file—no complex export scripts required.

“Career-Safety Principle (Nobody gets fired for buying IBM)”

This is the real underlying reason: Fear.

Many choose complex architectures not because the application requires it, but out of fear of blame if things break, or simply to polish their resume with industry buzzwords (Resume-Driven Development).

The mark of a mature Engineering Leader is having the courage to choose the simplest possible architecture that solves the business problem while incurring the lowest possible distributed infrastructure tax.