A table with a composite primary key often looks straightforward in SQL: two or more columns together identify one row. In SQLite, however, the storage layout depends on whether the table is an ordinary rowid table or a WITHOUT ROWID table.

That difference matters when the natural key is already the identity you use for nearly every lookup. An ordinary SQLite table normally keeps a hidden integer rowid as its storage key and implements a non-integer or composite PRIMARY KEY with a separate unique index. A WITHOUT ROWID table instead makes the declared primary key the key of the table’s main B-tree.

The result can be less duplicated key storage and one fewer lookup step for some primary-key reads. It is not a universal optimization. Wide rows, a single INTEGER PRIMARY KEY, rowid-dependent APIs, and large secondary indexes can make an ordinary table a better fit.

This article builds the mental model first, then shows how to decide deliberately.

Start with a composite key that is already the real identity

Suppose a service stores sessions for multiple tenants. A session ID is unique only inside its tenant, so the pair (tenant_id, session_id) is the real identity:

CREATE TABLE sessions (
    tenant_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    expires_at INTEGER NOT NULL,
    payload TEXT,
    PRIMARY KEY (tenant_id, session_id)
);

This schema is correct. SQLite enforces uniqueness for the pair.

But unless the table uses WITHOUT ROWID, it is still a rowid table. Conceptually, SQLite has two structures relevant to the primary key:

  1. the table B-tree, keyed by the hidden integer rowid;
  2. a unique index on (tenant_id, session_id) that points back to the table row.

A lookup such as:

SELECT payload
FROM sessions
WHERE tenant_id = 'tenant-a'
  AND session_id = 'session-42';

can first locate the composite key in the unique index, obtain the rowid stored with that index entry, and then use the rowid to find the full table row.

The important mental model is:

in an ordinary SQLite rowid table, a composite PRIMARY KEY is normally a uniqueness index, not the key of the table’s main storage B-tree.

There is one major exception: a column declared exactly as INTEGER PRIMARY KEY in an ordinary rowid table is an alias for the rowid. That case already uses the integer primary key as the table key and does not benefit from WITHOUT ROWID in the same way.

Make the declared primary key the storage key

The WITHOUT ROWID form changes the storage model:

CREATE TABLE sessions (
    tenant_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    expires_at INTEGER NOT NULL,
    payload TEXT,
    PRIMARY KEY (tenant_id, session_id)
) WITHOUT ROWID;

The SQL used by callers barely changes. Inserts, updates, joins, and primary-key predicates still use the declared columns.

What changes is the underlying organization. The table is keyed by (tenant_id, session_id) rather than by a hidden integer rowid. SQLite describes this as a clustered-index organization: rows are stored according to the declared primary key.

For the earlier lookup, the primary-key search can reach the row’s data directly through the table’s primary-key B-tree instead of using a separate primary-key index to recover a rowid and then searching the table.

That is why WITHOUT ROWID can help when a non-integer or composite key is both:

  • the stable identity of the row;
  • a common access path.

It removes an internal identity that the application does not otherwise need.

Compare the two query plans

EXPLAIN QUERY PLAN makes the distinction visible without relying on assumptions about timing:

EXPLAIN QUERY PLAN
SELECT payload
FROM sessions
WHERE tenant_id = 'tenant-a'
  AND session_id = 'session-42';

For an ordinary rowid table with the composite primary key, a typical plan reports a search through an automatically created primary-key index:

SEARCH sessions USING INDEX sqlite_autoindex_sessions_1
    (tenant_id=? AND session_id=?)

For the equivalent WITHOUT ROWID table, the plan reports a search using the primary key itself:

SEARCH sessions USING PRIMARY KEY
    (tenant_id=? AND session_id=?)

Do not turn this difference into a universal performance promise. Query-plan text explains the chosen access path, not the end-to-end cost of your workload. Cache state, page size, row width, secondary indexes, write frequency, and storage hardware can all matter.

The useful conclusion is narrower: the WITHOUT ROWID layout lets SQLite use the composite primary key as the table’s storage key instead of maintaining the usual hidden-rowid table plus a separate primary-key index.

Why storage can shrink

In an ordinary rowid table, the primary-key columns can appear in both the table record and the unique primary-key index. The index also needs a way to identify the corresponding table row, which for a rowid table means the rowid.

With WITHOUT ROWID, the declared primary key belongs to the table’s main key structure. For suitable schemas, this removes duplicate primary-key storage and the extra B-tree dedicated only to primary-key uniqueness.

That can matter for tables such as:

CREATE TABLE translations (
    locale TEXT NOT NULL,
    message_key TEXT NOT NULL,
    translated_text TEXT NOT NULL,
    PRIMARY KEY (locale, message_key)
) WITHOUT ROWID;

If most rows are small and lookups frequently use both key columns, the layout can be a good match.

