How to migrate from SQLite to PostgreSQL

Your SQLite database locks on every write. When your second user tries to update their profile while the first user is checking out, one of them waits. When your background job tries to send emails while your API handles requests, everything queues. SQLite's single-writer architecture worked great for your prototype, but now it's the bottleneck preventing you from scaling.

This guide walks you through migrating from a file-based SQLite database to a robust, concurrent PostgreSQL architecture on Render.

When SQLite becomes your bottleneck

You need PostgreSQL when:

1. Write latency spikes unpredictably: Users report "slow saves" or "request timeouts" during normal usage. SQLite's database-level write lock means every INSERT, UPDATE, or DELETE blocks all other writes.
2. You need multiple app instances: Deploying a second web server? SQLite can't handle concurrent writes across processes. PostgreSQL's MVCC (Multi-Version Concurrency Control) allows 100+ simultaneous connections writing without blocking. Instead of locking the entire database, PostgreSQL creates a new version of each row during updates. Readers see the old version while writers create the new one—no waiting, no locks.
3. Database file exceeds 10GB: SQLite performs well up to ~10GB, but beyond that, you'll notice degraded performance on complex queries. PostgreSQL handles terabytes efficiently.
4. You need advanced features: Full-text search, JSON operations, custom functions, or row-level security require PostgreSQL's extensibility.

Real-world indicator: If you're seeing sqlite3.OperationalError: database is locked in your logs more than once per week, migrate now.

Prerequisites and version requirements

Before you migrate, verify your environment meets these requirements:

• PostgreSQL 12 or later: Required for improved B-tree indexing (20-30% faster on large tables) and native table partitioning. Render provides managed instances from PostgreSQL 12 through the latest stable version. If you're starting fresh, choose the latest version for performance improvements.
• pgloader 3.6.0 or later: Earlier versions had type conversion bugs. This version handles JSON columns correctly.
• SQLite 3.8.0 or later: Ensures Common Table Expressions (CTE) support. If you're on an older version, upgrade SQLite first.

Install pgloader: apt-get install pgloader (Ubuntu/Debian) or brew install pgloader (macOS).

Understand schema translation requirements

SQLite and PostgreSQL implement different type systems. SQLite uses type affinity, allowing you to store a string in an INTEGER column—it tries to convert, then stores the string if conversion fails. This flexibility causes subtle bugs:

PostgreSQL's strictness prevents these silent data corruption bugs.

SQLite's INTEGER PRIMARY KEY auto-increments as ROWID alias. PostgreSQL requires explicit SERIAL, BIGSERIAL, or IDENTITY columns. Constraint handling differs: SQLite historically had limited foreign key enforcement, while PostgreSQL enforces referential integrity by default.

Type system comparison

This simplified example demonstrates the schema differences you'll encounter:

Adapt this pattern for your specific schema requirements.

Common schema translation challenges

Boolean columns:

SQLite stores booleans as 0/1 integers. PostgreSQL has a native BOOLEAN type.

pgloader handles this automatically, but review your application logic—SQLite queries like WHERE enabled = 1 need to become WHERE enabled = true.

Date/time handling:

SQLite stores dates as TEXT or INTEGER. PostgreSQL has dedicated temporal types.

Your application code parsing string dates will break. Use your ORM's date handling instead.

JSON columns:

SQLite added JSON support in 3.38.0, but most apps store JSON as TEXT. PostgreSQL has native JSONB.

pgloader converts TEXT to JSONB automatically if the content is valid JSON.

Provision PostgreSQL on Render

Create PostgreSQL through the Render Dashboard by selecting "New PostgreSQL." Choose regions close to your application instances and appropriate instance types for your workload.

For applications requiring connection pooling, you can set up PgBouncer on Render. Most applications start with application-level pooling (shown in the framework examples below). Add PgBouncer only when you exceed 100-200 database connections or need connection pooling across multiple services.

See Render's PostgreSQL documentation for detailed provisioning options.

Plan your migration strategy

Maintenance window migration (recommended for most applications):

1. Schedule downtime: Announce 1-4 hour maintenance window based on database size.
2. Stop application: Prevent new writes during migration.
3. Run pgloader: Transfer all data while app is down.
4. Validate: Test critical paths.
5. Switch connection: Update DATABASE_URL to PostgreSQL.
6. Start application: Monitor for errors.

For databases under 10GB, maintenance window migration typically completes in under 1 hour (varies based on data complexity and available resources). Most applications can tolerate this brief downtime during off-peak hours.

Advanced: Dual-write migration (for downtime-sensitive applications):

If you cannot tolerate any downtime and your application can handle increased write latency:

