I have been using duckdb files as caches infront of sql server for server side queries. Initially the cache was redis, but duckdb worked much better as the very same query that goes to sql can be send down to duckdb.
do you have more details on how you're doing this? i'd love to implement something like this
seconded.. very interested in this approach as well!
We have a restful server that accepts odata query. We translate that to sql using sqlalchemy (it’s a python stack). The application is a financial risk system with billions of rows. The query usually fetches data from sqlserver, does some manipulation using pandas dataframes, then serve it as either json or csv. We added duckdb as a cache distributed across many files (a request cannot return data from more than one file) then that very same odata query goes into duckdb. Applies the standard select, filter, group by or pivot and return a dataframe. In most cases duckdb was twice faster than sqlserver. Apologies about any bad grammar/spelling errors, typing from tiny phone in bed
You may shave some time replacing pandas with polars.
How? Do you replicate the whole db?
Not the whole db. Only data related to the day itself. A duckdb file is cached generated from a restful url (e.g /api/v1/risk/date/scheme) so that resides in a risk-scheme-date-uuid.duckdb) then a query will find the latest risk-scheme-date-*.duckdb and based on certain filters return a subset of the data. It’s more complex than that of course as the db is opened in read only to allow concurrent reads, a set of tmp tables are created on each read request to represent user rights where we filter returned data based on what the user is allowed to see. Each duckdb file is around 50mb and usually a newer file of matching scheme is written every 10 minutes, then the old ones are purged when they’re older than two hours