3 ms·

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 ver

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.

swasheck 3y ago
do you have more details on how you're doing this? i'd love to implement something like this
- Talusmaximus 3y ago
  seconded.. very interested in this approach as well!
  - meitham 3y ago
    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
    - elmolino89 3y ago
      You may shave some time replacing pandas with polars.
victorbjorklund 3y ago
How? Do you replicate the whole db?
- meitham 3y ago
  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

swasheck 3y ago

do you have more details on how you're doing this? i'd love to implement something like this

Talusmaximus 3y ago
seconded.. very interested in this approach as well!
- meitham 3y ago
  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
  - elmolino89 3y ago
    You may shave some time replacing pandas with polars.

victorbjorklund 3y ago

How? Do you replicate the whole db?

meitham 3y ago
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