SQLite vs PostgreSQL in Rails 8

As the Rails framework continues to evolve with version 8, so too do the considerations for building robust and scalable applications.
While Rails offers excellent abstraction over databases, the underlying engine you choose SQLite or PostgreSQL being two prime contenders can significantly impact everything from development speed to production stability.
SQLite, the default out-of-the-box, offers ease of setup, perfect for getting started quickly. PostgreSQL, on the other hand, brings enterprise-grade features and scalability crucial for growing applications. Choosing between them isn’t always straightforward.
This guide will dissect the key differences, performance considerations, and feature sets of SQLite specifically within a Rails 8 environment, empowering you to pick the right foundation for your next high-traffic deployment.

Rails 8’s SQLite Adapter: A Systems Engineering Perspective

Rails 8’s SQLite adapter introduces significant optimizations that leverage SQLite’s embedded architecture, transforming it from a development-only database to a production-ready solution for specific workloads.

• • SQLite WAL Mode Mechanics:

The powerful move for SQLite concurrency was the introduction of Write-Ahead Logging (WAL) mode.

• • • Traditional Rollback Journal: Writes lock the entire database, blocking readers. Changes are written to a separate rollback journal file first, then to the main DB file.
    • WAL Mode: Writes are appended to a separate .wal file. Readers access the main database file directly. Checkpoints periodically merge the .wal file back into the main database. This allows readers and one writer to operate concurrently.

SQLite’s WAL mode (Write-Ahead Logging) enables readers to map the database file and potentially the WAL file into their process space and leverages shared memory. This avoids the Inter-Process Communication (IPC) overhead inherent in PostgreSQL’s client-server model, where every query involves network round trips (even if just over a local socket) and context switching.

• • Code-level analysis of Rails’ PRAGMA optimizations:

Rails 8 (and recent versions) often default to or encourage optimal PRAGMAs for production SQLite usage. You’ll typically see this in config/database.yml:

                    
# config/database.yml
production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 25  # Optimized for modern multi-core systems
  pragmas:
    journal_mode: WAL     # Enable Write-Ahead Logging
    synchronous: NORMAL   # Balance between durability and performance
    cache_size: -64000    # Allocate ~64MB of RAM for page cache
    mmap_size: 17179869184  # 16GB memory mapping for zero-copy reads
    busy_timeout: 5000    # 5s timeout for lock acquisition

# The Rails 8 SQLite adapter automatically applies these pragmas
# Rails/ActiveRecord code that configures SQLite connection
                    
                

• journal_mode=WAL: Enables the concurrency model described above.
• synchronous=NORMAL: Balances safety and speed. Data is durable after checkpoints, but not necessarily after every transaction commit (unlike FULL). A crash might lose recent transactions in the WAL file, but the main DB remains consistent. This is often acceptable, especially with replication tools.

Concurrency & Locking:

• • SQLite: Offers Multi-Version Concurrency Control (MVCC) for readers via WAL mode. However, it still fundamentally has a database-level write lock. Only one process can hold the write lock and append to the WAL file at a time. High concurrent write contention leads to SQLITE_BUSY errors (though busy_timeout helps mitigate this by retrying).
  • PostgreSQL: Implements sophisticated row-level locking. Multiple writers can modify different rows concurrently without blocking each other. This scales much better for write-heavy workloads.

A simple sysbench test (or similar OLTP benchmark) quickly highlights this: SQLite excels under heavy reads with a single writer but falters rapidly as write concurrency increases, while PostgreSQL scales writes more gracefully (up to CPU/IO limits).

• • Thread Safety:
    • SQLite itself can be compiled in different threading modes.
    • The standard Ruby sqlite3 gem typically uses a build where SQLite is configured for SQLITE_CONFIG_SERIALIZED. This makes SQLite library calls thread-safe internally. Rails then manages concurrency at the connection level using its connection pool (ActiveRecord::ConnectionAdapters::ConnectionPool).
    • Each thread checks out a connection, ensuring serialized access per connection, aligning with SQLite’s single-writer model within that connection’s scope.
  • Async I/O Potential:

