Documentation Source Text

<title>About SQLite</title>

<h1 align=center>About SQLite</h1>

<div class="rightsidebar border2px">
<h4 align="center">Executive Summary</h4>
<ul>
<li> [Full-featured SQL]
<li> <a href='mostdeployed.html'>Billions and billions of deployments</a>
<li> <a href='onefile.html'>Single-file database</a>
<li> <a href='copyright.html'>Public domain source code</a>
<li> All source code in one file (<a href='amalgamation.html'>sqlite3.c</a>)
<li> <a href='footprint.html'>Small footprint</a>
<li> Max DB size: <a href='limits.html'>281 terabytes</a>
     (2<sup><small>47</small></sup> bytes)
<li> Max row size: <a href='limits.html'>1 gigabyte</a>
<li> <a href='fasterthanfs.html'>Faster than direct file I/O</a>
<li> <a href='testing.html'>Aviation-grade quality and testing</a>
<li> <a href='zeroconf.html'>Zero-configuration</a>
<li> <a href='transactional.html'>ACID transactions, even after power loss</a>
<li> <a href='fileformat.html'>Stable, enduring file format</a>

    set xrefChng($vers) $nChng
  }
  incr nChng
}

chng {2020-08-22 (3.33.0)} {
<li> Support for [UPDATE FROM] following the PostgreSQL syntax.
<li> Increase the maximum size of database files to 281 TB.
<li> Extended the [PRAGMA integrity_check] statement so that it can optionally be
     limited to verifying just a single table and its indexes, rather than the
     entire database file.
<li> Added the [decimal extension] for doing arbitrary-precision decimal arithmetic.
<li> Enhancments to the [ieee754 extension] for working with IEEE 754 binary64 numbers.
<li> [CLI] enhancements:
    <ol type="a">

<p>The main database file consists of one or more pages.  ^The size of a
page is a power of two between 512 and 65536 inclusive.  All pages within
the same database are the same size.  ^The page size for a database file
is determined by the 2-byte integer located at an offset of
16 bytes from the beginning of the database file.</p>

<p>Pages are numbered beginning with 1.  The maximum page number is
4294967294 (2<sup><small>32</small></sup> - 2).  The minimum size
SQLite database is a single 512-byte page.
The maximum size database would be 2147483646 pages at 65536 bytes per
page or 281,474,976,579,584 bytes (about 281 terabytes).  Usually SQLite will
hit the maximum file size limit of the underlying filesystem or disk
hardware long before it hits its own internal size limit.</p>

<p>In common use, SQLite databases tend to range in size from a few kilobytes
to a few gigabytes, though terabyte-size SQLite databases are known to exist
in production.</p>

1073741823, is the maximum number of pages allowed in a single
database file.  An attempt to insert new data that would cause
the database file to grow larger than this will return
SQLITE_FULL.
</p>

<p>
The largest possible setting for SQLITE_MAX_PAGE_COUNT is 4294967294.
When used with the maximum page size of 65536, this gives a maximum
SQLite database size of about 281 terabytes.</p>

<p>
The <a href="pragma.html#pragma_max_page_count">
max_page_count PRAGMA</a> can be used to raise or lower this
limit at run-time.
</p>
}

limititem {Maximum Number Of Rows In A Table} {} {
<p>
The theoretical maximum number of rows in a table is
2<sup><small>64</small></sup> (18446744073709551616 or about 1.8e+19).
This limit is unreachable since the maximum database size of 281 terabytes
will be reached first.  A 281 terabytes database can hold no more than
approximately 2e+13 rows, and then only if there are no indices and if
each row contains very little data.
}

limititem {Maximum Database Size} {} {
<p>
Every database consists of one or more "pages".  Within a single database,
every page is the same size, but different database can have page sizes
that are powers of two between 512 and 65536, inclusive.  The maximum
size of a database file is 4294967294 pages.  At the maximum page size
of 65536 bytes, this translates into a maximum database size of 
approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or
281474 gigabytes or 256,000 gibibytes).
<p>
This particular upper bound is untested since the developers do not 
have access to hardware capable of reaching this limit.  However, tests
do verify that SQLite behaves correctly and sanely when a database 
reaches the maximum file size of the underlying filesystem (which is
usually much less than the maximum theoretical database size) and when
a database is unable to grow due to disk space exhaustion.

But if the website is write-intensive or is so busy that it requires
multiple servers, then consider using an enterprise-class client/server 
database engine instead of SQLite.</p>
</li>

<li><p><b>Very large datasets</b></p>

<p>An SQLite database is limited in size to 281 terabytes 
(2<sup><small>47</small></sup> bytes, 128 tibibytes).
And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum
size of files to something less than this.  So if you are contemplating
databases of this magnitude, you would do well to consider using a
client/server database engine that spreads its content across multiple
disk files, and perhaps across multiple volumes.

</li>

<li><p><b>Big data? &rarr; choose client/server</b></p>

<p>If your data will grow to a size that you are uncomfortable
or unable to fit into a single disk file, then you should select
a solution other than SQLite.  SQLite supports databases up to
281 terabytes in size, assuming you can find a disk drive and filesystem
that will support 281-terabyte files.  Even so, when the size of the
content looks like it might creep into the terabyte range, it would
be good to consider a centralized client/server database.
</li>

<li><p><b>Otherwise &rarr; choose SQLite!</b></p>

<p>For device-local storage with low writer concurrency and less than a
terabyte of content, SQLite is almost always a better solution.  SQLite
is fast and reliable and it requires no configuration or maintenance.
It keeps things simple.  SQLite "just works".
</li>
</ol>