⚠️ Important considerations:

• Dual-writing increases SQLite's lock contention (the problem you're trying to solve)
• Expect 20-40% slower writes during the migration period
• Requires careful error handling to prevent data divergence
• More complex than maintenance window approach

1. Dual-write phase: Configure your application to write to both SQLite (primary) and PostgreSQL (secondary) simultaneously. Read from SQLite only.
2. Validation phase: After 24-48 hours of dual writes, compare data between databases. Fix any discrepancies.
3. Switch reads: Point read operations to PostgreSQL while continuing dual writes. Monitor for query errors.
4. Make PostgreSQL primary: Stop writes to SQLite. PostgreSQL is now your source of truth.
5. Cleanup: After 7 days of stable PostgreSQL operation, remove SQLite write code.

Implementation tip: Wrap your database write operations in a function that writes to both databases:

PostgreSQL writes can fail without impacting users. Build monitoring to track sync failures and fix discrepancies during the validation phase before switching reads to PostgreSQL.

When to use each approach:

• Maintenance window: Database <50GB, can schedule 1-4 hours downtime, want simplest path (90% of migrations)
• Dual-write: Database >50GB, zero-downtime requirement, can handle temporary write performance degradation

Execute migration with pgloader

pgloader automates data transfer with type conversion and batch processing.

Create migration command files for reproducibility:

Execute: pgloader migration.load

Handle errors during migration

Monitor pgloader output for common errors:

Type conversion failures: SQLite data incompatible with PostgreSQL strict types. Clean your source data or adjust your target schema.

Constraint violations: Foreign key references to non-existent rows or duplicate data. Validate referential integrity before you migrate.

Character encoding issues: Invalid UTF-8 sequences or null bytes. Use pgloader's CAST directives with encoding transformation.

Review rejection files for failed transfers:

Validate migration success

Step 1: Verify table structure

Compare this list against your SQLite schema: .tables in SQLite CLI.

Step 2: Verify row counts

Run this query in both databases:

Counts should match exactly. Mismatches indicate data loss.

Step 3: Verify data integrity

Check referential integrity (orphaned foreign keys):

If this query returns rows, you have orders referencing deleted users. SQLite allowed this because foreign key enforcement was historically optional. PostgreSQL will reject these on INSERT, causing application errors. Clean the data before migrating or add ON DELETE CASCADE to your foreign key constraints.

Step 4: Test critical queries

Run your application's most common queries against PostgreSQL:

• User authentication
• Order creation
• Search functionality
• Report generation

Compare results against SQLite. Differences indicate type conversion issues.

Update application configuration

Django:

Rails:

Node.js (Sequelize):

Query syntax adjustments:

Replace SQLite-specific syntax like AUTOINCREMENT (becomes SERIAL or IDENTITY) and strftime() (becomes to_char()).

Leverage PostgreSQL advanced features

Optimize with PostgreSQL-specific capabilities:

Partial indexes (save disk space and improve performance):

Full-text search (no external search engine needed):

JSON operations (query nested data efficiently):

These features often eliminate the need for external services like Elasticsearch or separate caching layers.

Expected performance improvements

Based on typical migrations, expect these gains:

Write performance:

• Single-user writes: ~10-20% slower (network overhead vs. local file).
• Concurrent writes: 100-1000x faster (no database lock contention).
• Bulk inserts: ~50% faster (better transaction handling).

Read performance:

• Simple queries: Similar performance to SQLite.
• Complex JOINs: 2-5x faster (query planner optimization).
• Full-text search: 10-50x faster (native indexing).

Your mileage varies based on: Database size, query complexity, network latency, and instance size. Run EXPLAIN ANALYZE on your slowest SQLite queries before and after migration to quantify improvements.

Important caveat: PostgreSQL's first connection and first query in a session are slower than SQLite due to network overhead and connection setup (~50-100ms). For applications that open/close database connections frequently, implement connection pooling (shown in the framework examples above) to amortize this cost.

Start your migration today

The longer you wait to migrate from SQLite to PostgreSQL, the harder it becomes. Every day adds more data to transfer and more application code that assumes SQLite behavior.

Your migration checklist:

• Provision PostgreSQL on Render (takes 2 minutes)
• Install pgloader locally
• Run migration on a copy of your database (test before production)
• Validate row counts and critical queries
• Update application configuration for one service
• Monitor for 48 hours before full rollout

For most applications under 10GB, this entire process takes less than a day. Render's managed PostgreSQL handles backups, monitoring, and scaling so you can focus on building features instead of maintaining infrastructure.

Provision PostgreSQL on Render

FAQ