Rails 8 leverages recent Linux kernel features like io_uring for asynchronous I/O operations, providing significant performance benefits for SQLite on modern hardware:

• • SQLite + io_uring: On modern Linux kernels, io_uring offers a highly efficient interface for asynchronous I/O. Theoretically, an SQLite VFS (Virtual File System) layer built on io_uring could dramatically reduce I/O overhead, especially for WAL writes and checkpoints. This is cutting-edge and not standard in Rails yet but represents a significant potential performance boost.
  • PostgreSQL + async-pg: The Ruby ecosystem already has mature libraries like async-pg that leverage non-blocking I/O for PostgreSQL communication, allowing frameworks like Async Roda or Falcon to handle many concurrent requests efficiently without tying up threads waiting for database responses.

Performance Benchmarking SQLite vs. PostgreSQL

Benchmarking SQLite vs. PostgreSQL requires going beyond simple web request timings.

• • Benchmark Methodology:
    • Disk I/O: Use fio to simulate WAL appends (sequential writes) and random reads to understand raw device performance under patterns similar to database usage.
    • CPU Cache: Use perf stat to measure CPU cycles, instructions, and importantly, cache misses during database operations. SQLite’s memory mapping (mmap) can lead to better L1/L2 cache utilization for reads compared to PostgreSQL’s shared buffer copying.
    • Lock Contention: Use eBPF (Extended Berkeley Packet Filter) tools (like bpftrace or custom scripts) to trace kernel functions related to futexes or file system locks to precisely measure time spent waiting for SQLite’s write lock under contention.
  • Read-Heavy Workloads:

SQLite shines here due to its architecture:

• • • SQLite: When data is cached by the OS page cache, queries using mmap can potentially perform zero-copy reads. The data is directly accessed from the memory-mapped file region in the process’s address space.
    • PostgreSQL: Uses its own shared buffer manager. Data is read from disk (if not cached) into PostgreSQL’s shared buffers, and then copied again from shared buffers into the backend process’s memory space to be sent back to the client. This involves more overhead.
  • Case study: Consider a read-only API endpoint serving cached user profiles from a ~50GB database.
  • Scenario: 100,000 Requests Per Second (RPS) spread across multiple read replicas or edge nodes.

Using 100 k RPS synthetic traffic we saw the following latency profile

• • Write Bottlenecks: This is SQLite’s Achilles’ heel at scale. The single database write lock becomes a serializing bottleneck.
  • Saturation Point: The maximum write transactions per second is fundamentally limited. An empirical estimation:

Max Writes / sec ≈ 1 / (avg_write_latency + contention_penalty)

• • A practical limit is often in the low hundreds to single-digit thousands of writes/sec on good hardware before contention kills performance.
• Sharding Strategies: If you need more write throughput, you must shard. Since SQLite operates on files, directory-based partitioning is feasible. Rails’ multi-database support helps:

                    
# config/database.yml
production:
  primary:
    adapter: sqlite3
    database: storage/primary.sqlite3
    # ... common pragmas ...
  shard_1:
    adapter: sqlite3
    database: storage/shard_1.sqlite3
    # ... common pragmas ...
  shard_2:
    adapter: sqlite3
    database: storage/shard_2.sqlite3
    # ... common pragmas ...
    
# In an ActiveRecord model or application code
class SomeData < ApplicationRecord
  connects_to shards: {
    shard_1: { writing: :shard_1, reading: :shard_1 },
    shard_2: { writing: :shard_2, reading: :shard_2 }
  }
end

# Assign data to a shard
def assign_shard_for(user_id)
  (user_id % 2 == 0) ? :shard_1 : :shard_2
end

user_id = 123
shard_key = assign_shard_for(user_id)

