SQLite is a row store, which is best for OLTP (point queries, inserting/updating/deleting one or a few rows at a time), while DuckDB is a column store, which means the data layout has values from the same column stored contiguously, making aggregation queries (GROUP BY) perform much better.
By the way, here's a YT video of a talk given by one of the DuckDB implementers about why they made it, what it's for, and how it works: https://www.youtube.com/watch?v=PFUZlNQIndo
Not SQLite itself. One of the DB Browser for SQLite dev's is an Aussie though. :)
And check SQLiteStudio for Windows, is nice too.
PowerPivot is extremely amazing.
Loading hundreds of millions of rows into it takes a while, but given a commensurate amount of RAM and a reasonable data model (single table or star schema), performing aggregations with a pivot table is pretty snappy.
Here's a video showing rapid pivots of 100m row dataset
If it helps, our development builds for quite a few months have:
https://nightlies.sqlitebrowser.org/latest/
Our recent 3.12.0-alpha1 release does as well:
https://nightlies.sqlitebrowser.org/latest/
The first beta release for 3.12.0 should be out next week. There's not much change in it though (mostly language string changes), as the alpha1 has turned out to be really stable. :)
Still very rough, but usable.
How far off do you reckon it is from being "production" ready?
Asking because we've been adding useful SQLite extensions as optional extras in our (sqlitebrowser.org) installer. Can add yours too, if you reckon the code is reasonably cross platform and shouldn't cause (many) weird issues. :)
There's also a long tradition of "faking" decimals with integers. I have always found that to be extremely tedious and error prone.
[0]https://sqlitebrowser.org/