When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)]

[Ethan Furman]

At the risk of using actual data, I looked this up at http://www.sqlite.org/whentouse.html:


Checklist For Choosing The Right Database Engine

 * Is the data separated from the application by a network? → choose client/server

    Relational database engines act as a bandwidth-reducing data filter. 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.

    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.

 * Many concurrent writers? → choose client/server

    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.

    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 that 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.

 * Big data? → choose client/server

    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 140 terabytes in size, assuming you can find
a disk drive and filesystem that will support 140-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.

 * Otherwise → choose SQLite!

    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 thing simple.
SQLite "just works".

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: OpenPGP digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20150218/ad81380b/attachment.sig>