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.
Why naive? That's the experience of all of us with SQLite.
How do you make sure you don't lose data in a SQLite DB?
I deal with massive scale all day every day for work (kuberntes/openshift/public clouds) so I find the simplicity amazingly refreshing.
I might also say the performance of my "single box" websites is also amazingly good. I've had even developer friends ask me how I get response times so fast, and they are often surprised when I tell them.
I do regular file backups and have a cron job restarting the web apps each 20 minutes, it’s enough for now. I would love to have a more solid architecture, but it matches the traffic volume and it’s good enough..
I've never thrown more than 20GB of data at it, but it always handles things admirably, and it often (to me) makes the right calls about the level of complexity required (accepts column type defs but really just doesnt care if it maps to string.)
I'm a front-end dev that can wing backend from time to time, and I use SQLite as much as possible. On multiple projects now I've run into complications due to complexity or environments, and adding a simplified local development backend with sqlite kept down time to a minimum.
SQLite is awesome.
It's basic but enough to start.
It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited!