Why We Chose SQLite for a Production Integration System

PostgreSQL isn't always the answer. When we built TurfDrive—a system syncing Pipedrive CRM with Ostendo ERP—we chose SQLite. A year later, handling 5,000+ deals and running 24/7, it was the right call.

Here's why.

The Default Choice (That We Skipped)

If you've built Rails apps in the last decade, you've probably reached for PostgreSQL by default. It's robust, scalable, and has excellent Rails support. For multi-server deployments, read replicas, and high-write workloads, Postgres is often the right tool.

But TurfDrive didn't need any of that.

What TurfDrive Actually Needs

TurfDrive is an integration system that:
- Runs on a single server
- Syncs data between two external systems
- Handles moderate write volume (~500-1000 records/day)
- Performs more reads than writes (checking sync status, querying for updates)
- Needs to be simple to deploy and maintain
- Requires reliable background job processing

The data model is straightforward: customers, jobs, deals, sales orders, and sync logs. No complex joins. No full-text search. No geospatial queries.

Why SQLite Made Sense

1. Zero Operational Overhead

With PostgreSQL, you need:
- A database server process
- Connection pooling configuration
- Backup and restore procedures
- Monitoring and alerting
- Version upgrades and migrations
- Potentially separate infrastructure

With SQLite:
- It's a file on disk
- No server process to manage
- Backups are cp database.sqlite3 database.backup.sqlite3
- Version upgrades? Update the gem.
- Monitoring? Check file size and query performance.

For a single-person operation managing multiple client projects, this simplicity is invaluable.

2. Performance Is More Than Adequate

SQLite gets a bad rap for "not being production-ready," but the numbers tell a different story:

Read performance: Faster than Postgres for single-row lookups (no network latency)
Write performance: 1000+ inserts/second in WAL mode
Concurrent reads: Unlimited
Concurrent writes: Serialized (but fast enough for our workload)

TurfDrive's bottleneck isn't the database—it's the external API calls to Pipedrive and Ostendo. While waiting on a network request, SQLite is sitting idle.

Benchmark: Syncing 100 deals takes ~30 seconds. Of that, 28 seconds is API calls, 1 second is business logic, and 1 second is database operations.

3. Rails 8 + Solid Queue = No Redis Needed

Rails 8 introduced Solid Queue, a database-backed job queue that runs on SQLite (or Postgres). This means:
- No separate Redis instance to manage
- Jobs persist across restarts
- Query job status directly via SQL
- One less service to monitor

For TurfDrive, this was huge. We get reliable background jobs without adding infrastructure complexity.

Our setup:
- SQLite for application data
- SQLite for job queue (same database)
- Sidekiq (or Solid Queue) processing jobs in the background

Everything in one process, one database file.

4. Deployment Simplicity

TurfDrive deploys via Kamal (from the Rails team) with Docker. The entire deployment is:

Build Docker image
Push to server
Kamal swaps containers with zero downtime
Done

No database migrations to run separately. No connection string secrets to manage. No database server to restart. The database is just part of the application.

Rolling back? Swap back to the old container. The database file is mounted as a volume, so data persists across deployments.

5. Backup Strategy Is Trivial

Our backup process:
```bash

!/bin/bash

Run every hour via cron

cp /app/storage/production.sqlite3 /backups/production-$(date +%Y%m%d-%H%M%S).sqlite3

Keep last 7 days of hourly backups

find /backups -name "production-*.sqlite3" -mtime +7 -delete
```

That's it. No pg_dump. No WAL archiving. No point-in-time recovery complexity.

To restore: cp backup.sqlite3 production.sqlite3. Restart app. Done.

We also sync backups to S3 daily, but the backup process itself is dead simple.

The Trade-Offs (And Why They Don't Matter Here)

1. No Horizontal Scaling

SQLite is tied to a single server. You can't add read replicas or shard across multiple databases.

Why it doesn't matter: TurfDrive will never need horizontal scaling. The workload is bounded by the number of deals and customers, which grows slowly. If we hit SQLite's limits (unlikely), we'd scale vertically first—more CPU, more RAM, faster disk.

And if we ever truly need Postgres? Migration is straightforward. Rails abstracts the database layer, so the code doesn't change.

2. Write Concurrency

SQLite serializes writes. Only one write transaction can happen at a time.

Why it doesn't matter: TurfDrive's writes are:
- Background jobs (processed sequentially)
- API webhooks (low volume, async)
- Admin dashboard updates (infrequent)

We're not building Twitter. We're syncing a few hundred records per day. SQLite's write throughput (1000+ writes/sec) is orders of magnitude beyond what we need.

3. Less Mature Extensions

PostgreSQL has rich extensions: full-text search (pg_trgm), JSON operators, geospatial (PostGIS), time-series (TimescaleDB).

Why it doesn't matter: We don't need any of that. Our queries are simple:
- Find records that need syncing
- Lookup customers by Ostendo ID
- Check sync status
- Store error logs

SQLite's built-in features cover 100% of our use cases.

When SQLite Is Not the Right Choice

To be clear: SQLite isn't always the answer. Skip it if you need:
- Multiple application servers hitting the same database
- High write concurrency (many writers at once)
- Complex queries that benefit from Postgres features
- Large datasets (100GB+, though SQLite handles terabytes fine)
- Team unfamiliarity (if your team only knows Postgres, stick with it)

But for single-server Rails apps with moderate workloads? SQLite is a fantastic choice.

Production Stats (1 Year In)

5,000+ deals synced
Database size: 1.2GB
Avg query time: <5ms
Background jobs processed: 150,000+
Database-related incidents: 0

Zero downtime due to database issues. Zero performance problems. Zero "why is Postgres using all the RAM" debugging sessions.

The Philosophy: Match Complexity to Need

Software engineering is about trade-offs. The "right" tool depends on your constraints:

Building a multi-tenant SaaS with thousands of concurrent users? Use Postgres.
Building an internal dashboard with 10 users? SQLite might be perfect.
Building a sync system on a single server? SQLite is excellent.

TurfDrive doesn't need the complexity of Postgres. It needs reliability, simplicity, and low operational overhead. SQLite delivers all three.

Takeaways

Question defaults. Just because everyone uses Postgres doesn't mean you should.
Match tools to workload. TurfDrive's workload fits SQLite perfectly.
Operational simplicity matters. One less service to manage = fewer things to break.
Measure what matters. Our bottleneck is API latency, not database performance.
Rails 8 makes this easier. Solid Queue + SQLite = no external dependencies.

If you're building a single-server Rails app, consider SQLite. You might be surprised how far it takes you.

Next in this series: Building bidirectional sync and handling conflicts when two systems update the same data.