But there is a counterweight: the primary key is also the logical locator used by secondary indexes on a WITHOUT ROWID table. A wide composite primary key can therefore make each secondary index larger.

For example:

CREATE INDEX sessions_by_expiry
ON sessions (expires_at);

On a rowid table, a secondary index can use the compact integer rowid to identify the table row. On a WITHOUT ROWID table, the primary-key columns participate as the row locator instead.

So a schema with a very wide natural key and many secondary indexes may save space in one place and spend more elsewhere. Measure the complete schema rather than comparing only the main table.

Primary-key order becomes physical organization

A composite key has an order:

PRIMARY KEY (tenant_id, session_id)

That order is not interchangeable with:

PRIMARY KEY (session_id, tenant_id)

Both enforce uniqueness for the pair, but their useful prefixes differ.

With (tenant_id, session_id), SQLite can naturally use the primary key for predicates such as:

WHERE tenant_id = ?

and for a fully specified pair:

WHERE tenant_id = ?
  AND session_id = ?

A predicate on only session_id does not have the same leftmost-prefix advantage. If that lookup matters, it may need a separate index:

CREATE INDEX sessions_by_session_id
ON sessions (session_id);

This consideration exists for composite indexes generally, but it becomes especially important with WITHOUT ROWID because the primary-key order also organizes the table.

Choose key order from actual access patterns, not merely from the visual order of columns in the schema.

Prefix scans can become a natural fit

Suppose the service frequently lists every active session for one tenant:

SELECT session_id, expires_at
FROM sessions
WHERE tenant_id = ?
ORDER BY session_id;

With:

PRIMARY KEY (tenant_id, session_id)

rows for the same tenant are adjacent in primary-key order, and session_id is already ordered within each tenant.

That does not mean every such query will require zero additional work under every schema. Selected columns, additional predicates, collations, and planner choices still matter.

But the key organization matches the query’s logical grouping. This is one of the strongest reasons to consider WITHOUT ROWID: the natural primary key and the dominant read pattern describe the same ordering.

WITHOUT ROWID changes a few SQLite behaviors

The storage optimization comes with semantic differences that should be reviewed before migration.

There is no hidden rowid

This fails on a WITHOUT ROWID table:

SELECT rowid
FROM sessions;

There is no rowid, _rowid_, or oid alias for the row.

Application code should use the declared primary key instead. If existing code relies on rowid for bookmarks, references, debugging shortcuts, or ad hoc updates, changing the table layout is not transparent to that code.

Every primary-key column is non-null

A WITHOUT ROWID table enforces NOT NULL semantics on every primary-key column.

It is still good practice to write the requirement explicitly:

tenant_id TEXT NOT NULL,
session_id TEXT NOT NULL,
PRIMARY KEY (tenant_id, session_id)

The explicit declarations communicate the data contract to readers and schema tools, instead of making them infer nullability from the table kind.

There is also a SQLite compatibility quirk worth knowing: ordinary rowid tables can allow NULL in non-INTEGER PRIMARY KEY columns unless NOT NULL is stated separately. Do not depend on that behavior for a new schema.

INTEGER PRIMARY KEY loses its rowid alias behavior

In an ordinary rowid table:

id INTEGER PRIMARY KEY

makes id an alias for the hidden rowid.

In a WITHOUT ROWID table, there is no hidden rowid, so the same declaration is simply an integer-affinity primary-key column.

For a table whose identity is one INTEGER PRIMARY KEY, the normal rowid-table representation is usually the better design. SQLite’s own documentation recommends ordinary rowid tables for that case.

AUTOINCREMENT is not available

This is invalid:

CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    payload TEXT
) WITHOUT ROWID;

AUTOINCREMENT depends on rowid behavior, so SQLite rejects it on WITHOUT ROWID tables.

If the table requires generated integer identities, that is another sign that the ordinary INTEGER PRIMARY KEY rowid model probably matches the design better.

last_insert_rowid() is not updated by these inserts

An insert into a WITHOUT ROWID table does not update last_insert_rowid() because no rowid was generated.

Code that treats last_insert_rowid() as the universal way to obtain the identity of the most recently inserted row must not use that assumption for these tables. The application’s identity is the declared primary key, so it should already know or explicitly return those values.

Row size changes the trade-off

A WITHOUT ROWID table is not simply “the same thing with less overhead.”

SQLite’s documented guidance favors the optimization for non-integer or composite primary keys when rows are relatively small. Large rows can reduce the fan-out of the B-tree because more record content participates in its internal organization.

That means a narrow mapping table can be a stronger candidate than a table dominated by large text or BLOB values.

Consider:

CREATE TABLE membership (
    team_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    role TEXT NOT NULL,
    PRIMARY KEY (team_id, user_id)
) WITHOUT ROWID;

This is compact, has a natural composite identity, and may be queried heavily by key prefix.

By contrast:

