# TIL: SQLite's 'WITHOUT ROWID'


**By default, SQLite tables have a special `rowid` column that uniquely
identifies each row.** This `rowid` exists even if you have a user-specified
`PRIMARY KEY` on the table. How this `rowid` column behaves is influenced by
your `PRIMARY KEY` type.

**Integer Primary Keys**: If you have an integer primary key, then the primary
key column becomes an alias for the special `rowid` column. The `rowid` and your
user-defined primary key are literally just the same column.

Taking an example:

```sql
CREATE TABLE IF NOT EXISTS users(
  user_id INTEGER PRIMARY KEY,
  email TEXT,
);
```

In this case, since we’ve defined `user_id` to be an `INTEGER PRIMARY KEY`,
`user_id` becomes an alias for `rowid`. These two labels refer to the same
physical column.

![Figure 1: Integer primary keys are aliases for the `rowid` column](https://benjamincongdon.me/blog/2025/12/05/TIL-SQLites-WITHOUT-ROWID/users.png)

**Non-Integer Primary Keys**: When you use a non-integer primary key, such as
`TEXT`, SQLite actually implements the “primary key” as a `UNIQUE INDEX` between
`rowid` and your user-defined primary key.

SQLite stores data on disk in
[B-trees](/blog/2021/08/17/B-Trees-More-Than-I-Thought-Id-Want-to-Know/). Table
rows and indices are both stored in B-trees. For non-integer primary keys, this
means that on disk, your table has at least two B-trees -- one for the rows, and
another for the index between the `rowid` and primary key.

Taking an example:

```sql
CREATE TABLE IF NOT EXISTS page_views(
  page_url TEXT PRIMARY KEY,
  views INTEGER,
);
```

This table would have two on-disk B-trees. The first for the rows of
`(rowid, page_url, views)` and the second for the index entries of
`(page_url, rowid)`.

![Figure 2: Non-integer primary keys](https://benjamincongdon.me/blog/2025/12/05/TIL-SQLites-WITHOUT-ROWID/page_views_1.png)

This two-tree structure has performance implications. If we were to do a query
like `SELECT views FROM page_views WHERE page_url='/blog/sqlite'`, the SQLite
query engine has to first search the `(page_url, rowid)` B-tree to get the
`rowid` of the row with `page_url=‘/blog/sqlite’`, and then uses that rowid to
locate the full row in the `(rowid, page_url, views)` B-tree to get the `views`
from that row.

## Enter WITHOUT ROWID

Adding the `WITHOUT ROWID` clause to a `CREATE TABLE` statement disables this
`rowid` behavior. For example:

```sql
CREATE TABLE IF NOT EXISTS page_views(
  page_url TEXT PRIMARY KEY,
  views INTEGER,
) WITHOUT ROWID;
```

Instead of creating the phantom `rowid` column, the primary key of a
`WITHOUT ROWID` table uses a “clustered index”. In this context, a clustered
index means that the row data and the primary key index are stored in the same
B-tree. The rows in this index are physically stored in order of the primary
key. Thus, there isn’t the need for a second look-up B-tree.

![Figure 3: WITHOUT ROWID tables use a clustered index](https://benjamincongdon.me/blog/2025/12/05/TIL-SQLites-WITHOUT-ROWID/page_views_2.png)

_Diagram note:_ Visualizing a B-tree as a table is a bit of an
oversimplification. Note that in Figure 3, as opposed to Figure 2, the rows are
stored in order of the primary key to gesture at the on-disk layout of the
B-tree.

**When to use WITHOUT ROWID:**

-   If you frequently lookup by primary key, or range scan by primary key, there
    is a performance benefit to using `WITHOUT ROWID`.
-   If you use an composite primary key, and frequently lookup by this key.

**When NOT to use WITHOUT ROWID:**

-   **If your primary keys are large.** If your primary keys are large, then any
    additional indices on the table will have to duplicate storage of the entire
    primary key, instead of using the `rowid` to refer to the entry. For
    instance, if you’re using a 200 byte string primary key, then every
    secondary index will include those 200 bytes, instead of the 8 byte `rowid`.
-   **If your table rows are large (e.g. they store a large blob).** If your
    table rows are large, then the disk layout of the table will be less
    efficient as a `WITHOUT ROWID` table. This is because SQLite uses a
    different type of B-tree -- a B\*-tree -- layout for `rowid` tables that
    only stores data in leaf nodes, whereas clustered indices use a normal
    B-tree layout, which stores data in intermediate nodes. If the rows
    themselves are large, the storage of data in intermediate nodes can worsen
    the fan-out when searching the tree.

### Learnings / Discussion

I dug into this concept more deeply when trying to read-optimize a SQLite
database that is used as a simple key-value on-disk cache. The notion of
disabling `rowid` for this table seemed appealing, until I realized that the
optimization was likely not worth it given that the table stored quite large
blobs in its rows, which would degrade the overall efficiency of the on-disk
layout. In any case, I got to learn more about SQLite internals, which was well
worth the time.

The SQLite [documentation](https://sqlite.org/docs.html) makes for a fascinating
read and is exemplary technical writing. The SQLite project is a true treasure.

**Further Reading**:

-   [Clustered Indexes and the WITHOUT ROWID Optimization](https://sqlite.org/withoutrowid.html)
-   [SQLite Internals: Pages & B-trees](https://fly.io/blog/sqlite-internals-btree/)