diff options
Diffstat (limited to 'www/whentouse.html')
| -rw-r--r-- | www/whentouse.html | 535 |
1 files changed, 535 insertions, 0 deletions
diff --git a/www/whentouse.html b/www/whentouse.html new file mode 100644 index 0000000..12f83f2 --- /dev/null +++ b/www/whentouse.html @@ -0,0 +1,535 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>Appropriate Uses For SQLite</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> + + + +<h1 align="center">Appropriate Uses For SQLite</h1> + +<p> +SQLite is not directly comparable to client/server SQL database engines such +as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to +solve a different problem. +</p> + +<p> +Client/server SQL database engines strive to implement a shared repository +of enterprise data. They emphasize scalability, concurrency, centralization, +and control. +SQLite strives to provide local data storage for +individual applications and devices. SQLite emphasizes economy, +efficiency, reliability, independence, and simplicity. +</p> + + +<p> +SQLite does not compete with client/server databases. +SQLite competes with <a href="http://man.he.net/man3/fopen">fopen()</a>. +</p> + +<h2>Situations Where SQLite Works Well</h2> + +<ul> + +<a name="appfileformat"></a> + +<li><b>Embedded devices and the internet of things</b> + +<p>Because an SQLite database requires no administration, +it works well in devices that must operate without expert human support. +SQLite is a good fit for use in +cellphones, set-top boxes, televisions, game consoles, +cameras, watches, kitchen appliances, thermostats, automobiles, +machine tools, airplanes, remote sensors, drones, medical devices, +and robots: the "internet of things". +</p> + +<p>Client/server database engines are designed to live inside a +lovingly-attended datacenter at the core of the network. +SQLite works there too, but SQLite also thrives at the edge of the network, +fending for itself while providing fast and +reliable data services to applications that would otherwise +have dodgy connectivity. +</li> + +<li><p><b>Application file format</b></p> + +<p> +SQLite is often used as the on-disk file format +for desktop applications such as version control systems, +financial analysis tools, media cataloging and editing suites, CAD +packages, record keeping programs, and so forth. The traditional +File/Open operation calls sqlite3_open() to attach to the database +file. Updates happen automatically as application content is revised +so the File/Save menu option becomes superfluous. The File/Save_As +menu option can be implemented using the <a href="backup.html">backup API</a>. +</p> + +<p>There are many benefits to this approach, including improved +performance, reduced cost and complexity, and +improved reliability. See technical notes +<a href="aff_short.html">"aff_short.html"</a> and +<a href="appfileformat.html">"appfileformat.html"</a> and +<a href="fasterthanfs.html">"fasterthanfs.html"</a> for more information. +This use case is closely related to the +<a href="#wireproto">data transfer format</a> and +<a href="#container">data container</a> use cases below. +</li> + +<a name="website"></a> + +<li><p><b>Websites</b></p> + +<p>SQLite works great as the database engine for most low to +medium traffic websites (which is to say, most websites). +The amount of web traffic that SQLite can handle depends +on how heavily the website uses its database. Generally +speaking, any site that gets fewer than 100K hits/day should work +fine with SQLite. +The 100K hits/day figure is a conservative estimate, not a +hard upper bound. +SQLite has been demonstrated to work with 10 times that amount +of traffic.</p> + +<p>The SQLite website (<a href="https://www.sqlite.org/">https://www.sqlite.org/</a>) uses SQLite itself, +of course, and as of this writing (2015) it handles about 400K to 500K +HTTP requests per day, about 15-20% of which are dynamic pages touching +the database. Dynamic content uses <a href="np1queryprob.html">about 200 SQL statements per webpage</a>. +This setup runs on a single VM that shares a physical server with 23 others +and yet still keeps the load average below 0.1 most of the time. + +<p>See also: <a href="https://news.ycombinator.com/item?id=33975635">Hacker New discussion from 2022-12-13</a>. +</li> + +<li><p><b>Data analysis</b></p> + +<p> +People who understand SQL can employ the +<a href="cli.html">sqlite3 command-line shell</a> (or various third-party +SQLite access programs) to analyze large +datasets. Raw data can be imported from CSV files, then that +data can be sliced and diced to generate a myriad of summary +reports. More complex analysis can be done using simple scripts written +in Tcl or Python (both of which come with SQLite built-in) or in R or +other languages using readily available adaptors. +Possible uses include website log analysis, sports +statistics analysis, compilation of programming metrics, and +analysis of experimental results. Many bioinformatics researchers +use SQLite in this way. +</p> + +<p> +The same thing can be done with an enterprise client/server +database, of course. The advantage of SQLite is +that it is easier to install and use and the resulting database +is a single file that can be written to a USB memory stick +or emailed to a colleague. +</p> +</li> + +<li><p><b>Cache for enterprise data</b></p> + +<p> +Many applications use SQLite as a cache of relevant content from +an enterprise RDBMS. +This reduces latency, since most queries now occur against the local +cache and avoid a network round-trip. It also reduces the load +on the network and on the central database server. And in many cases, +it means that the client-side application can continue operating during +network outages. +</p> +</li> + +<a name="serversidedb"></a> + +<li><p><b>Server-side database</b></p> + +<p> +Systems designers +report success using SQLite as a data store on server applications +running in the datacenter, or in other words, using SQLite as the underlying +storage engine for an application-specific database server.</p> + +<p>With this pattern, the overall system is still client/server: +clients send requests to the server and get back replies over the network. +But instead of sending generic SQL and getting back raw table content, +the client requests and server responses are high-level and +application-specific. +The server translates requests into multiple SQL queries, gathers the +results, does post-processing, filtering, and analysis, then constructs +a high-level reply containing only the essential information.</p> + +<p>Developers report that SQLite is often faster than a client/server +SQL database engine in this scenario. +Database requests are serialized by the server, so concurrency is not +an issue. Concurrency is also improved by "database sharding": +using separate database files for different subdomains. For +example, the server might have a separate SQLite database for each +user, so that the server can handle hundreds or thousands of simultaneous +connections, but each SQLite database is only used by one connection.</p> +</li> + +<a name="wireproto"></a> + +<li><p><b>Data transfer format</b><p> + +<p>Because an SQLite database is a single compact file in a +<a href="fileformat2.html">well-defined cross-platform format</a>, it is often used +as a container for transferring content from one system to another. +The sender gathers content into an SQLite database file, transfers +that one file to the receiver, then the receiver uses SQL to extract +the content as needed. + +<p>An SQLite database facilitates data transfer between systems even +when the endpoints have different word sizes and/or byte orders. +The data can be a complex mix of large binary blobs, text, and small +numeric or boolean values. The data format can be easily extended +by adding new tables and/or columns, without breaking legacy receivers. +The SQL query language means that receivers are not required to parse +the entire transfer all at once, but can instead query the +received content as needed. The data format is "transparent" in the +sense that it is easily decoded for human viewing using +a variety of universally available, open-source tools, from multiple +vendors. +</li> + +<a name="container"></a> + +<li><p><b>File archive and/or data container</b></p> + +<p> +The <a href="sqlar.html">SQLite Archive</a> idea shows how +SQLite can be used as a substitute for ZIP archives or Tarballs. +An archive of files stored in SQLite is only very slightly larger, and +in some cases actually smaller, than the equivalent ZIP archive. +And an SQLite archive features incremental and atomic updating +and the ability to store much richer metadata. +</p> + +<p><a href="https://www.fossil-scm.org/">Fossil</a> version 2.5 and later offers +<a href="sqlar.html">SQLite Archive files</a> as a download format, in addition +to traditional tarball and ZIP archive. +The <a href="cli.html">sqlite3.exe command-line shell</a> version 3.22.0 and later will create, +list, or unpack an SQL archiving using the +<a href="cli.html#sqlar">.archive command</a>.</p> + +<p> +SQLite is a good solution for any situation that requires bundling +diverse content into a self-contained and self-describing package +for shipment across a network. +Content is encoded in a +<a href="fileformat2.html">well-defined, cross-platform, and stable file format</a>. +The encoding is efficient, and receivers can extract small subsets +of the content without having to read and parse the entire file. +</p> + +<p>SQL archives are useful as the distribution format for software +or content updates that are broadcast to many clients. Variations +on this idea are used, for example, to transmit TV programming guides +to set-top boxes and to send over-the-air updates to vehicle navigation +systems.</p> +</li> + +<li><p><b>Replacement for <i>ad hoc</i> disk files</b></p> + +<p>Many programs use +<a href="http://man.he.net/man3/fopen">fopen()</a>, +<a href="http://man.he.net/man3/fread">fread()</a>, and +<a href="http://man.he.net/man3/fwrite">fwrite()</a> to create and +manage files of data in home-grown formats. SQLite works +particularly well as a +replacement for these <i>ad hoc</i> data files. +Contrary to intuition, SQLite can be <a href="fasterthanfs.html">faster than the filesystem</a> +for reading and writing content to disk. +</li> + +<li><p><b>Internal or temporary databases</b></p> + +<p> +For programs that have a lot of data that must be sifted and sorted +in diverse ways, it is often easier and quicker to load the data into +an in-memory SQLite database and use queries with joins and ORDER BY +clauses to extract the data in the form and order needed rather than +to try to code the same operations manually. +Using an SQL database internally in this way also gives the program +greater flexibility since new columns and indices can be added without +having to recode every query. +</p> +</li> + +<li><p><b>Stand-in for an enterprise database during demos or testing</b></p> + +<p> +Client applications typically use a generic database interface that allows +connections to various SQL database engines. It makes good sense to +include SQLite in the mix of supported databases and to statically +link the SQLite engine in with the client. That way the client program +can be used standalone with an SQLite data file for testing or for +demonstrations. +</p> +</li> + +<li><p><b>Education and Training</b></p> + +<p> +Because it is simple to setup and use (installation is trivial: just +copy the <b>sqlite3</b> or <b>sqlite3.exe</b> executable to the target machine +and run it) SQLite makes a good database engine for use in teaching SQL. +Students can easily create as many databases as they like and can +email databases to the instructor for comments or grading. For more +advanced students who are interested in studying how an RDBMS is +implemented, the modular and well-commented and documented SQLite code +can serve as a good basis. +</p> +</li> + +<li><p><b>Experimental SQL language extensions</b></p> + +<p>The simple, modular design of SQLite makes it a good platform for +prototyping new, experimental database language features or ideas. +</p> +</li> + + +</ul> + +<h2>Situations Where A Client/Server RDBMS May Work Better</h2> + +<ul> +<li><p><b>Client/Server Applications</b><p> + +<p>If there are many client programs sending SQL to the same +database over a network, then use a client/server database +engine instead of SQLite. SQLite will work over a network filesystem, +but because of the latency associated with most network filesystems, +performance will not be great. Also, file locking logic is buggy in +many network filesystem implementations (on both Unix and Windows). +If file locking does not work correctly, +two or more clients might try to modify the +same part of the same database at the same time, resulting in +corruption. Because this problem results from bugs in +the underlying filesystem implementation, there is nothing SQLite +can do to prevent it.</p> + +<p>A good rule of thumb is to avoid using SQLite +in situations where the same database will be accessed directly +(without an intervening application server) and simultaneously +from many computers over a network.</p> +</li> + +<li><p><b>High-volume Websites</b></p> + +<p>SQLite will normally work fine as the database backend to a website. +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>48</small></sup> bytes, 256 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. +</p> +</li> + +<li><p><b>High Concurrency</b></p> + +<p> +SQLite supports an unlimited number of simultaneous readers, but it +will only allow one writer at any instant in time. +For many situations, this is not a problem. Writers queue up. Each application +does its database work quickly and moves on, and no lock lasts for more +than a few dozen milliseconds. But there are some applications that require +more concurrency, and those applications may need to seek a different +solution. +</p> +</li> + +</ul> + +<a name="dbcklst"></a> + +<h2>Checklist For Choosing The Right Database Engine</h2> + +<ol> +<li><p><b>Is the data separated from the application by a network? + → choose client/server</b></p> + +<p>Relational database engines act as bandwidth-reducing data filters. +So it is best to keep the database engine and the data on +the same physical device so that the high-bandwidth engine-to-disk +link does not have to traverse the network, only the lower-bandwidth +application-to-engine link. + +<p>But SQLite is built into the application. So if the data is on a +separate device from the application, it is required that the higher +bandwidth engine-to-disk link be across the network. This works, but +it is suboptimal. Hence, it is usually better to select a client/server +database engine when the data is on a separate device from the +application. + +<p><em>Nota Bene:</em> +In this rule, "application" means the code that issues SQL statements. +If the "application" is an <a href="whentouse.html#serversidedb">application server</a> and +if the content resides on the same physical machine as the application server, +then SQLite might still be appropriate even though the end user is +another network hop away.</p> +</li> + +<li><p><b>Many concurrent writers? → choose client/server</b></p> + +<p>If many threads and/or processes need to write the +database at the same instant (and they cannot queue up and take turns) +then it is best to select a database engine that supports that +capability, which always means a client/server database engine. + +<p>SQLite only supports one writer at a time per database file. +But in most cases, a write transaction only takes milliseconds and +so multiple writers can simply take turns. SQLite will handle +more write concurrency than many people suspect. Nevertheless, +client/server database systems, because they have a long-running +server process at hand to coordinate access, can usually handle +far more write concurrency than SQLite ever will. +</li> + +<li><p><b>Big data? → 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 → 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> + + |