4 ms·

I have been looking for exactly this for a while now: “For my usecase, I wanted a way to: Stash my data in its "original" JSON form Explore it later & build w

by rjeli 5y ago

I have been looking for exactly this for a while now: “For my usecase, I wanted a way to: Stash my data in its "original" JSON form Explore it later & build whatever views I want Keep costs & infrastructure complexity low Self-host it / own my data” However, I think compression is a key part. Large scraped json datasets can be compressed by a few orders of magnitude, which is the difference between scp’ing a db up to aws in minutes vs days. SQLite has an official proprietary extension to DEFLATE backing pages, but that’s not easy to buy/distribute for hobby projects. I’ve tried row compression with zstd dictionaries and it works well, but then you lose native indexing. Mongo wiredtiger does pretty much what I want, it’s just not a neat flat file :/

adamlouis 5y ago
Nice! Glad it resonated. Never quite sure how a project like this will land. Thanks for sharing those - will check them out. Interested to see what happens as the size of the dataset grows. I have not looked deeply, but Typesense[1] seems like another interesting project. Similar to ES or Algolia, easy to self-host, & with a seemingly efficient memory & disk footprint. [1]https://github.com/typesense/typesense https://github.com/typesense/typesense
NicoJuicy 5y ago
Except the flat file part. What was lacking with postgress? It has decent json support and v8 javascript build-in.
- rjeli 5y ago
  No compression of json docs. Jsonb is even bigger than json text column. There’s an extension out there but it requires manual compression passes and doesn’t allow indexing. Might as well use SQLite
  - mnahkies 5y ago
    I think it would probably just make sense to run postgres on a compressed filesystem rather than use an extension. Looks like citus had good results: https://www.citusdata.com/blog/2013/04/30/zfs-compression/ https://www.citusdata.com/blog/2013/04/30/zfs-compression/
  - NicoJuicy 5y ago
    Thanks for the informative answer!

by rjeli 5y ago

adamlouis 5y ago

Nice! Glad it resonated. Never quite sure how a project like this will land. Thanks for sharing those - will check them out. Interested to see what happens as the size of the dataset grows. I have not looked deeply, but Typesense[1] seems like another interesting project. Similar to ES or Algolia, easy to self-host, & with a seemingly efficient memory & disk footprint. [1]https://github.com/typesense/typesense https://github.com/typesense/typesense

NicoJuicy 5y ago

Except the flat file part. What was lacking with postgress? It has decent json support and v8 javascript build-in.

rjeli 5y ago
No compression of json docs. Jsonb is even bigger than json text column. There’s an extension out there but it requires manual compression passes and doesn’t allow indexing. Might as well use SQLite
- mnahkies 5y ago
  I think it would probably just make sense to run postgres on a compressed filesystem rather than use an extension. Looks like citus had good results: https://www.citusdata.com/blog/2013/04/30/zfs-compression/ https://www.citusdata.com/blog/2013/04/30/zfs-compression/
- NicoJuicy 5y ago
  Thanks for the informative answer!