Debian Codegraph @

www/limits.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Implementation Limits For SQLite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120
121 <h2>Limits In SQLite</h2>
122
123 <p>
124 "Limits" in the context of this article means sizes or
125 quantities that can not be exceeded. We are concerned
126 with things like the maximum number of bytes in a
127 BLOB or the maximum number of columns in a table.
128 </p>
129
130 <p>
131 SQLite was originally designed with a policy of avoiding
132 arbitrary limits.
133 Of course, every program that runs on a machine with finite
134 memory and disk space has limits of some kind. But in SQLite,
135 those limits
136 were not well defined. The policy was that if it would fit
137 in memory and you could count it with a 32-bit integer, then
138 it should work.
139 </p>
140
141 <p>
142 Unfortunately, the no-limits policy has been shown to create
143 problems. Because the upper bounds were not well
144 defined, they were not tested, and bugs were often found
145 when pushing SQLite to extremes. For this reason, versions
146 of SQLite since about release 3.5.8 (2008-04-16)
147 have well-defined limits, and those limits are tested as part of
148 the <a href="testing.html">test suite</a>.
149 </p>
150
151 <p>
152 This article defines what the limits of SQLite are and how they
153 can be customized for specific applications. The default settings
154 for limits are normally quite large and adequate for almost every
155 application. Some applications may want to increase a limit here
156 or there, but we expect such needs to be rare. More commonly,
157 an application might want to recompile SQLite with much lower
158 limits to avoid excess resource utilization in the event of
159 bugs in higher-level SQL statement generators or to help thwart
160 attackers who inject malicious SQL statements.
161 </p>
162
163 <p>
164 Some limits can be changed at run-time on a per-connection basis
165 using the <a href="c3ref/limit.html">sqlite3_limit()</a> interface with one of the
166 <a href="c3ref/c_limit_attached.html#sqlitelimitlength">limit categories</a> defined for that interface.
167 Run-time limits are designed for applications that have multiple
168 databases, some of which are for internal use only and others which
169 can be influenced or controlled by potentially hostile external agents.
170 For example, a web browser application might use an internal database
171 to track historical page views but have one or more separate databases
172 that are created and controlled by javascript applications that are
173 downloaded from the internet.
174 The <a href="c3ref/limit.html">sqlite3_limit()</a> interface allows internal databases managed by
175 trusted code to be unconstrained while simultaneously placing tight
176 limitations on databases created or controlled by untrusted external
177 code in order to help prevent a denial of service attack.
178 </p>
179
180
181 <ol>
182 <a name="max_length"></a>
183 <li><p><b>Maximum length of a string or BLOB</b></p>
184
185 <p>
186 The maximum number of bytes in a string or BLOB in SQLite is defined
187 by the preprocessor macro SQLITE_MAX_LENGTH. The default value
188 of this macro is 1 billion (1 thousand million or 1,000,000,000).
189 You can raise or lower this value at compile-time using a command-line
190 option like this:
191 </p>
192
193 <blockquote>-DSQLITE_MAX_LENGTH=123456789</blockquote>
194
195 <p>
196 The current implementation will only support a string or BLOB
197 length up to 2<small><sup>31</sup></small>-3 or 2147483645. And
198 some built-in functions such as hex() might fail well before that
199 point. In security-sensitive applications it is best not to
200 try to increase the maximum string and blob length. In fact,
201 you might do well to lower the maximum string and blob length
202 to something more in the range of a few million if that is
203 possible.
204 </p>
205
206 <p>
207 During part of SQLite's INSERT and SELECT processing, the complete
208 content of each row in the database is encoded as a single BLOB.
209 So the SQLITE_MAX_LENGTH parameter also determines the maximum
210 number of bytes in a row.
211 </p>
212
213 <p>
214 The maximum string or BLOB length can be lowered at run-time using
215 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitlength">SQLITE_LIMIT_LENGTH</a>,size) interface.
216 </p>
217 </li><a name="max_column"></a>
218 <li><p><b>Maximum Number Of Columns</b></p>
219
220 <p>
221 The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper
222 bound on:
223 </p>
224
225 <ul>
226 <li>The number of columns in a table</li>
227 <li>The number of columns in an index</li>
228 <li>The number of columns in a view</li>
229 <li>The number of terms in the SET clause of an UPDATE statement</li>
230 <li>The number of columns in the result set of a SELECT statement</li>
231 <li>The number of terms in a GROUP BY or ORDER BY clause</li>
232 <li>The number of values in an INSERT statement</li>
233 </ul>
234
235 <p>
236 The default setting for SQLITE_MAX_COLUMN is 2000. You can change it
237 at compile time to values as large as 32767. On the other hand, many
238 experienced database designers will argue that a well-normalized database
239 will never need more than 100 columns in a table.
240 </p>
241
242 <p>
243 In most applications, the number of columns is small - a few dozen.
244 There are places in the SQLite code generator that use algorithms
245 that are O(N&sup2;) where N is the number of columns.
246 So if you redefine SQLITE_MAX_COLUMN to be a
247 really huge number and you generate SQL that uses a large number of
248 columns, you may find that <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
249 runs slowly.</p>
250
251
252 <p>
253 The maximum number of columns can be lowered at run-time using
254 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitcolumn">SQLITE_LIMIT_COLUMN</a>,size) interface.
255 </p>
256
257 </li><a name="max_sql_length"></a>
258 <li><p><b>Maximum Length Of An SQL Statement</b></p>
259
260 <p>
261 The maximum number of bytes in the text of an SQL statement is
262 limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.
263 </p>
264
265 <p>
266 If an SQL statement is limited to be a million bytes in length, then
267 obviously you will not be able to insert multi-million byte strings
268 by embedding them as literals inside of INSERT statements. But
269 you should not do that anyway. Use host <a href="lang_expr.html#varparam">parameters</a>
270 for your data. Prepare short SQL statements like this:
271 </p>
272
273 <blockquote>
274 INSERT INTO tab1 VALUES(?,?,?);
275 </blockquote>
276
277 <p>
278 Then use the <a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> functions
279 to bind your large string values to the SQL statement. The use of binding
280 obviates the need to escape quote characters in the string, reducing the
281 risk of SQL injection attacks. It also runs faster since the large
282 string does not need to be parsed or copied as much.
283 </p>
284
285 <p>
286 The maximum length of an SQL statement can be lowered at run-time using
287 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitsqllength">SQLITE_LIMIT_SQL_LENGTH</a>,size) interface.
288 </p>
289 </li><li><p><b>Maximum Number Of Tables In A Join</b></p>
290
291 <p>
292 SQLite does not support joins containing more than 64 tables.
293 This limit arises from the fact that the SQLite code generator
294 uses bitmaps with one bit per join-table in the query optimizer.
295 </p>
296
297 <p>
298 SQLite uses an efficient <a href="queryplanner-ng.html">query planner algorithm</a>
299 and so even a large join can be <a href="c3ref/prepare.html">prepared</a> quickly.
300 Hence, there is no mechanism to raise or lower the limit on the
301 number of tables in a join.
302 </p>
303 </li><a name="max_expr_depth"></a>
304 <li><p><b>Maximum Depth Of An Expression Tree</b></p>
305
306 <p>
307 SQLite parses expressions into a tree for processing. During
308 code generation, SQLite walks this tree recursively. The depth
309 of expression trees is therefore limited in order to avoid
310 using too much stack space.
311 </p>
312
313 <p>
314 The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression
315 tree depth. If the value is 0, then no limit is enforced. The
316 current implementation has a default value of 1000.
317 </p>
318
319 <p>
320 The maximum depth of an expression tree can be lowered at run-time using
321 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitexprdepth">SQLITE_LIMIT_EXPR_DEPTH</a>,size) interface if the
322 SQLITE_MAX_EXPR_DEPTH is initially positive. In other words, the maximum
323 expression depth can be lowered at run-time if there is already a
324 compile-time limit on the expression depth. If SQLITE_MAX_EXPR_DEPTH is
325 set to 0 at compile time (if the depth of expressions is unlimited) then
326 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitexprdepth">SQLITE_LIMIT_EXPR_DEPTH</a>,size) is a no-op.
327 </p>
328
329
330 </li><a name="max_function_arg"></a>
331 <li><p><b>Maximum Number Of Arguments On A Function</b></p>
332
333 <p>
334 The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number
335 of parameters that can be passed to an SQL function. For many years, the
336 default value was about 100 but the default was raised to 1000 beginning
337 with SQLite version 3.48.0 (2025-01-14).
338
339 <p>The number of arguments to a function is sometimes stored in a signed
340 16-bit integer. So there is a hard upper bound on SQLITE_MAX_FUNCTION_ARG
341 of 32767.</p>
342
343 <p>
344 The maximum number of arguments in a function can be lowered at run-time using
345 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitfunctionarg">SQLITE_LIMIT_FUNCTION_ARG</a>,size) interface.
346 </p>
347 </li><a name="max_compound_select"></a>
348 <li><p><b>Maximum Number Of Terms In A Compound SELECT Statement</b></p>
349
350 <p>
351 A compound <a href="lang_select.html">SELECT</a> statement is two or more SELECT statements connected
352 by operators UNION, UNION ALL, EXCEPT, or INTERSECT. We call each
353 individual SELECT statement within a compound SELECT a "term".
354 </p>
355
356 <p>
357 The code generator in SQLite processes compound SELECT statements using
358 a recursive algorithm. In order to limit the size of the stack, we
359 therefore limit the number of terms in a compound SELECT. The maximum
360 number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.
361 We think this is a generous allotment since in practice we almost
362 never see the number of terms in a compound select exceed single digits.
363 </p>
364
365 <p>
366 The maximum number of compound SELECT terms can be lowered at run-time using
367 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitcompoundselect">SQLITE_LIMIT_COMPOUND_SELECT</a>,size) interface.
368 </p>
369
370
371 </li><a name="max_like_pattern_length"></a>
372 <li><p><b>Maximum Length Of A LIKE Or GLOB Pattern</b></p>
373
374 <p>
375 The pattern matching algorithm used in the default <a href="lang_expr.html#like">LIKE</a> and <a href="lang_expr.html#glob">GLOB</a>
376 implementation of SQLite can exhibit O(N&sup2;) performance (where
377 N is the number of characters in the pattern) for certain pathological
378 cases. To avoid denial-of-service attacks from miscreants who are able
379 to specify their own LIKE or GLOB patterns, the length of the LIKE
380 or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.
381 The default value of this limit is 50000. A modern workstation can
382 evaluate even a pathological LIKE or GLOB pattern of 50000 bytes
383 relatively quickly. The denial of service problem only comes into
384 play when the pattern length gets into millions of bytes. Nevertheless,
385 since most useful LIKE or GLOB patterns are at most a few dozen bytes
386 in length, paranoid application developers may want to reduce this
387 parameter to something in the range of a few hundred if they know that
388 external users are able to generate arbitrary patterns.
389 </p>
390
391 <p>
392 The maximum length of a LIKE or GLOB pattern can be lowered at run-time using
393 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitlikepatternlength">SQLITE_LIMIT_LIKE_PATTERN_LENGTH</a>,size) interface.
394 </p>
395 </li><a name="max_variable_number"></a>
396 <li><p><b>Maximum Number Of Host Parameters In A Single SQL Statement</b></p>
397
398 <p>
399 A host <a href="lang_expr.html#varparam">parameter</a> is a place-holder in an SQL statement that is filled
400 in using one of the
401 <a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> interfaces.
402 Many SQL programmers are familiar with using a question mark ("?") as a
403 host parameter. SQLite also supports named host parameters prefaced
404 by ":", "$", or "@" and numbered host parameters of the form "?123".
405 </p>
406
407 <p>
408 Each host parameter in an SQLite statement is assigned a number. The
409 numbers normally begin with 1 and increase by one with each new
410 parameter. However, when the "?123" form is used, the host parameter
411 number is the number that follows the question mark.
412 </p>
413
414 <p>
415 SQLite allocates space to hold all host parameters between 1 and the
416 largest host parameter number used. Hence, an SQL statement that contains
417 a host parameter like ?1000000000 would require gigabytes of storage.
418 This could easily overwhelm the resources of the host machine.
419 To prevent excessive memory allocations,
420 the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER,
421 which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22)
422 or 32766 for SQLite versions after 3.32.0.
423 </p>
424
425 <p>
426 The maximum host parameter number can be lowered at run-time using
427 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitvariablenumber">SQLITE_LIMIT_VARIABLE_NUMBER</a>,size) interface.
428 </p>
429 </li><a name="max_trigger_depth"></a>
430 <li><p><b>Maximum Depth Of Trigger Recursion</b></p>
431
432 <p>
433 SQLite limits the depth of recursion of triggers in order to prevent
434 a statement involving recursive triggers from using an unbounded amount
435 of memory.
436 </p>
437
438 <p>Prior to SQLite <a href="releaselog/3_6_18.html">version 3.6.18</a> (2009-09-11),
439 triggers were not recursive and so
440 this limit was meaningless. Beginning with version 3.6.18, recursive triggers
441 are supported but have to be explicitly enabled using the
442 <a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a> statement.
443 The SQLITE_MAX_TRIGGER_DEPTH is
444 only meaningful if recursive triggers are enabled.</p>
445
446 <p>The default maximum trigger recursion depth is 1000.</p>
447 </li><a name="max_attached"></a>
448 <li><p><b>Maximum Number Of Attached Databases</b></p>
449
450 <p>
451 The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
452 that allows two or more databases to be associated to the same database
453 connection and to operate as if they were a single database. The number
454 of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED
455 which is set to 10 by default.
456 The maximum number of attached databases cannot be increased above 125.</p>
457
458 <p>
459 The maximum number of attached databases can be lowered at run-time using
460 the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitattached">SQLITE_LIMIT_ATTACHED</a>,size) interface.
461 </p>
462 </li><a name="max_page_count"></a>
463 <li><p><b>Maximum Number Of Pages In A Database File</b></p>
464
465 <p>
466 SQLite is able to limit the size of a database file to prevent
467 the database file from growing too large and consuming too much
468 disk space.
469 The SQLITE_MAX_PAGE_COUNT parameter
470 is the maximum number of pages allowed in a single
471 database file. An attempt to insert new data that would cause
472 the database file to grow larger than this will return
473 SQLITE_FULL.
474 </p>
475
476 <p>
477 The largest possible setting for SQLITE_MAX_PAGE_COUNT is 4294967294
478 (2<sup><small>32</small></sup>-2).
479 Since version 3.45.0 (2024-01-15), 4294967294 is
480 also the default value for SQLITE_MAX_PAGE_COUNT.
481 When used with the default page size of 4096 bytes, this gives a
482 maximum database size of about 17.5 terabytes.
483 If the page size is increased to the maximum of 65536 bytes, then the
484 database file can grow to be as large as about 281 terabytes.</p>
485
486 <p>
487 The <a href="pragma.html#pragma_max_page_count">
488 max_page_count PRAGMA</a> can be used to raise or lower this
489 limit at run-time.
490 </p>
491 </li><li><p><b>Maximum Number Of Rows In A Table</b></p>
492
493 <p>
494 The theoretical maximum number of rows in a table is
495 2<sup><small>64</small></sup> (18446744073709551616 or about 1.8e+19).
496 This limit is unreachable since the maximum database size of 281 terabytes
497 will be reached first. A 281 terabytes database can hold no more than
498 approximately 2e+13 rows, and then only if there are no indices and if
499 each row contains very little data.
500 </li><li><p><b>Maximum Database Size</b></p>
501
502 <p>
503 Every database consists of one or more "pages". Within a single database,
504 every page is the same size, but different databases can have page sizes
505 that are powers of two between 512 and 65536, inclusive. The maximum
506 size of a database file is 4294967294 pages. At the maximum page size
507 of 65536 bytes, this translates into a maximum database size of
508 approximately 2.8e+14 bytes (281 terabytes, or 256 tebibytes, or
509 281474 gigabytes, or 262143 gibibytes).
510 <p>
511 This particular upper bound is untested since the developers do not
512 have access to hardware capable of reaching this limit. However, tests
513 do verify that SQLite behaves correctly and sanely when a database
514 reaches the maximum file size of the underlying filesystem (which is
515 usually much less than the maximum theoretical database size) and when
516 a database is unable to grow due to disk space exhaustion.
517 </li><li><p><b>Maximum Number Of Tables In A Schema</b></p>
518
519 <p>
520 Each table and index requires at least one page in the database file.
521 Thus the maximum number of pages in a database file is also the upper
522 bound on the number of tables and indices in a schema. An "index" in
523 the previous sentences means an index created explicitly using a
524 <a href="lang_createindex.html">CREATE INDEX</a> statement or implicit indices created by UNIQUE and
525 PRIMARY KEY constraints.
526 <p>
527 Whenever a database is opened, the entire schema is scanned and parsed
528 and a parse tree for the schema is held in memory. That means that
529 database connection startup time and initial memory usage
530 is proportional to the size of the schema.
531 </li>
532 </ol>
533 <p align="center"><small><i>This page was last updated on 2026-03-11 15:15:23Z </small></i></p>
534