PostgreSQL vs SQLite

Databases come in two fundamental architectures. PostgreSQL runs as a standalone server that applications connect to over a network. SQLite compiles into your application as a library that reads and writes a local file. The architectural difference determines everything else about how these databases work.

SQLite emerged in 2000 to solve a specific problem: applications needed structured storage without the complexity of running a database server. By embedding the entire database engine into a 750KB library, SQLite eliminated setup, configuration, and administration. The database became just another file your application managed.

PostgreSQL evolved from academic research into a production-grade server designed for multiple simultaneous users. The server architecture enabled features impossible in an embedded library: coordinating hundreds of concurrent connections, enforcing access controls across users, and optimizing complex queries with sophisticated planning algorithms.

Modern versions of both databases support similar SQL features. Window functions, common table expressions, and JSON operations work in both. The choice isn't about SQL capabilities anymore. It's about whether your application needs a database server or a library.

What is PostgreSQL?

PostgreSQL runs as a separate server process that manages databases and coordinates access from multiple clients. Applications connect using network protocols, even when running on the same machine. The server maintains its own memory space, handles authentication, and enforces permissions across users.

This separation between database and application enables shared data access. A web application, batch processing job, and analytics tool can all query the same database simultaneously. PostgreSQL's server coordinates their requests, prevents conflicts, and maintains consistency.

The database includes an extension system that adds specialized capabilities. You can enable full-text search, work with geographic coordinates, or optimize time-series data by loading extensions. The server architecture makes this modularity practical.

What is SQLite?

SQLite compiles directly into your application. There's no separate process, no configuration files, and no server listening on a port. When your code calls SQLite functions, the database operations happen in the same process using the same memory.

The entire database exists as a single file on your filesystem. Your application opens this file, reads and writes data, and closes it when done. File permissions control access—anyone who can read the file can query the database.

This embedded design makes SQLite suitable for scenarios where PostgreSQL would be overkill. Mobile applications store user data locally. Desktop software uses SQLite instead of custom file formats. Embedded devices log sensor readings without running a server.

PostgreSQL vs SQLite: quick comparison

Installation differences

SQLite does not require installation like a traditional database server. It is embedded directly into your application by linking the library or including the amalgamation source file during compilation. The database itself is just a single file on disk.

If you want a command-line tool for inspecting or querying SQLite databases on Ubuntu, you can optionally install it with:

sudo apt install sqlite3

This installs only the CLI client. The database engine still runs inside your application without any separate service or configuration.

That's it. No daemon to start, no configuration to write, no users to create. The library is part of your application binary.

PostgreSQL needs proper installation and initialization:

# Install packages
sudo apt install postgresql postgresql-contrib

# Server starts as system service
sudo systemctl status postgresql

# Connect as superuser to create application database
sudo -u postgres createdb appdata
sudo -u postgres createuser appuser

# Grant permissions
sudo -u postgres psql
GRANT ALL PRIVILEGES ON DATABASE appdata TO appuser;

Your application then connects to this running server using connection strings. The server persists independently of your application.

How they handle data types

SQLite stores data in five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Column types are more like hints:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

-- SQLite allows this (stores text in numeric column)
INSERT INTO products (id, name, price) VALUES (1, 'Widget', 'expensive');

-- Stores whatever you give it
SELECT typeof(price) FROM products WHERE id = 1;
-- Returns: "text"

This flexibility speeds development—you don't fight the database over types. But it means your application must validate data since the database won't reject type mismatches.

PostgreSQL enforces declared types strictly:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2)
);

-- PostgreSQL rejects invalid types
INSERT INTO products (name, price) VALUES ('Widget', 'expensive');
-- ERROR: invalid input syntax for type numeric: "expensive"

-- You must provide correct types
INSERT INTO products (name, price) VALUES ('Widget', 29.99);

The database validates every value against its column type. This catches bugs early but requires careful schema design upfront.

Concurrency models

SQLite's concurrency limitation stems from its file-based nature. A write operation locks the entire database file:

import sqlite3
import threading

def writer():
    conn = sqlite3.connect('test.db')
    conn.execute('BEGIN IMMEDIATE')  # Locks database
    conn.execute('INSERT INTO logs VALUES (?, ?)', (1, 'entry'))
    time.sleep(5)  # Hold lock for 5 seconds
    conn.commit()

def reader():
    conn = sqlite3.connect('test.db')
    # Can read while write is uncommitted (in WAL mode)
    # But cannot write
    conn.execute('SELECT * FROM logs')

WAL (Write-Ahead Logging) mode improves this by allowing reads during writes, but only one writer can operate at any time. High-concurrency applications quickly hit this bottleneck.

