5 ms·

I can't think of a single case where in-memory database is a good option, aside playing w/ SQL. Whatever queries that might be a hashmap/tree/skiplist, etc. wo

by xxs 5y ago

I can't think of a single case where in-memory database is a good option, aside playing w/ SQL. Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.

adrianN 5y ago
If the set of queries you want to run is fixed you can probably beat SQLite with a lot of work, if the queries are dynamic I doubt that you can do much better than SQLite without herculean efforts. A in-memory database is thus a good option if you either don't care too much about the runtime of your queries and you want to save a bunch of development time, or if you don't know enough about your workload to be able to beat a general purpose solution.
- xxs 5y ago
  >you can =probably= beat SQLite with =a lot of= work, Actually, I am beyond certain. When it's all about the memory no database comes even remotely close to a properly picked datastructures + structure/objects layout. If I need transaction log + persistence, databases have a decent application. In more than 20y, I have never had a case: Yay, I can use relation structures in memory b/c I don't know what I am going to do with the data.
_delirium 5y ago
For the case where I want the ability to run one-off analytics queries, having access to the usual set of join/filter/summarize/etc. operations is a lot more convenient and less error-prone than having to manually write them. But dplyr [1] is my go-to rather than SQLite personally for in-memory data sizes. [1] https://dplyr.tidyverse.org/ https://dplyr.tidyverse.org/
tgv 5y ago
For something like redis, but with more structure. But a pretty good use case (IMO) is testing. If you want to do an integration test with an SQL database, and you want to test large numbers, this might be a good fully functional stub to run locally.
- xxs 5y ago
  >For something like redis, Redis is in pretty much the same category. Testing is sort of a valid case, if you are committed to write pure SQL with minimal use of any dialect specifics (but even 'create table' syntax might be different). Running on the real thing is close to no replacement when it comes to databases. Many databases have docker images nowadays, so it aint hard to run them locally. Likely at a point you'd want to optimize the SQL, itself, rendering the tests incompatible.
einpoklum 5y ago
It's very likely it wouldn't. (Decent) analytical DBMSes have a whole lot up their sleeves which just choosing one plain-vanilla data structure for your table doesn't even approach in terms of performance. The benefit may be well upwards of 10x in many real-world scenario. Of course, if you know you get one single query which you know in advance, carefully build a data structure to cater just to that, and you know your data structures beyond the just the basics - then, yes, a DBMS would be overkill. But it still won't be a walk in the park.
faizshah 5y ago
Sqlite provides a full text search module (FTS5) which provides a lot of nice search features. I have used this numerous times to build serverless apis for large static datasets for frontend analytical experiences like data tables without setting up elasticsearch. Thats one use case. Another is they support this closures.c extension which is very nice for rapid queries on tree structured data locally in memory. The JSON1 extension is also nice for rapidly querying/reshaping deeply nested json data. Theres also spellfix1 that can provide fuzzing capabilities. If you need any of these with low latency and in memory its a great choice. Sqlite is great for rapidly building low latency static data serving services for frontend experiences. Something I’m exploring now is combining sqlite with streamlit to rapidly build data exploration UIs. Like how many times have you wanted to quickly add fuzzy matching or full text search to some program? You use fuzzywuzzy but its pretty slow, sqlite provides performant implementations of this stuff thats super simple to set up.
manigandham 5y ago
What do you think a relational database does to provide you the querying flexibility? It builds the same exact data structures, but does it automatically and dynamically, while offering much more usability. Most attempts to query using raw data structures just means you end up rebuilding a (very poor) relational database with none of the features.
deleted 5y ago
[deleted]

by xxs 5y ago

I can't think of a single case where in-memory database is a good option, aside playing w/ SQL. Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.

adrianN 5y ago

If the set of queries you want to run is fixed you can probably beat SQLite with a lot of work, if the queries are dynamic I doubt that you can do much better than SQLite without herculean efforts. A in-memory database is thus a good option if you either don't care too much about the runtime of your queries and you want to save a bunch of development time, or if you don't know enough about your workload to be able to beat a general purpose solution.

xxs 5y ago
>you can =probably= beat SQLite with =a lot of= work, Actually, I am beyond certain. When it's all about the memory no database comes even remotely close to a properly picked datastructures + structure/objects layout. If I need transaction log + persistence, databases have a decent application. In more than 20y, I have never had a case: Yay, I can use relation structures in memory b/c I don't know what I am going to do with the data.

_delirium 5y ago

For the case where I want the ability to run one-off analytics queries, having access to the usual set of join/filter/summarize/etc. operations is a lot more convenient and less error-prone than having to manually write them. But dplyr [1] is my go-to rather than SQLite personally for in-memory data sizes. [1] https://dplyr.tidyverse.org/ https://dplyr.tidyverse.org/

tgv 5y ago

For something like redis, but with more structure. But a pretty good use case (IMO) is testing. If you want to do an integration test with an SQL database, and you want to test large numbers, this might be a good fully functional stub to run locally.

xxs 5y ago
>For something like redis, Redis is in pretty much the same category. Testing is sort of a valid case, if you are committed to write pure SQL with minimal use of any dialect specifics (but even 'create table' syntax might be different). Running on the real thing is close to no replacement when it comes to databases. Many databases have docker images nowadays, so it aint hard to run them locally. Likely at a point you'd want to optimize the SQL, itself, rendering the tests incompatible.

einpoklum 5y ago

It's very likely it wouldn't. (Decent) analytical DBMSes have a whole lot up their sleeves which just choosing one plain-vanilla data structure for your table doesn't even approach in terms of performance. The benefit may be well upwards of 10x in many real-world scenario. Of course, if you know you get one single query which you know in advance, carefully build a data structure to cater just to that, and you know your data structures beyond the just the basics - then, yes, a DBMS would be overkill. But it still won't be a walk in the park.

faizshah 5y ago

Sqlite provides a full text search module (FTS5) which provides a lot of nice search features. I have used this numerous times to build serverless apis for large static datasets for frontend analytical experiences like data tables without setting up elasticsearch. Thats one use case. Another is they support this closures.c extension which is very nice for rapid queries on tree structured data locally in memory. The JSON1 extension is also nice for rapidly querying/reshaping deeply nested json data. Theres also spellfix1 that can provide fuzzing capabilities. If you need any of these with low latency and in memory its a great choice. Sqlite is great for rapidly building low latency static data serving services for frontend experiences. Something I’m exploring now is combining sqlite with streamlit to rapidly build data exploration UIs. Like how many times have you wanted to quickly add fuzzy matching or full text search to some program? You use fuzzywuzzy but its pretty slow, sqlite provides performant implementations of this stuff thats super simple to set up.

manigandham 5y ago

What do you think a relational database does to provide you the querying flexibility? It builds the same exact data structures, but does it automatically and dynamically, while offering much more usability. Most attempts to query using raw data structures just means you end up rebuilding a (very poor) relational database with none of the features.

deleted 5y ago

[deleted]