CREATE TABLE documents (
    workspace_id TEXT NOT NULL,
    document_key TEXT NOT NULL,
    body BLOB NOT NULL,
    PRIMARY KEY (workspace_id, document_key)
) WITHOUT ROWID;

may deserve more careful measurement if body values are large. The layout is legal and correct, but correctness is not the same as performance suitability.

Do not infer a storage win from the key shape alone.

Secondary indexes can reverse an apparent win

Assume a table has a composite primary key containing two text values and six secondary indexes.

A WITHOUT ROWID design can avoid the ordinary separate primary-key index, but each secondary index still needs enough information to identify the underlying row. The table’s primary key provides that identity.

If the primary key is wide, multiplying it across many secondary indexes can become significant.

This gives a practical design rule:

evaluate key width together with secondary-index count.

A compact composite key used by one or two secondary indexes may be attractive. A multi-column key containing long strings, copied into many secondary indexes, may not be.

If a surrogate integer key would materially simplify the whole schema and the natural key still needs a UNIQUE constraint, compare both designs with representative data rather than assuming “natural key” or “surrogate key” is always superior.

Migration is a table rebuild, not a toggle

WITHOUT ROWID is part of the CREATE TABLE definition. You do not switch an existing table in place with a pragma.

A migration normally follows SQLite’s table-rebuild pattern:

  1. create a replacement table with the desired definition;
  2. copy compatible data;
  3. recreate required indexes, triggers, and foreign-key relationships;
  4. replace the old table inside a carefully managed migration.

For example, the replacement schema might be:

CREATE TABLE sessions_new (
    tenant_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    expires_at INTEGER NOT NULL,
    payload TEXT,
    PRIMARY KEY (tenant_id, session_id)
) WITHOUT ROWID;

INSERT INTO sessions_new (
    tenant_id,
    session_id,
    expires_at,
    payload
)
SELECT
    tenant_id,
    session_id,
    expires_at,
    payload
FROM sessions;

The full production migration must also preserve every schema object and application invariant that depends on the original table. Do not copy only the columns and forget indexes, triggers, foreign keys, or code that references rowid.

Because the optimization is workload-dependent, benchmark before making the rebuild part of a release.

Measure with representative data

Avoid microbenchmarks that insert ten rows and conclude that one layout is “faster.”

A useful comparison should reflect:

  • realistic primary-key widths;
  • representative row payload sizes;
  • the actual secondary indexes;
  • realistic read and write ratios;
  • the queries that matter operationally;
  • a database large enough that page organization matters.

At minimum, compare database size after populating equivalent schemas and inspect plans for primary-key and secondary-index queries.

For latency measurements, control for cache state and run enough operations to avoid timing noise. If production cares about write amplification, startup time, or file size as much as point-read latency, include those outcomes too.

WITHOUT ROWID is valuable precisely because it changes physical organization. The right benchmark therefore uses the physical shape of the real workload.

Common mistakes come from treating it as a magic suffix

The first mistake is adding WITHOUT ROWID to every table. A table with one INTEGER PRIMARY KEY is already a strong fit for SQLite’s normal rowid representation.

The second is ignoring primary-key width. A wide key becomes part of how rows and secondary indexes identify records.

The third is choosing composite-key order only for uniqueness. Key order also affects which prefix lookups and ordered scans can use the primary key efficiently.

The fourth is assuming application behavior is unchanged because most SQL still works. Rowid access, AUTOINCREMENT, last_insert_rowid(), incremental BLOB I/O, and some rowid-oriented SQLite interfaces do not behave the same.

The fifth is claiming a performance gain without measuring the actual schema. The optimization can reduce work for one access pattern while increasing storage or lookup costs elsewhere.

When WITHOUT ROWID is a strong candidate

Consider it when the table has a non-integer or composite primary key, that key is the real application identity, rows are reasonably small, primary-key or key-prefix lookups are common, and the application does not depend on rowid-specific features.

Prefer an ordinary rowid table when a single INTEGER PRIMARY KEY already represents identity, rows are large, the natural key is very wide and repeated through many secondary indexes, or application code needs rowid-oriented SQLite APIs.

There is no need to decide from ideology. Both table kinds implement ordinary relational operations correctly. The choice is about matching physical organization to a known key and workload.

Let the real key drive the layout

WITHOUT ROWID is most useful when SQLite’s hidden integer identity adds an indirection your data model does not need.

For a compact table whose stable identity is a text or composite primary key, using that key as the table’s storage key can remove a separate primary-key index and make key-based access more direct. At the same time, it changes row organization, secondary-index costs, and several rowid-specific behaviors.

Start with the logical schema first. If the natural key is non-integer or composite and the workload uses it heavily, test the equivalent WITHOUT ROWID table with representative data. Keep it only when the measured storage and query behavior justify the different layout.

Database SQLite SQL Schema Design