| 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>Appropriate Uses 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 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
Appropriate Uses For SQLite |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#situations_where_sqlite_works_well">1. Situations Where SQLite Works Well</a></div> |
| 126 |
<div class="fancy-toc1"><a href="#situations_where_a_client_server_rdbms_may_work_better">2. Situations Where A Client/Server RDBMS May Work Better</a></div> |
| 127 |
<div class="fancy-toc1"><a href="#checklist_for_choosing_the_right_database_engine">3. Checklist For Choosing The Right Database Engine</a></div> |
| 128 |
</div> |
| 129 |
</details> |
| 130 |
</div> |
| 131 |
|
| 132 |
|
| 133 |
|
| 134 |
|
| 135 |
<p> |
| 136 |
SQLite is not directly comparable to client/server SQL database engines such |
| 137 |
as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to |
| 138 |
solve a different problem. |
| 139 |
</p> |
| 140 |
|
| 141 |
<p> |
| 142 |
Client/server SQL database engines strive to implement a shared repository |
| 143 |
of enterprise data. They emphasize scalability, concurrency, centralization, |
| 144 |
and control. |
| 145 |
SQLite strives to provide local data storage for |
| 146 |
individual applications and devices. SQLite emphasizes economy, |
| 147 |
efficiency, reliability, independence, and simplicity. |
| 148 |
</p> |
| 149 |
|
| 150 |
|
| 151 |
<p> |
| 152 |
SQLite does not compete with client/server databases. |
| 153 |
SQLite competes with <a href="http://man.he.net/man3/fopen">fopen()</a>. |
| 154 |
</p> |
| 155 |
|
| 156 |
<h1 id="situations_where_sqlite_works_well"><span>1. </span>Situations Where SQLite Works Well</h1> |
| 157 |
|
| 158 |
<ul> |
| 159 |
|
| 160 |
<a name="appfileformat"></a> |
| 161 |
|
| 162 |
<li><b>Embedded devices and the internet of things</b> |
| 163 |
|
| 164 |
<p>Because an SQLite database requires no administration, |
| 165 |
it works well in devices that must operate without expert human support. |
| 166 |
SQLite is a good fit for use in |
| 167 |
cellphones, set-top boxes, televisions, game consoles, |
| 168 |
cameras, watches, kitchen appliances, thermostats, automobiles, |
| 169 |
machine tools, airplanes, remote sensors, drones, medical devices, |
| 170 |
and robots: the "internet of things". |
| 171 |
</p> |
| 172 |
|
| 173 |
<p>Client/server database engines are designed to live inside a |
| 174 |
lovingly-attended datacenter at the core of the network. |
| 175 |
SQLite works there too, but SQLite also thrives at the edge of the network, |
| 176 |
fending for itself while providing fast and |
| 177 |
reliable data services to applications that would otherwise |
| 178 |
have dodgy connectivity. |
| 179 |
</p></li> |
| 180 |
|
| 181 |
<li><p><b>Application file format</b></p> |
| 182 |
|
| 183 |
<p> |
| 184 |
SQLite is often used as the on-disk file format |
| 185 |
for desktop applications such as version control systems, |
| 186 |
financial analysis tools, media cataloging and editing suites, CAD |
| 187 |
packages, record keeping programs, and so forth. The traditional |
| 188 |
File/Open operation calls sqlite3_open() to attach to the database |
| 189 |
file. Updates happen automatically as application content is revised |
| 190 |
so the File/Save menu option becomes superfluous. The File/Save_As |
| 191 |
menu option can be implemented using the <a href="backup.html">backup API</a>. |
| 192 |
</p> |
| 193 |
|
| 194 |
<p>There are many benefits to this approach, including improved |
| 195 |
performance, reduced cost and complexity, and |
| 196 |
improved reliability. See technical notes |
| 197 |
<a href="aff_short.html">"aff_short.html"</a> and |
| 198 |
<a href="appfileformat.html">"appfileformat.html"</a> and |
| 199 |
<a href="fasterthanfs.html">"fasterthanfs.html"</a> for more information. |
| 200 |
This use case is closely related to the |
| 201 |
<a href="#wireproto">data transfer format</a> and |
| 202 |
<a href="#container">data container</a> use cases below. |
| 203 |
</p></li> |
| 204 |
|
| 205 |
<a name="website"></a> |
| 206 |
|
| 207 |
<li><p><b>Websites</b></p> |
| 208 |
|
| 209 |
<p>SQLite works great as the database engine for most low to |
| 210 |
medium traffic websites (which is to say, most websites). |
| 211 |
The amount of web traffic that SQLite can handle depends |
| 212 |
on how heavily the website uses its database. Generally |
| 213 |
speaking, any site that gets fewer than 100K hits/day should work |
| 214 |
fine with SQLite. |
| 215 |
The 100K hits/day figure is a conservative estimate, not a |
| 216 |
hard upper bound. |
| 217 |
SQLite has been demonstrated to work with 10 times that amount |
| 218 |
of traffic.</p> |
| 219 |
|
| 220 |
<p>The SQLite website (<a href="https://sqlite.org/">https://sqlite.org/</a>) uses SQLite itself, |
| 221 |
of course, and as of this writing (2015) it handles about 400K to 500K |
| 222 |
HTTP requests per day, about 15-20% of which are dynamic pages touching |
| 223 |
the database. Dynamic content uses <a href="np1queryprob.html">about 200 SQL statements per webpage</a>. |
| 224 |
This setup runs on a single VM that shares a physical server with 23 others |
| 225 |
and yet still keeps the load average below 0.1 most of the time. |
| 226 |
|
| 227 |
</p><p>See also: <a href="https://news.ycombinator.com/item?id=33975635">Hacker News discussion from 2022-12-13</a>. |
| 228 |
</p></li> |
| 229 |
|
| 230 |
<li><p><b>Data analysis</b></p> |
| 231 |
|
| 232 |
<p> |
| 233 |
People who understand SQL can employ the |
| 234 |
<a href="cli.html">sqlite3 command-line shell</a> (or various third-party |
| 235 |
SQLite access programs) to analyze large |
| 236 |
datasets. Raw data can be imported from CSV files, then that |
| 237 |
data can be sliced and diced to generate a myriad of summary |
| 238 |
reports. More complex analysis can be done using simple scripts written |
| 239 |
in Tcl or Python (both of which come with SQLite built-in) or in R or |
| 240 |
other languages using readily available adaptors. |
| 241 |
Possible uses include website log analysis, sports |
| 242 |
statistics analysis, compilation of programming metrics, and |
| 243 |
analysis of experimental results. Many bioinformatics researchers |
| 244 |
use SQLite in this way. |
| 245 |
</p> |
| 246 |
|
| 247 |
<p> |
| 248 |
The same thing can be done with an enterprise client/server |
| 249 |
database, of course. The advantage of SQLite is |
| 250 |
that it is easier to install and use and the resulting database |
| 251 |
is a single file that can be written to a USB memory stick |
| 252 |
or emailed to a colleague. |
| 253 |
</p> |
| 254 |
</li> |
| 255 |
|
| 256 |
<li><p><b>Cache for enterprise data</b></p> |
| 257 |
|
| 258 |
<p> |
| 259 |
Many applications use SQLite as a cache of relevant content from |
| 260 |
an enterprise RDBMS. |
| 261 |
This reduces latency, since most queries now occur against the local |
| 262 |
cache and avoid a network round-trip. It also reduces the load |
| 263 |
on the network and on the central database server. And in many cases, |
| 264 |
it means that the client-side application can continue operating during |
| 265 |
network outages. |
| 266 |
</p> |
| 267 |
</li> |
| 268 |
|
| 269 |
<a name="serversidedb"></a> |
| 270 |
|
| 271 |
<li><p><b>Server-side database</b></p> |
| 272 |
|
| 273 |
<p> |
| 274 |
Systems designers |
| 275 |
report success using SQLite as a data store on server applications |
| 276 |
running in the datacenter, or in other words, using SQLite as the underlying |
| 277 |
storage engine for an application-specific database server.</p> |
| 278 |
|
| 279 |
<p>With this pattern, the overall system is still client/server: |
| 280 |
clients send requests to the server and get back replies over the network. |
| 281 |
But instead of sending generic SQL and getting back raw table content, |
| 282 |
the client requests and server responses are high-level and |
| 283 |
application-specific. |
| 284 |
The server translates requests into multiple SQL queries, gathers the |
| 285 |
results, does post-processing, filtering, and analysis, then constructs |
| 286 |
a high-level reply containing only the essential information.</p> |
| 287 |
|
| 288 |
<p>Developers report that SQLite is often faster than a client/server |
| 289 |
SQL database engine in this scenario. |
| 290 |
Database requests are serialized by the server, so concurrency is not |
| 291 |
an issue. Concurrency is also improved by "database sharding": |
| 292 |
using separate database files for different subdomains. For |
| 293 |
example, the server might have a separate SQLite database for each |
| 294 |
user, so that the server can handle hundreds or thousands of simultaneous |
| 295 |
connections, but each SQLite database is only used by one connection.</p> |
| 296 |
</li> |
| 297 |
|
| 298 |
<a name="wireproto"></a> |
| 299 |
|
| 300 |
<li><p><b>Data transfer format</b></p><p> |
| 301 |
|
| 302 |
</p><p>Because an SQLite database is a single compact file in a |
| 303 |
<a href="fileformat2.html">well-defined cross-platform format</a>, it is often used |
| 304 |
as a container for transferring content from one system to another. |
| 305 |
The sender gathers content into an SQLite database file, transfers |
| 306 |
that one file to the receiver, then the receiver uses SQL to extract |
| 307 |
the content as needed. |
| 308 |
|
| 309 |
</p><p>An SQLite database facilitates data transfer between systems even |
| 310 |
when the endpoints have different word sizes and/or byte orders. |
| 311 |
The data can be a complex mix of large binary blobs, text, and small |
| 312 |
numeric or boolean values. The data format can be easily extended |
| 313 |
by adding new tables and/or columns, without breaking legacy receivers. |
| 314 |
The SQL query language means that receivers are not required to parse |
| 315 |
the entire transfer all at once, but can instead query the |
| 316 |
received content as needed. The data format is "transparent" in the |
| 317 |
sense that it is easily decoded for human viewing using |
| 318 |
a variety of universally available, open-source tools, from multiple |
| 319 |
vendors. |
| 320 |
</p></li> |
| 321 |
|
| 322 |
<a name="container"></a> |
| 323 |
|
| 324 |
<li><p><b>File archive and/or data container</b></p> |
| 325 |
|
| 326 |
<p> |
| 327 |
The <a href="sqlar.html">SQLite Archive</a> idea shows how |
| 328 |
SQLite can be used as a substitute for ZIP archives or Tarballs. |
| 329 |
An archive of files stored in SQLite is only very slightly larger, and |
| 330 |
in some cases actually smaller, than the equivalent ZIP archive. |
| 331 |
And an SQLite archive features incremental and atomic updating |
| 332 |
and the ability to store much richer metadata. |
| 333 |
</p> |
| 334 |
|
| 335 |
<p><a href="https://www.fossil-scm.org/">Fossil</a> version 2.5 and later offers |
| 336 |
<a href="sqlar.html">SQLite Archive files</a> as a download format, in addition |
| 337 |
to traditional tarball and ZIP archive. |
| 338 |
The <a href="cli.html">sqlite3.exe command-line shell</a> version 3.22.0 and later will create, |
| 339 |
list, or unpack an SQL archive using the |
| 340 |
<a href="cli.html#sqlar">.archive command</a>.</p> |
| 341 |
|
| 342 |
<p> |
| 343 |
SQLite is a good solution for any situation that requires bundling |
| 344 |
diverse content into a self-contained and self-describing package |
| 345 |
for shipment across a network. |
| 346 |
Content is encoded in a |
| 347 |
<a href="fileformat2.html">well-defined, cross-platform, and stable file format</a>. |
| 348 |
The encoding is efficient, and receivers can extract small subsets |
| 349 |
of the content without having to read and parse the entire file. |
| 350 |
</p> |
| 351 |
|
| 352 |
<p>SQL archives are useful as the distribution format for software |
| 353 |
or content updates that are broadcast to many clients. Variations |
| 354 |
on this idea are used, for example, to transmit TV programming guides |
| 355 |
to set-top boxes and to send over-the-air updates to vehicle navigation |
| 356 |
systems.</p> |
| 357 |
</li> |
| 358 |
|
| 359 |
<li><p><b>Replacement for <i>ad hoc</i> disk files</b></p> |
| 360 |
|
| 361 |
<p>Many programs use |
| 362 |
<a href="http://man.he.net/man3/fopen">fopen()</a>, |
| 363 |
<a href="http://man.he.net/man3/fread">fread()</a>, and |
| 364 |
<a href="http://man.he.net/man3/fwrite">fwrite()</a> to create and |
| 365 |
manage files of data in home-grown formats. SQLite works |
| 366 |
particularly well as a |
| 367 |
replacement for these <i>ad hoc</i> data files. |
| 368 |
Contrary to intuition, SQLite can be <a href="fasterthanfs.html">faster than the filesystem</a> |
| 369 |
for reading and writing content to disk. |
| 370 |
</p></li> |
| 371 |
|
| 372 |
<li><p><b>Internal or temporary databases</b></p> |
| 373 |
|
| 374 |
<p> |
| 375 |
For programs that have a lot of data that must be sifted and sorted |
| 376 |
in diverse ways, it is often easier and quicker to load the data into |
| 377 |
an in-memory SQLite database and use queries with joins and ORDER BY |
| 378 |
clauses to extract the data in the form and order needed rather than |
| 379 |
to try to code the same operations manually. |
| 380 |
Using an SQL database internally in this way also gives the program |
| 381 |
greater flexibility since new columns and indices can be added without |
| 382 |
having to recode every query. |
| 383 |
</p> |
| 384 |
</li> |
| 385 |
|
| 386 |
<li><p><b>Stand-in for an enterprise database during demos or testing</b></p> |
| 387 |
|
| 388 |
<p> |
| 389 |
Client applications typically use a generic database interface that allows |
| 390 |
connections to various SQL database engines. It makes good sense to |
| 391 |
include SQLite in the mix of supported databases and to statically |
| 392 |
link the SQLite engine in with the client. That way the client program |
| 393 |
can be used standalone with an SQLite data file for testing or for |
| 394 |
demonstrations. |
| 395 |
</p> |
| 396 |
</li> |
| 397 |
|
| 398 |
<li><p><b>Education and Training</b></p> |
| 399 |
|
| 400 |
<p> |
| 401 |
Because it is simple to setup and use (installation is trivial: just |
| 402 |
copy the <b>sqlite3</b> or <b>sqlite3.exe</b> executable to the target machine |
| 403 |
and run it) SQLite makes a good database engine for use in teaching SQL. |
| 404 |
Students can easily create as many databases as they like and can |
| 405 |
email databases to the instructor for comments or grading. For more |
| 406 |
advanced students who are interested in studying how an RDBMS is |
| 407 |
implemented, the modular and well-commented and documented SQLite code |
| 408 |
can serve as a good basis. |
| 409 |
</p> |
| 410 |
</li> |
| 411 |
|
| 412 |
<li><p><b>Experimental SQL language extensions</b></p> |
| 413 |
|
| 414 |
<p>The simple, modular design of SQLite makes it a good platform for |
| 415 |
prototyping new, experimental database language features or ideas. |
| 416 |
</p> |
| 417 |
</li> |
| 418 |
|
| 419 |
|
| 420 |
</ul> |
| 421 |
|
| 422 |
<h1 id="situations_where_a_client_server_rdbms_may_work_better"><span>2. </span>Situations Where A Client/Server RDBMS May Work Better</h1> |
| 423 |
|
| 424 |
<ul> |
| 425 |
<li><p><b>Client/Server Applications</b></p><p> |
| 426 |
|
| 427 |
</p><p>If there are many client programs sending SQL to the same |
| 428 |
database over a network, then use a client/server database |
| 429 |
engine instead of SQLite. SQLite will work over a network filesystem, |
| 430 |
but because of the latency associated with most network filesystems, |
| 431 |
performance will not be great. Also, file locking logic is buggy in |
| 432 |
many network filesystem implementations (on both Unix and Windows). |
| 433 |
If file locking does not work correctly, |
| 434 |
two or more clients might try to modify the |
| 435 |
same part of the same database at the same time, resulting in |
| 436 |
corruption. Because this problem results from bugs in |
| 437 |
the underlying filesystem implementation, there is nothing SQLite |
| 438 |
can do to prevent it.</p> |
| 439 |
|
| 440 |
<p>A good rule of thumb is to avoid using SQLite |
| 441 |
in situations where the same database will be accessed directly |
| 442 |
(without an intervening application server) and simultaneously |
| 443 |
from many computers over a network.</p> |
| 444 |
</li> |
| 445 |
|
| 446 |
<li><p><b>High-volume Websites</b></p> |
| 447 |
|
| 448 |
<p>SQLite will normally work fine as the database backend to a website. |
| 449 |
But if the website is write-intensive or is so busy that it requires |
| 450 |
multiple servers, then consider using an enterprise-class client/server |
| 451 |
database engine instead of SQLite.</p> |
| 452 |
</li> |
| 453 |
|
| 454 |
<li><p><b>Very large datasets</b></p> |
| 455 |
|
| 456 |
<p>An SQLite database is limited in size to 281 terabytes |
| 457 |
(2<sup><small>48</small></sup> bytes, 256 tibibytes). |
| 458 |
And even if it could handle larger databases, SQLite stores the entire |
| 459 |
database in a single disk file and many filesystems limit the maximum |
| 460 |
size of files to something less than this. So if you are contemplating |
| 461 |
databases of this magnitude, you would do well to consider using a |
| 462 |
client/server database engine that spreads its content across multiple |
| 463 |
disk files, and perhaps across multiple volumes. |
| 464 |
</p> |
| 465 |
</li> |
| 466 |
|
| 467 |
<li><p><b>High Concurrency</b></p> |
| 468 |
|
| 469 |
<p> |
| 470 |
SQLite supports an unlimited number of simultaneous readers, but it |
| 471 |
will only allow one writer at any instant in time. |
| 472 |
For many situations, this is not a problem. Writers queue up. Each application |
| 473 |
does its database work quickly and moves on, and no lock lasts for more |
| 474 |
than a few dozen milliseconds. But there are some applications that require |
| 475 |
more concurrency, and those applications may need to seek a different |
| 476 |
solution. |
| 477 |
</p> |
| 478 |
</li> |
| 479 |
|
| 480 |
</ul> |
| 481 |
|
| 482 |
<a name="dbcklst"></a> |
| 483 |
|
| 484 |
<h1 id="checklist_for_choosing_the_right_database_engine"><span>3. </span>Checklist For Choosing The Right Database Engine</h1> |
| 485 |
|
| 486 |
<ol> |
| 487 |
<li><p><b>Is the data separated from the application by a network? |
| 488 |
→ choose client/server</b></p> |
| 489 |
|
| 490 |
<p>Relational database engines act as bandwidth-reducing data filters. |
| 491 |
So it is best to keep the database engine and the data on |
| 492 |
the same physical device so that the high-bandwidth engine-to-disk |
| 493 |
link does not have to traverse the network, only the lower-bandwidth |
| 494 |
application-to-engine link. |
| 495 |
|
| 496 |
</p><p>But SQLite is built into the application. So if the data is on a |
| 497 |
separate device from the application, it is required that the higher |
| 498 |
bandwidth engine-to-disk link be across the network. This works, but |
| 499 |
it is suboptimal. Hence, it is usually better to select a client/server |
| 500 |
database engine when the data is on a separate device from the |
| 501 |
application. |
| 502 |
|
| 503 |
</p><p><em>Nota Bene:</em> |
| 504 |
In this rule, "application" means the code that issues SQL statements. |
| 505 |
If the "application" is an <a href="whentouse.html#serversidedb">application server</a> and |
| 506 |
if the content resides on the same physical machine as the application server, |
| 507 |
then SQLite might still be appropriate even though the end user is |
| 508 |
another network hop away.</p> |
| 509 |
</li> |
| 510 |
|
| 511 |
<li><p><b>Many concurrent writers? → choose client/server</b></p> |
| 512 |
|
| 513 |
<p>If many threads and/or processes need to write the |
| 514 |
database at the same instant (and they cannot queue up and take turns) |
| 515 |
then it is best to select a database engine that supports that |
| 516 |
capability, which always means a client/server database engine. |
| 517 |
|
| 518 |
</p><p>SQLite only supports one writer at a time per database file. |
| 519 |
But in most cases, a write transaction only takes milliseconds and |
| 520 |
so multiple writers can simply take turns. SQLite will handle |
| 521 |
more write concurrency than many people suspect. Nevertheless, |
| 522 |
client/server database systems, because they have a long-running |
| 523 |
server process at hand to coordinate access, can usually handle |
| 524 |
far more write concurrency than SQLite ever will. |
| 525 |
</p></li> |
| 526 |
|
| 527 |
<li><p><b>Big data? → choose client/server</b></p> |
| 528 |
|
| 529 |
<p>If your data will grow to a size that you are uncomfortable |
| 530 |
or unable to fit into a single disk file, then you should select |
| 531 |
a solution other than SQLite. SQLite supports databases up to |
| 532 |
281 terabytes in size, assuming you can find a disk drive and filesystem |
| 533 |
that will support 281-terabyte files. Even so, when the size of the |
| 534 |
content looks like it might creep into the terabyte range, it would |
| 535 |
be good to consider a centralized client/server database. |
| 536 |
</p></li> |
| 537 |
|
| 538 |
<li><p><b>Otherwise → choose SQLite!</b></p> |
| 539 |
|
| 540 |
<p>For device-local storage with low writer concurrency and less than a |
| 541 |
terabyte of content, SQLite is almost always a better solution. SQLite |
| 542 |
is fast and reliable and it requires no configuration or maintenance. |
| 543 |
It keeps things simple. SQLite "just works". |
| 544 |
</p></li> |
| 545 |
</ol> |
| 546 |
<p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p> |
| 547 |
|