SQLite vs PostgreSQL

Quick verdict

SQLite is a C library that implements a self-contained SQL database engine inside your application's process. There is no server: the application reads and writes a single database file directly. It uses a dynamic type system in which the type belongs to the value rather than the column, unless you declare a table STRICT. SQLite is in the public domain. The latest release listed on sqlite.org is 3.53.4.

PostgreSQL is an open source object-relational database server developed by the PostgreSQL Global Development Group and released under the PostgreSQL Licence. Clients connect to a server process, authenticate as a role, and run SQL against strictly typed tables. It uses multiversion concurrency control (MVCC), has a large set of built-in data types, and can be extended with CREATE EXTENSION. The current stable release is 18.6, with PostgreSQL 19 in beta.

For the general embedded versus client-server trade-off with MySQL as the server, see SQLite vs MySQL. This page is about the PostgreSQL decision: what PostgreSQL adds over SQLite, when SQLite is still enough, and what changes when you move a prototype from one to the other. If your interest is analytics on local files, see DuckDB vs SQLite.

Side by side

Key differences

When SQLite is enough, and when to move to PostgreSQL

sqlite.org lists embedded devices, application file formats, low to medium traffic websites, data analysis, caches and teaching as good uses, and recommends a client/server database when data is on a separate machine from the application, when there are many concurrent writers, or when data approaches a terabyte. Inside those limits SQLite needs no installation, configuration or user management.

In our view the signals that a project has outgrown SQLite and should move to PostgreSQL are: more than one application server needs the same data; writes queue behind each other; you need per-user permissions or row-level rules enforced by the database; or you want server-side features such as extensions, rich types or foreign data wrappers. If none of these apply, staying on SQLite is a legitimate choice, not a shortcut.

Types: flexible values versus enforced columns

SQLite has five storage classes (NULL, INTEGER, REAL, TEXT and BLOB). A declared column type only sets an affinity, so in an ordinary table a column declared INTEGER will still store 'abc' as text. There is no separate boolean storage class (TRUE and FALSE are aliases for 1 and 0 since 3.23.0) and no date/time storage class: dates are stored as ISO-8601 text, Julian day numbers or Unix times. STRICT tables reject values of the wrong type.

PostgreSQL enforces column types and rejects input that does not convert. Its documentation lists numeric, monetary, character, binary, date/time, boolean, enumerated, geometric, network address, bit string, text search, UUID, XML, JSON, array, composite, range and domain types, and users can add types with CREATE TYPE. This is a main reason teams move to PostgreSQL: the database, not the application, guarantees what is stored.

-- SQLite: a typical prototype table
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  is_active  INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- PostgreSQL: the same table with native types
CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  is_active  boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

Both engines have JSON support, but they are not interchangeable. SQLite's JSON functions are built in from 3.38.0 and it added a binary JSONB format in 3.45.0; sqlite.org states that this format is not binary compatible with PostgreSQL's jsonb despite the name. PostgreSQL's jsonb can be indexed with GIN.

Concurrency: one writer versus MVCC

sqlite.org states that SQLite allows any number of simultaneous readers but only one writer at a time per database file. Write-ahead logging (PRAGMA journal_mode=WAL) lets readers continue while the writer works, but there is still one writer, and WAL does not work over a network filesystem.

PostgreSQL uses MVCC: its documentation states that reading never blocks writing and writing never blocks reading, and it offers Serializable Snapshot Isolation. Writers lock rows, not the database, so many sessions can update different rows at once. Row locking also enables patterns SQLite cannot express, such as a job queue where each worker claims a different row:

-- PostgreSQL: claim one pending job without waiting on other workers
SELECT id FROM jobs
WHERE  status = 'pending'
ORDER  BY id
LIMIT  1
FOR UPDATE SKIP LOCKED;

Roles, security and extensions

SQLite does not implement GRANT or REVOKE; sqlite.org explains that the only permissions that apply are the operating system's file permissions. Anyone who can read the file can read all of the data.

PostgreSQL manages access through roles, which can be users or groups, can own objects, can be granted privileges on them, and can be members of other roles. Row-level security adds per-row rules: once enabled on a table, every normal read or write must be allowed by a policy, and with no policy the default is deny.

-- PostgreSQL: each manager sees only their own rows
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers
  USING (manager = current_user);