PostgreSQL coordinates multiple simultaneous writers through MVCC:

-- Session 1
BEGIN;
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 'ABC';
-- Holds row-level lock on product ABC

-- Session 2 (different product)
BEGIN;
UPDATE inventory SET quantity = quantity - 3 WHERE product_id = 'XYZ';
-- Proceeds immediately - different row

-- Session 3 (same product)
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'ABC';
-- Waits for Session 1 to complete

Row-level locking means operations only block when they conflict. Hundreds of connections can write to different rows simultaneously.

Query performance patterns

SQLite performs exceptionally well for simple queries with minimal overhead:

-- Lightning fast: in-process, no network
SELECT * FROM users WHERE id = 42;

-- Aggregations work but without parallel execution
SELECT category, AVG(price) 
FROM products 
GROUP BY category;

Everything runs in a single thread within your application's process. No network latency exists, making simple lookups incredibly fast. Complex queries can't leverage multiple CPU cores for parallel execution.

PostgreSQL invests significant effort in query optimization:

-- Query planner analyzes statistics and chooses strategy
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.name
HAVING COUNT(o.id) > 5;

-- May use:
-- - Index scans on date range
-- - Hash joins for user/order combination
-- - Parallel workers for aggregation

Network overhead adds latency, but the sophisticated query planner makes better decisions for complex operations. Large queries can run across multiple CPU cores simultaneously.

Storage and file handling

SQLite stores everything in a single file that you can copy, rename, or email:

# Backup: just copy the file
cp application.db application.db.backup

# Move between systems
scp application.db user@remote:/path/

# Version control a test database
git add test.db

# The entire database is portable

This portability makes SQLite ideal for application file formats. Database versioning, distribution, and backup become file operations.

PostgreSQL maintains a complex directory structure:

# Data directory contains many files
/var/lib/postgresql/14/main/
├── base/           # Database files
├── global/         # Cluster-wide tables
├── pg_wal/         # Transaction logs
├── pg_stat/        # Statistics
└── postgresql.conf # Configuration

# Backup requires special tools
pg_dump database_name > backup.sql

# Or base backup for point-in-time recovery
pg_basebackup -D /backup/location

The database spans multiple files optimized for different purposes. You can't simply copy the data directory while the server runs. Backup strategies require understanding PostgreSQL's architecture.

Transaction guarantees

Both databases provide ACID transactions but implement durability differently.

SQLite commits transactions by syncing the database file:

BEGIN IMMEDIATE;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- Syncs to disk, may be slow on some filesystems

The COMMIT forces a filesystem sync to ensure data reaches disk. Performance depends on your filesystem and storage device. WAL mode improves this by batching syncs.

PostgreSQL uses write-ahead logging for durability:

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- Writes to WAL, fast sequential I/O

Changes write to the WAL first (fast sequential writes), then later sync to data files. This design provides durability with better performance on most hardware.

Operational complexity

SQLite requires essentially zero administration:

# Application code handles everything
import sqlite3

# Open database (creates if needed)
db = sqlite3.connect('app.db')

# Enable WAL mode for better concurrency
db.execute('PRAGMA journal_mode=WAL')

# Done. No tuning, monitoring, or maintenance needed

There's no server to monitor, no connections to pool, no vacuuming to schedule. The database runs as your application runs and stops when your application stops.

PostgreSQL demands ongoing administration:

# Monitor connection usage
SELECT count(*) FROM pg_stat_activity;

# Analyze query performance
SELECT query, mean_exec_time 
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;

# Vacuum to reclaim space
VACUUM ANALYZE;

# Adjust configuration for workload
# postgresql.conf settings affect performance significantly

Production PostgreSQL deployments need monitoring, tuning, and maintenance. Connection pooling (pgBouncer), query analysis (pg_stat_statements), and resource management require understanding PostgreSQL internals.

Final thoughts

This article explained that PostgreSQL and SQLite are built for different jobs. SQLite is a library that your app uses directly, so there is no server to install or manage. It is easy to set up, uses a single file, and works best when only one application needs to access the data.

PostgreSQL is a full database server that many apps and users can connect to at the same time. It handles complex queries, permissions, and concurrency. It requires more setup and administration, but it gives you much more power and flexibility when working with shared or high-traffic data.

If you are building a mobile app, desktop tool, or small embedded system that stores local data, SQLite is usually the right choice. If you are building a web application, API, or service that many people or systems will use at once, PostgreSQL is a better fit.

In short, choose SQLite when you want a lightweight database inside your app, and choose PostgreSQL when you need a dedicated server that can manage data for many users.

Got an article suggestion? Let us know

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.