ActiveRecord::Base.connected_to(role: :writing, shard: shard_key) do
  SomeData.create!(user_id: user_id, data: '...')
end
                    
                

This manually partitions data across different SQLite database files, distributing the write load. It adds significant application complexity.

Best SQLite Production Architecture Patterns

SQLite can be deployed in innovative ways that leverage its strengths while mitigating its traditional weaknesses.

• • Edge SQLite with Litestream:

Litestream is a brilliant tool that provides asynchronous replication for SQLite by continuously backing up WAL file changes to object storage (like S3).

• • Architecture: Deploy your Rails application to dozens or hundreds of edge locations (e.g., Fly.io, Cloudflare Workers with upcoming persistent storage). Each instance runs with its own local SQLite database file. Litestream runs as a sidecar, replicating changes to S3. Other nodes can then restore or replicate from S3 to get updates.
  • Mechanism: While not strictly RAFT, Litestream provides eventual consistency. Writes are durable in object storage quickly, but propagation to other edge nodes depends on their replication cycle.
  • Trade offs (CAP Theorem): This architecture prioritizes Availability (each edge node is independent) and Partition Tolerance (network splits between edge nodes don't halt local reads/writes). It sacrifices strong Consistency. A read on one node might see slightly stale data compared to a node where a write just occurred. Perfect for read-heavy workloads like caching, product catalogs, or configuration data served geographically close to users.

                    
# Conceptual Litestream setup (run alongside Rails server)
litestream replicate /path/to/your/production.sqlite3 s3://your-bucket-name/db
                    
                

Ephemeral Data at Scale:
Think of use cases often handled by Redis: rate limiting, session storage, short-lived caches, job queues with tolerance for slight delays.
SQLite as a Redis Alternative: For simple key/value patterns or counters, SQLite can be incredibly fast, especially on NVMe drives. According to benchmarks, SQLite can handle 2 million+ simple operations/second on a single node when writes are serialized appropriately (e.g., background job processing counters).
Leveraging mmap_size: For larger datasets where you want in-memory performance but crash safety, you can configure a large mmap_size

                    
# config/database.yml addition
production:
  pragmas:
    mmap_size: 21474836480 # 20 GB - Careful! Ensure you have >20GB RAM available!
                    
                

This tells SQLite to memory-map up to 20GB of the database file. If the DB fits within this and the OS can cache it in RAM, reads become near-instant, while WAL mode still provides crash safety for writes. It's like having a persistent, memory-speed cache.

Operational Rigor for SQLite at Scale

According to benchmark tests run on Rails 8, SQLite can handle traffic levels of up to 100,000 hits per day, and in some cases, even more. Running SQLite in production, especially with Litestream, requires different operational practices than managing PostgreSQL.

• • Disaster Recovery:
    • Litestream:
      • RPO (Recovery Point Objective): How much data can you lose? Depends on WAL upload frequency. Typically very low, potentially ~1-15 seconds.
      • RTO (Recovery Time Objective): How fast can you recover? Depends on DB size and download speed from object storage. For multi-GB databases, it can be minutes (~2m cited). Point-in-time recovery (PITR) to any specific second is possible by replaying the WAL segments from backup.
    • PostgreSQL PITR: Mature tooling allows recovery to nearly any transaction boundary, but setup (WAL archiving, base backups) is more complex. RPO is typically seconds, RTO depends heavily on database size and replay speed, often minutes to hours for large DBs.
    • Custom Backup Integration: You might want synchronous backups triggered before deployments or shutdowns. Integrating sqlite3_backup_init API calls via FFI (Foreign Function Interface) into a Kubernetes preStop hook or a deployment script can ensure a consistent snapshot is taken before an instance terminates.
  • Monitoring Stack:

Standard database monitoring tools often lack deep SQLite insight.

• • eBPF for Lock Contention: Essential for understanding write bottlenecks. Trace futex wait times associated with the database file handles to quantify lock contention impact. Visualize this in Grafana.
  • Custom Prometheus Exporter: Expose SQLite-specific metrics:
  • WAL checkpoint frequency and duration.
  • Current WAL size (PRAGMA journal_size_limit vs actual size).
  • Cache hit/miss ratios (PRAGMA cache_info - might require periodic polling).
  • Litestream replication lag (by checking object storage timestamps vs local WAL).

PostgreSQL to SQLite Migration Step by Step Guide:

Migrating from PostgreSQL to SQLite is uncommon but feasible for specific workloads identified as suitable (e.g., read-heavy services).

• • Schema Smells to Avoid:

Features heavily used in PostgreSQL might not have direct SQLite equivalents or perform poorly:

• • PostgreSQL-specific types/functions: JSONB operations, extensions (PostGIS, pg_trgm), complex generate_series, window functions, LISTEN/NOTIFY.
  • Common Table Expressions (CTEs): SQLite supports them, but complex recursive CTEs might perform differently.
  • Advisory Locks: No direct equivalent for application-level locking.
  • Materialized Views: Need to be implemented manually using triggers or periodic batch jobs.
  • Stored Procedures/Triggers: SQLite supports triggers, but complex procedural logic is often better handled in application code.
• Active Record Gotchas:
  • find_each / find_in_batches: PostgreSQL cursors are efficient. SQLite's implementation might load more data into memory or have different performance characteristics. Tune batch_size carefully; smaller batches might be needed for SQLite to avoid high memory usage or performance issues on very large tables.
  • Case Sensitivity: Default collation might differ. Ensure string comparisons behave as expected.
  • Locking/Retries: Replace PostgreSQL SELECT ... FOR UPDATE patterns. Implement application-level retry logic around potential SQLITE_BUSY errors during writes.
• Zero-Downtime Cutover (Conceptual):
  • Schema Translation: Create the equivalent SQLite schema. Script data migration (e.g., pg_dump to CSV, then SQLite import).
  • Dual Write: Modify application code to write to both PostgreSQL and SQLite databases. Use ActiveRecord::Base.connected_to to manage connections. Add consistency checks (e.g., background job comparing records) to ensure data integrity during this phase.

                    
# Conceptual Dual Write Service
class DualWriterService
  def create_record(params)
    pg_record = nil
    sqlite_record = nil

    ActiveRecord::Base.transaction do # PG Transaction
      pg_record = PostgresModel.create!(params)
      
      begin
        ActiveRecord::Base.connected_to(role: :writing, shard: :sqlite_primary) do
          ActiveRecord::Base.transaction do # SQLite Transaction
            sqlite_record = SqliteModel.create!(pg_record.attributes) # Adapt attributes if needed
          end
        end
      rescue ActiveRecord::StatementInvalid => e
        raise unless e.cause.is_a?(SQLite3::BusyException)
        # Handle SQLite busy, maybe retry or log critically
        Rails.logger.error("Dual write failed for SQLite: #{e.message}")
        # Decide on rollback strategy - rollback PG too? Or just log inconsistency?
        raise ActiveRecord::Rollback # Example: Rollback PG write if SQLite fails critically
      end
    end
    
    # Optional: Enqueue consistency check job
    ConsistencyCheckJob.perform_later(pg_record.id) if pg_record && sqlite_record
    
    pg_record # Return the primary record
  end
end
                    
                

• • Dark Traffic Shadowing:

Route a percentage of read traffic to the SQLite database using connected_to. Compare results (latency, correctness) against PostgreSQL responses without affecting users.integrity during this phase

                    
# Conceptual Shadow Reading in a Controller
def show
  @pg_data = PostgresModel.find(params[:id])
  
  # Shadow read to SQLite for comparison (e.g., 10% of requests)
  if rand < 0.1 
    begin
      ActiveRecord::Base.connected_to(role: :reading, shard: :sqlite_primary) do
        @sqlite_data = SqliteModel.find(params[:id])
        log_comparison(@pg_data, @sqlite_data) # Log latency, check for data discrepancies
      end
    rescue => e
      Rails.logger.warn("SQLite shadow read failed: #{e.message}")
    end
  end
  
  render json: @pg_data # Serve data from PostgreSQL
end
                    
                

• Cutover: Once confident, switch read traffic entirely to SQLite.
• Decommission Dual Writes: Remove the PostgreSQL write path.

Decision Framework

Choosing requires evaluating your specific workload characteristics.

                    
def choose_database(read_ratio:, latency_sensitivity:, edge_deployment:, 
                   write_ratio:, requires_distributed_transactions?,
                   estimated_writes_per_sec:, estimated_data_size_gb:, max_threads:)
  
 
  is_sqlite_candidate = read_ratio > 0.8 && latency_sensitivity && (edge_deployment || estimated_data_size_gb < 500)
  
  # Heuristics based on common PostgreSQL strengths
  is_pg_candidate = write_ratio > 0.4 || requires_distributed_transactions? || 
                   estimated_writes_per_sec > 500 || estimated_data_size_gb > 500 ||
                   max_threads > 50 # Higher contention likely

  if is_sqlite_candidate && !is_pg_candidate && estimated_writes_per_sec < 500
    # Strong candidate for SQLite, especially if edge or latency is paramount
    puts "Consider SQLite: Strong fit based on heuristics (High Read Ratio, Latency Sensitive, Manageable Writes/Size)."
    :sqlite
  elsif is_pg_candidate
 
    puts "Consider PostgreSQL: Strong fit based on heuristics (High Writes, Large Data, Dist. TX, High Concurrency)."
    :postgresql
  else
    # Ambiguous case: Benchmarking is crucial
    puts "Ambiguous Case: Requires detailed benchmarks. SQLite might work if reads dominate and writes are manageable, otherwise lean PostgreSQL."
    run_benchmarks! # Placeholder for actual performance testing
    :benchmark_required 
  end
end

# --- Helper/Placeholder Methods ---
def latency_sensitivity?; true; end # Is p99 latency critical?
def edge_deployment?; false; end    # Is this deployed to multiple edge nodes?
def requires_distributed_transactions?; false; end # Does it need complex multi-shard transactions?
def run_benchmarks!; :benchmarked_result; end # Placeholder

# Example Usage:
read_ratio = 0.9
write_ratio = 0.1
estimated_writes_per_sec = 100
estimated_data_size_gb = 50
max_threads = 20

recommendation = choose_database(
  read_ratio: read_ratio, latency_sensitivity: latency_sensitivity?, edge_deployment: edge_deployment?,
  write_ratio: write_ratio, requires_distributed_transactions: requires_distributed_transactions?,
  estimated_writes_per_sec: estimated_writes_per_sec, estimated_data_size_gb: estimated_data_size_gb, max_threads: max_threads
) 
# Output: Consider SQLite: Strong fit based on heuristics...
                    
                

Quantitative Thresholds Table (Rule of Thumb):

Conclusion: The New Calculus

Here’s what you need to know if you’re deciding between SQLite and PostgreSQL. SQLite thrives in edge deployments, read-heavy APIs, and apps with moderate write loads where simplicity matters. PostgreSQL remains unmatched for high concurrency, large datasets, and complex queries.
Start with SQLite, test against your traffic patterns, and scale to PostgreSQL only when needed. Learn this and your ops team (and cloud bill) will thank you.
At Bluetick Consultants, we build resilient systems designed for performance and growth.
Explore our Digital Product Engineering Services.

Planning to build or scale a high-traffic app?

Partner with Bluetick Consultants Inc and we’ll help you grow faster, keep users happy, and never worry about crashes.

Consult with our database experts for a free performance review.