What is it that makes a database an OLAP database rather than a generic relational database?
Expressed otherwise: why would I choose DuckDB over SQLite, or SQLite over DuckDB?
Expressed otherwise: why would I choose DuckDB over SQLite, or SQLite over DuckDB?
SQLite/MySQL/Postgres/MSSQL etc are all OLTP databases whose primary operation is based around operations on single (or few) rows.
OLAP databases like ClickHouse/DuckDB, Monet, Redshift, etc are optimised for operating on columns and performing operations like bulk aggregations, group-bys, pivots, etc on a large subsets or whole tables.
If I was recording user purchases/transactions: SQLite.
If I was aggregating and analysing a batch of data on a machine: DuckDB.
I read an interesting engineering blog from Spotify(?) where in their data pipeline instead of passing around CSV’s or rows of JSON, passed around SQLite databases: DuckDB would probably be a good fit there.
The shortest useful description I can give of what constitutes an analytical workloads is that it is table-scan-heavy, and often concerned with a small subset of columns in a table.
I could add more indices to MSSQL and do all sorts of query optimisations, but out of the box the OLAP wins hands down for that workload.