Pros
- Compact ( 1 / 10th of Postgres)
- Fast reads
- Great for memory-constrained environments
Cons
No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity. One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml.
WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems.
Date and time types don’t really exist. There are functions to assemble your own, but it does require some thought. The ODBC driver for SQLite does have options to “emulate” this.
Length specifications on a column are ignored. CHAR(2) will allow the insertion of a blob. I think that check constraints could be used to enforce this.
Type affinity means that any data type can be inserted into columns declared as any other data type. Rigid type enforcement can be done, but it is not the default.
Details
https://avi.im/blag/2024/sqlite-facts/
Operations
Upsert
https://antonz.org/sql-upsert/
Performance
https://www.sqlite.org/queryplanner.html https://www.sqlite.org/eqp.html
To minimize the incurred reads and writes on your database:
Read about the SQLite query planner to understand how queries work. Use SQLite’s EXPLAIN QUERY PLAN statement to understand if a query performs a full table scan, and if it’s using the best available index to minimize reads. Ensure that every query that filters rows is able to use an index to find the relevant rows. Without an index, SQLite performs a table scan to find the rows, which costs one read for each existing row in the table. Add all required indexes at the time a table is created. Adding indexes to tables with existing rows incurs one read for each existing row.
For OLAP workloads
https://avi.im/blag/2024/sqlite-past-present-future/
- Star Schema Benchmark (SSB)
- Optimized joins using Bloom filters