Both engines are extensible, in different ways. SQLite can load extensions (new SQL functions, collations, virtual tables) from shared libraries at run time, but loading is turned off by default for security and must be enabled by the application. PostgreSQL's CREATE EXTENSION installs packaged extensions into a database; the distribution supplies about 50 modules, including pg_stat_statements, pgcrypto, postgres_fdw, pg_trgm and citext. Many extensions need superuser rights, though extensions marked trusted can be installed by users with CREATE privilege on the database. Managed services usually offer only an approved list, so check your provider.

Prototyping on SQLite, moving to PostgreSQL: the dialect differences you will meet

Some syntax carries over unchanged. SQLite's upsert uses the same ON CONFLICT ... DO UPDATE form with excluded, and its RETURNING clause (3.35.0) is modelled on PostgreSQL's. This statement runs on both, provided you write true rather than 1: PostgreSQL will not put an integer into a boolean column, while SQLite treats TRUE as 1.

-- SQLite 3.35+ and PostgreSQL
INSERT INTO users (email, is_active) VALUES ('a@example.com', true)
  ON CONFLICT (email) DO UPDATE SET is_active = excluded.is_active
  RETURNING id;

Auto-numbering. In SQLite an INTEGER PRIMARY KEY column is filled automatically. In PostgreSQL use an identity column (GENERATED ALWAYS AS IDENTITY or BY DEFAULT). With ALWAYS, PostgreSQL rejects explicit ids unless you add OVERRIDING SYSTEM VALUE, which matters when you copy existing rows with their ids; afterwards, move the identity sequence past the highest copied id.

Dates. SQLite date arithmetic uses functions and modifiers on text or numbers; PostgreSQL uses date and timestamp types with intervals.

-- SQLite: a week from today
SELECT date('now', '+7 days');

-- PostgreSQL: a week from today
SELECT current_date + 7;
SELECT now() + interval '7 days';

String aggregation. SQLite's group_concat() has a string_agg() alias that sqlite.org describes as compatible with PostgreSQL, so string_agg(name, ', ') can be written the same way on both. PostgreSQL has no group_concat.

Foreign keys and loose data. SQLite enforces foreign keys only when PRAGMA foreign_keys = ON is set for the connection, and ordinary tables accept values of any type. A prototype can therefore contain orphaned rows, text in numeric columns or 'yes' in a flag column that PostgreSQL will refuse on import. Clean these first, or turn on foreign keys and STRICT tables early in the prototype so it behaves more like production.

Tooling for the move. pgloader, an open source loader, documents SQLite as a source and discovers its schema, and also notes that SQLite's dynamic typing needs care when mapping to relational types. Exporting to CSV and loading with PostgreSQL's COPY is the manual alternative.

Pricing and licensing

SQLite is in the public domain and free for any use, commercial or not. Hwaci, which employs the SQLite developers, sells a Warranty of Title for organisations that need a formal document; sqlite.org does not list its price.

PostgreSQL is released under the PostgreSQL Licence, which the project describes as a liberal open source licence similar to BSD or MIT. There is no paid edition from the project. Running it means either your own server or a managed service (for example Amazon RDS for PostgreSQL), which each provider prices by instance size, storage and region, so there is no single list price to quote.

We describe licence terms only; take your own legal advice if licence obligations matter to you.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• Local analytical queries over large files or columnar data: an in-process analytical engine fits better; see DuckDB vs SQLite.
• Your stack or host is built around MySQL: see SQLite vs MySQL and MySQL vs PostgreSQL.
• Your data is document-shaped and you expect to shard: see MongoDB vs PostgreSQL and SQL vs NoSQL.

Final recommendation

Frequently asked questions

Sources

• SQLite: Appropriate uses
• SQLite: Datatypes
• SQLite: Write-ahead logging
• SQLite: SQL features not implemented
• SQLite: ALTER TABLE
• SQLite: Foreign key support
• SQLite: RETURNING
• SQLite: Aggregate functions
• SQLite: JSON functions and JSONB
• SQLite: Run-time loadable extensions
• PostgreSQL documentation: Data types
• PostgreSQL documentation: Concurrency control (MVCC)
• PostgreSQL documentation: Database roles
• PostgreSQL documentation: Row security policies
• PostgreSQL documentation: CREATE EXTENSION
• PostgreSQL documentation: Additional supplied modules
• PostgreSQL documentation: CREATE TABLE (identity columns)
• PostgreSQL documentation: INSERT (ON CONFLICT, RETURNING)
• PostgreSQL documentation: SELECT (locking clause, SKIP LOCKED)
• PostgreSQL Licence
• pgloader documentation

Checked October 2026.

How we research comparisons: our editorial method.