4 ms·

This kind of realization that "yes, it probably will" has recently inspired me to hand-build various database engines wherein the entire working set lives in me

This kind of realization that "yes, it probably will" has recently inspired me to hand-build various database engines wherein the entire working set lives in memory. I do realize others have worked on this idea too, but I always wanted to play with it myself. My most recent prototypes use a hybrid mechanism that dramatically increases the supported working set size. Any property larger than a specific cutoff would be a separate read operation to the durable log. For these properties, only the log's 64-bit offset is stored in memory. There is an alternative heuristic that allows for the developer to add attributes which signify if properties are to be maintained in-memory or permitted to be secondary lookups. As a consequence, that 2TB worth of ram can properly track hundreds or even thousands of TB worth of effective data. If you are using modern NVMe storage, those reads to disk are stupid-fast in the worst case. There's still a really good chance you will get a hit in the IO cache if you application isn't ridiculous and has some predictable access patterns.

saltcured 4y ago
I don't mean to discourage personal exploration in any way, but when doing this sort of thing it can also be illuminating to consider the null hypothesis... what happens if you let the conventional software use a similarly enlarged RAM budget or fast storage? SQLite or PostgreSQL can be given some configuration/hints to be more aggressive about using RAM while still having their built-in capability to spill to storage rather than hit a hard limit. Or on Linux (at least), just allowing the OS page cache to sprawl over a large RAM system may make the IO so fast that the database doesn't need to worry about special RAM usage. For PostgreSQL, this can just be hints to the optimizer to adjust the cost model and consider random access to be cheaper when comparing possible query plans. Once you do some sanity check benchmarks of different systems like that, you might find different bottlenecks than expected, and this might highlight new performance optimization quests you hadn't even considered before. :-)
- bob1029 4y ago
  > what happens if you let the conventional software use a similarly enlarged RAM budget or fast storage? Oh I absolutely have gone down this road as well. The biggest thing for me is taking advantage of the other benefits you can get with in-memory working sets, such as arbitrary pointer machine representations. When working with a traditional SQL engine (even one tuned for memory-only operation), there are many rules you have to play by or things will not go well.
- lmwnshn 4y ago
  To add to this thread, some people have done this exploration before, see slides 11 and 20 of [0] or Figure 1 of [1]. Could you just throw more RAM at your problems in a disk-backed system? In practice, probably. But there are distinct advantages to designing upfront for an in-memory scenario. [0] https://15721.courses.cs.cmu.edu/spring2020/slides/02-inmemory.pdf https://15721.courses.cs.cmu.edu/spring2020/slides/02-inmemo... [1] https://15721.courses.cs.cmu.edu/spring2020/papers/02-inmemory/hstore-lookingglass.pdf https://15721.courses.cs.cmu.edu/spring2020/papers/02-inmemo...

saltcured 4y ago

I don't mean to discourage personal exploration in any way, but when doing this sort of thing it can also be illuminating to consider the null hypothesis... what happens if you let the conventional software use a similarly enlarged RAM budget or fast storage? SQLite or PostgreSQL can be given some configuration/hints to be more aggressive about using RAM while still having their built-in capability to spill to storage rather than hit a hard limit. Or on Linux (at least), just allowing the OS page cache to sprawl over a large RAM system may make the IO so fast that the database doesn't need to worry about special RAM usage. For PostgreSQL, this can just be hints to the optimizer to adjust the cost model and consider random access to be cheaper when comparing possible query plans. Once you do some sanity check benchmarks of different systems like that, you might find different bottlenecks than expected, and this might highlight new performance optimization quests you hadn't even considered before. :-)

bob1029 4y ago
> what happens if you let the conventional software use a similarly enlarged RAM budget or fast storage? Oh I absolutely have gone down this road as well. The biggest thing for me is taking advantage of the other benefits you can get with in-memory working sets, such as arbitrary pointer machine representations. When working with a traditional SQL engine (even one tuned for memory-only operation), there are many rules you have to play by or things will not go well.
lmwnshn 4y ago
To add to this thread, some people have done this exploration before, see slides 11 and 20 of [0] or Figure 1 of [1]. Could you just throw more RAM at your problems in a disk-backed system? In practice, probably. But there are distinct advantages to designing upfront for an in-memory scenario. [0] https://15721.courses.cs.cmu.edu/spring2020/slides/02-inmemory.pdf https://15721.courses.cs.cmu.edu/spring2020/slides/02-inmemo... [1] https://15721.courses.cs.cmu.edu/spring2020/papers/02-inmemory/hstore-lookingglass.pdf https://15721.courses.cs.cmu.edu/spring2020/papers/02-inmemo...