# SQLite Column Limit: Default 2,000 Cap, Schema Tuning & Workarounds

The SQLite column limit restricts a single table or view to a default maximum of 2,000 columns (defined by SQLITE_MAX_COLUMN), with a compile-time hard upper bound of 32,767 columns. In production environments, wide tables degrade performance long before hitting schema boundaries due to page overflow chaining and variable limits. For AI agents managing dynamic metadata, alternatives like JSONB and persistent workspace indexing provide predictable query performance.

Source: https://fast.io/resources/sqlite-column-limit/
Author: [Tom Langridge](https://fast.io/authors/tom-langridge/)
Last reviewed: 2026-10-27

## What Governs the SQLite Column Limit in Architecture and Code

Under official SQLite architectural specifications, the default column limit is 2,000 columns per table, with a compile-time hard upper bound of 32,767 columns. As documented in the official [SQLite implementation limits](https://www.sqlite.org/limits.html), this ceiling applies to standard relational tables, views, virtual tables, and the projection expressions of compound SELECT queries.

"The SQLite column limit restricts a single table or view to a default maximum of 2,000 columns (defined by SQLITE_MAX_COLUMN), with a compile-time hard upper bound of 32,767 columns."

When database architects discuss the sqlite max columns per table, they are referencing the `SQLITE_MAX_COLUMN` preprocessor parameter. SQLite established the 2,000-column default boundary to safeguard memory consumption and prevent inefficient query execution plans. While modern hardware handles gigabytes of RAM, SQLite was engineered as an embedded library intended to operate efficiently on memory-constrained systems, microcontrollers, and mobile devices.

The table below outlines the core limits governing columns, parameters, and query statements in SQLite:

| Parameter | Default Value | Hard Upper Bound | Scope of Enforcement |
|---|---|---|---|
| `SQLITE_MAX_COLUMN` | 2,000 columns | 32,767 columns | Maximum columns in a table, view, or INSERT statement |
| `SQLITE_LIMIT_COLUMN` | 2,000 (runtime) | Value of `SQLITE_MAX_COLUMN` | Runtime category adjusted via `sqlite3_limit()` |
| `SQLITE_MAX_VARIABLE_NUMBER` | 32,766 parameters | 32,766 (historically 999) | Maximum host parameters (`?`) in a single query |
| `SQLITE_MAX_SQL_LENGTH` | 1,000,000,000 bytes | 1,073,741,824 bytes (1 GB) | Maximum length of raw SQL statement text |
| `SQLITE_MAX_EXPR_DEPTH` | 1,000 levels | Configurable at compile time | Maximum depth of an expression parse tree |

### Compiling SQLite with Custom Column Ceilings

If an application requires more than 2,000 columns in a single table, the developer must recompile the SQLite library from source. The compile-time flag `SQLITE_MAX_COLUMN` accepts positive integer values reaching the 32,767 (2^15 - 1) architectural ceiling.

To build an SQLite binary with the maximum allowable column count, compile the amalgamation source with the preprocessor definition:

```bash
gcc -O2 -DSQLITE_MAX_COLUMN=32767 -DSQLITE_ENABLE_JSON1 -c sqlite3.c
ar rcs libsqlite3.a sqlite3.o
```

Setting `SQLITE_MAX_COLUMN` beyond 32,767 produces a compiler error or undefined behavior because SQLite internally uses signed 16-bit integers (2-byte signed numbers) to index and track column offsets within its internal data structures. Attempting to declare 32,768 columns overflows the 16-bit integer boundary.

### Runtime Limit Inspection and Adjustment via sqlite3_limit

Applications cannot increase the column limit beyond the compile-time value at runtime. However, applications can inspect the active limit or reduce it on a per-connection basis using the C interface `sqlite3_limit()`:

```c
#include <stdio.h>
#include <sqlite3.h>

int main(void) {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    int current_limit = sqlite3_limit(db, SQLITE_LIMIT_COLUMN, -1);
    printf("Current SQLite column limit: %d", current_limit);
    int previous_limit = sqlite3_limit(db, SQLITE_LIMIT_COLUMN, 50);
    printf("Adjusted limit from %d down to 50", previous_limit);
    int truncated = sqlite3_limit(db, SQLITE_LIMIT_COLUMN, 50000);
    printf("Result of setting 50000: %d", truncated);
    sqlite3_close(db);
    return 0;
}
```

When an application calls `sqlite3_limit(db, SQLITE_LIMIT_COLUMN, new_val)`, SQLite verifies whether `new_val` is positive. If `new_val` exceeds `SQLITE_MAX_COLUMN`, SQLite silently truncates the request to the compile-time maximum. Runtime adjustments are primarily employed by multi-tenant environments and sandboxes to restrict untrusted SQL inputs from constructing memory-intensive schemas.

## Why Wide Tables Trigger Page Overflow Chaining and I/O Bottlenecks

While the sqlite maximum column limit permits 2,000 columns by default, production databases encounter severe performance degradation long before approaching this ceiling. The root cause lies in how SQLite serializes records onto physical storage pages.

SQLite organizes database files into fixed-size blocks called pages, defaulting to 4,096 bytes. Tables use a B+ tree structure where interior pages direct search paths and leaf pages store actual data rows. Each row is represented as a single contiguous binary record consisting of a record header and a data payload.

### The Anatomy of Record Header Bloat

The record header describes the data types and byte lengths of every column in that row. It contains:

1. A variable-length integer (varint) indicating the total byte size of the record header.
2. A sequence of varints representing the serial type code for each individual column in the table.

```text
SQLite Record Structure on Disk:
+-----------------------------------------------------------------------+
| Record Header Length (varint)                                         |
+-----------------------------------------------------------------------+
| Serial Type 1 | Serial Type 2 | ... | Serial Type N (varints)         |
+-----------------------------------------------------------------------+
| Value 1 Payload | Value 2 Payload | ... | Value N Payload             |
+-----------------------------------------------------------------------+
```

In a table with 2,000 columns, the record header must store 2,000 distinct serial type varints. Even if every column contains a NULL value (which requires a 1-byte serial type of 0), the header consumes 2,001 bytes. If the columns store integers, text, or floating-point values, serial type codes consume 1 to 3 bytes each.

Consequently, for a 2,000-column row, the record header alone demands between 2,000 and 4,000 bytes of storage space before a single byte of actual data payload is written.

### The Mathematics of Page Overflow Chaining

To maintain balanced B+ trees and preserve predictable search depths, SQLite limits how much of a record payload can reside directly on a leaf page. The internal B-tree engine calculates local payload boundaries using the following formulas:

```text
maxLocal = (page_size - 35) * 64 / 255
minLocal = (page_size - 35) * 32 / 255
```

On a standard 4,096-byte page:
- `maxLocal` is approximately 1,020 bytes.
- `minLocal` is approximately 510 bytes.

If a complete record (header plus payload) fits within `maxLocal`, the entire row is stored directly on the leaf page. However, if the record exceeds `maxLocal`, SQLite stores only `minLocal` bytes on the leaf page and spills the remainder onto one or more overflow pages.

Because the record header of a 2,000-column table requires at least 2,000 bytes, every single row inserted into such a table immediately exceeds the 1,020-byte local threshold. Every row is forced onto overflow pages regardless of how compact the data values are.

### Performance Consequences of Chained Overflow Pages

Overflow pages are structured as a singly linked list. The first 4 bytes of each overflow page contain a 32-bit pointer to the next overflow page in the chain, with the remaining bytes holding payload data:

```text
Leaf Page (Cell)
  |-- Local Payload (~510 bytes)
  +-- Overflow Page Pointer (4 bytes) ---> [ Overflow Page 1 ]
                                              |-- Payload Segment
                                              +-- Next Page Pointer ---> [ Overflow Page 2 ]
                                                                            |-- Final Payload
                                                                            +-- Next: 0x00000000
```

This architecture introduces three operational bottlenecks:

- **Random I/O Overhead:** To read a single row from a wide table, the storage engine cannot read a single continuous block. It must traverse the leaf page and follow multiple 4-byte pointers across disconnected overflow pages scattered across the database file.
- **Cache Thrashing:** The SQLite page cache, governed by `PRAGMA cache_size`, defaults to roughly 2,000 pages. When queries scan rows that span dozens of overflow pages, the cache fills with overflow fragments, evicting critical index and root B-tree pages.
- **Amplified Write Latency:** Inserting or updating a row in a wide table requires allocating, initializing, and writing to multiple pages on disk, turning what should be a single sequential write into a multi-page allocation cycle.

## How Query Execution Costs and Parameter Caps Degrade Wide Schemas

Beyond physical disk layout, the sqlite column count limit intersects with query compilation mechanics, memory allocations, and routine maintenance commands. Developers attempting to run analytical queries or high-throughput writes on wide tables encounter constraints across the database engine lifecycle.

### The O(N^2) Cost in Query Preparation

Before executing an SQL statement, SQLite parses the query string and compiles it into Virtual Database Engine (VDBE) bytecode using the `sqlite3_prepare_v2()` interface. The SQLite query planner and code generator contain multiple algorithmic routines that exhibit O(N^2) time complexity relative to the number of columns involved.

When compiling a query like `SELECT * FROM wide_table`, the code generator must:
1. Resolve column names and verify table schemas across every column identifier.
2. Check for duplicate column names and resolve type affinities.
3. Allocate internal register structures for every projected expression.

For a table with 20 columns, N^2 is 400 operations. For a table with 2,000 columns, N^2 represents 4,000,000 operations. Generating SQL statements that reference hundreds or thousands of columns causes `sqlite3_prepare_v2()` to execute noticeably slower, consuming significant CPU time and allocating substantial temporary heap memory during compilation.

### The Parameter Limit Collision: SQLITE_MAX_VARIABLE_NUMBER

A common operational hurdle when working with wide tables is parameter binding. In application code, developers use parameterized queries rather than raw string interpolation:

```sql
INSERT INTO sensor_matrix VALUES (?, ?, ?, ..., ?);
```

To insert a single row into a table with 2,000 columns, the application must bind 2,000 distinct parameters.

In SQLite releases prior to version 3.32.0 (released in May 2020), `SQLITE_MAX_VARIABLE_NUMBER` was set to a default ceiling of 999. In those environments, executing a parameterized INSERT statement on a table with more than 999 columns immediately failed with:

```text
Error: too many SQL variables
```

SQLite 3.32.0 raised the default `SQLITE_MAX_VARIABLE_NUMBER` to 32,766, aligning it with the compile-time column maximum. However, many embedded runtimes, mobile OS distributions, and legacy enterprise software stacks continue to run older SQLite versions. If an application must maintain backward compatibility with legacy distributions, 999 parameters remains the effective ceiling.

### Maintenance Lockouts During VACUUM Operations

As wide rows are updated or deleted, SQLite leaves unused space fragments inside overflow pages. Reclaiming this wasted disk space requires running the `VACUUM` command.

The `VACUUM` process works by creating a new temporary database file, reading every active record from the original file, re-inserting records in optimal order, and replacing the original file. On wide tables, `VACUUM` degrades severely:

- **Scratch Space Demands:** `VACUUM` requires unallocated scratch disk space equal to the entire active database file. A large database file with wide tables requires equivalent free space on the host volume to complete a vacuum.
- **Exclusive Locking:** During the entire duration of the `VACUUM` process, SQLite holds an exclusive lock on the database. All other read and write transactions are blocked.
- **Reconstruction Latency:** Because every row must be unpacked from chained overflow pages, parsed through VDBE registers, and repacked into fresh pages, vacuuming a wide table takes substantially longer than vacuuming a normalized schema of identical byte size.

## How to Implement Architectural Workarounds with JSONB and Partitioning

When application requirements demand storing hundreds or thousands of attributes per entity, developers should avoid expanding table column counts toward the 2,000 cap. Three proven architectural patterns provide flexible data modeling without triggering overflow page degradation.

### Pattern 1: Semi-Structured Attributes with JSONB

Introduced in SQLite 3.45.0, `JSONB` stores JSON documents in a pre-parsed binary format. Unlike standard textual JSON, JSONB allows SQLite to read, query, and mutate specific object paths without parsing text strings on every access.

Instead of defining hundreds of sparsely populated columns where most fields remain NULL, define a compact relational schema with a JSONB column for dynamic attributes:

```sql
CREATE TABLE device_telemetry (
    device_id TEXT NOT NULL,
    recorded_at INTEGER NOT NULL,
    firmware_version TEXT NOT NULL,
    attributes BLOB,
    PRIMARY KEY (device_id, recorded_at)
);

INSERT INTO device_telemetry (device_id, recorded_at, firmware_version, attributes)
VALUES (
    'dev_8841',
    1730000000,
    'v2.4.1',
    jsonb('{"temperature": 21.4, "voltage": 3.29, "error_flags": ["warn_low_batt"]}')
);

SELECT
    device_id,
    json_extract(attributes, '$.temperature') AS temp
FROM device_telemetry
WHERE json_extract(attributes, '$.voltage') < 3.30;
```

To accelerate queries against specific JSON fields, define virtual generated columns paired with standard B-tree indexes:

```sql
ALTER TABLE device_telemetry ADD COLUMN battery_voltage REAL
    GENERATED ALWAYS AS (json_extract(attributes, '$.voltage')) STORED;

CREATE INDEX idx_telemetry_voltage ON device_telemetry(battery_voltage);
```

This pattern eliminates record header bloat. Missing keys consume zero bytes of disk space, and adding new attributes requires zero schema migrations.

### Pattern 2: Vertical Table Partitioning

Vertical partitioning separates frequently queried core columns from rarely accessed auxiliary attributes using one-to-one extension tables:

```sql
CREATE TABLE customer_core (
    customer_id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    account_status TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE TABLE customer_attributes_extended (
    customer_id INTEGER PRIMARY KEY,
    survey_responses TEXT,
    marketing_tags TEXT,
    locale_preferences TEXT,
    FOREIGN KEY (customer_id) REFERENCES customer_core(customer_id) ON DELETE CASCADE
);
```

By keeping `customer_core` narrow, dozens of core records fit onto a single leaf page. Routine identity and status lookups execute entirely within memory, while heavy profile data is retrieved only when an explicit `JOIN` is executed.

### Pattern 3: Entity-Attribute-Value (EAV) Modeling

For datasets where entities possess wildly disparate properties, the Entity-Attribute-Value (EAV) design models attributes as rows rather than columns:

```sql
CREATE TABLE entity_eav (
    entity_id INTEGER NOT NULL,
    attribute_name TEXT NOT NULL,
    attribute_value TEXT,
    PRIMARY KEY (entity_id, attribute_name)
);
CREATE INDEX idx_eav_lookup ON entity_eav(attribute_name, attribute_value);
```

While EAV requires complex multi-join queries to reconstruct complete entities, it enforces a fixed 3-column schema that never encounters column count boundaries.

## How AI Agents Scale Metadata and File Storage Beyond Table Limits

Autonomous AI agents, coding assistants, and multi-agent coordination pipelines frequently encounter database limit constraints. When agents ingest codebases, legal contracts, or technical documentation, they generate high-dimensional metadata, execution traces, and extracted entity maps. Attempting to store every extracted variable as a column in a local SQLite database quickly triggers schema errors and disk bloat.

### The Constraint of Local Storage and Context Windows

AI workflows face physical boundaries at both the model layer and the database layer. In model chat sessions, context windows restrict how much documentation can be supplied at once. As documented on Anthropic's help page https://support.claude.com/en/articles/8241126-upload-files-to-claude, a chat session accepts up to 20 files at up to 500MB each, while Claude Projects accepts files up to 30MB each with unlimited file count, bounded by total context window capacity.

When an autonomous agent must reason across thousands of files, attaching raw files directly exhausts model context. Conversely, flattening full documents into local SQLite database columns forces rows onto dozens of chained overflow pages, resulting in disk cache thrashing and unmanageable database files.

The table below contrasts how different architectural approaches handle extensive agent metadata and large file corpora:

| Storage Architecture | Column/Field Scalability | Payload Capacity | Search & Retrieval Mechanics |
|---|---|---|---|
| Monolithic SQLite Tables | Capped at 2,000 columns (default) | Bounded by page overflow | Raw SQL queries with index scans |
| SQLite JSONB Columns | Dynamic keys, single BLOB column | Single BLOB document payload | `json_extract()` with generated indexes |
| Persistent Cloud Workspaces | Dynamic typed schema extraction | File uploads based on plan tier | Hybrid semantic search and MCP tools |

### Decoupling Agent Memory with Fast.io Workspaces

The recommended pattern for multi-agent architectures is to separate state tracking from document corpora. Keep local SQLite tables lean, storing only active execution pointers, task queues, and session IDs. Offload raw documents, extracted analysis, and shared team assets to persistent cloud workspaces in Fast.io.

Fast.io provides an intelligent workspace substrate designed specifically for humans and AI agents:

- **Intelligence Mode:** When Intelligence Mode is enabled on a workspace, Fast.io automatically indexes uploaded files for semantic search and Retrieval-Augmented Generation (RAG). Agents can query extensive documentation corpora with source citations without configuring a standalone vector database.
- **Metadata Views:** Instead of designing wide database schemas, teams can use [Metadata Views](/product/document-data-extraction/) to turn unstructured PDFs, spreadsheets, presentations, and images into structured, queryable data. Users and agents describe desired fields in natural language, and Fast.io designs a typed schema (Text, Integer, Decimal, Boolean, URL, JSON, Date & Time), populating filterable views without templates or OCR rules.
- **Consolidated MCP Toolset:** Agents connect to Fast.io using the Model Context Protocol over Streamable HTTP. Claude apps and general agent runtimes connect via `https://mcp.fast.io/mcp/tools`, while coding agents in IDEs or terminals connect via `https://mcp.fast.io/mcp/code`. Agents can search, read, write, and manage workspace files directly through standardized tools.
- **Auditability and Version History:** Fast.io maintains per-file version history, allowing teams to review and restore prior file revisions. A detailed activity log records all storage modifications (uploads, edits, moves, and deletions) alongside membership and share events, ensuring that multi-agent operations remain transparent.
- **Flexible Synchronization:** Cloud Sync connects Google Drive, Dropbox, Box, and OneDrive folders to a workspace, one-way or two-way, on a schedule or on demand.

The table below outlines Fast.io subscription tiers for agent teams:

| Plan | Monthly Pricing | Included Storage | Monthly Credit Allowance |
|---|---|---|---|
| Starter | $9.99/mo | 250 GB | 100,000 credits |
| Business | $49.99/mo | 5 TB | 600,000 credits |
| Enterprise | $199.99/mo | 25 TB | 3,000,000 credits |

Monthly plans start with a 14-day free trial that requires a credit card. By pairing compact local SQLite state tables with scalable cloud workspaces, development teams eliminate column limit headaches while equipping agents with durable, searchable memory.

## Frequently asked questions

### How many columns can an SQLite table have?

By default, an SQLite table can have a maximum of 2,000 columns, governed by the SQLITE_MAX_COLUMN compile-time parameter. If you compile SQLite from source, you can increase this ceiling up to an absolute hard upper bound of 32,767 columns.

### Can I increase the column limit in SQLite?

You cannot increase the SQLite column limit at runtime beyond the compile-time cap. While the sqlite3_limit() C interface allows applications to lower the column limit dynamically on a per-connection basis, attempts to raise it higher than SQLITE_MAX_COLUMN are silently truncated to the compile-time maximum. Increasing the ceiling up to 32,767 columns requires recompiling the SQLite library from source with a higher SQLITE_MAX_COLUMN definition.

### Why does SQLite limit columns to 2000?

SQLite limits columns to 2,000 by default to conserve memory and preserve query processing speed on resource-constrained devices. The internal SQLite code generator uses algorithms that exhibit O(N^2) complexity relative to column count during query preparation, meaning excessively wide tables cause noticeable latency and high memory consumption in sqlite3_prepare_v2().

### What error occurs when exceeding the SQLite column limit?

If an application executes a CREATE TABLE or ALTER TABLE statement that attempts to declare more columns than permitted by SQLITE_MAX_COLUMN, SQLite returns an error stating "too many columns on table". If a query exceeds the parameter ceiling during INSERT, SQLite returns "too many SQL variables".

### How does SQLite store wide rows that exceed the page size?

When an SQLite row exceeds the local payload threshold of a leaf page (roughly 1,020 bytes on a standard 4,096-byte page), SQLite stores the initial bytes locally and spills the remaining payload onto a chain of overflow pages. These overflow pages form a singly linked list, which increases random disk I/O and degrades page cache efficiency.

### When should AI agents use JSONB instead of adding database columns?

AI agents should use JSONB columns when managing dynamic, semi-structured metadata, execution logs, or sparse attributes where most fields are optional. Storing flexible attributes in a JSONB column avoids schema migrations, eliminates record header bloat on disk, and allows targeted indexing through generated columns.

## Sources

- [SQLite: Implementation Limits](https://www.sqlite.org/limits.html): Under official SQLite architectural specifications, the default column limit is 2,000 columns per table, with a compile-time hard upper bound of 32,767 columns.
- [Claude Help Center: Upload files to Claude](https://support.claude.com/en/articles/8241126-upload-files-to-claude): Anthropic limits Claude chat uploads to 20 files at up to 500MB each and Project files to 30MB, bounded by total context window capacity.

## About Fast.io

Fast.io provides shared workspaces where people and AI agents work on the same files, with built-in semantic search and citation-backed chat over what they hold. Agents reach it through a remote MCP server, a REST API at https://api.fast.io/current/, and a command line client published on npm as @vividengine/fastio-cli. MCP setup is at https://mcp.fast.io/docs: Claude and most MCP clients connect to https://mcp.fast.io/mcp/tools, ChatGPT to https://mcp.fast.io/mcp/operations, and coding agents to https://mcp.fast.io/mcp/code.