I am building a three-tier architecture application that receives compressed (zlib) text records from a data store, decompresses the records into structs, and then serves the records to clients over the network, which run SQL-style queries on the records.

The issue I'm running into is I need this application to be able to serve potentially hundreds of gigabytes of uncompressed text records, far too much to be able to store in memory.

The tricky part here is that these records are nearly completely updated every hour, so my solution needs to be able to fetch data quickly enough so that hourly downtime does not become a problem.

Previous versions of the server simply read from the data store files directly, which led to reliability problems and race conditions with the data store itself. Now, I've reworked the server to receive transmissions from the data store, but this comes with the issue of where to store all that data while it's being served.

What are some good techniques for minimizing memory usage on the server while also keeping query execution time relatively low?

  • 3
    This sounds like database territory. Sep 29 '20 at 15:11
  • 1
    Serving uncompressed text doesn't mean you need to store it uncompressed in memory or on disk. How are you doing updates? Everything gets replaced and starting at some point you only serve new data, or do you serve mixed versions?
    – D. Jurcau
    Sep 29 '20 at 15:50
  • @D. Jurcau The idea is that old data is served up until the update is complete, after which point only new data is server.
    – A. Vance
    Sep 29 '20 at 16:00

This is what database software does. And it's quite good at it!

Install your favourite RDBMS of choice - MariaDB, PostgreSQL, or whatever. Create your tables and indices. Write code to load the data in. Query it using SQL.

Indices are very important. Indices are how RDBMSes find the right piece of data on disk without having to look through all the data. Set up indices based on your query patterns.

If you want to serve the old data while the new data is being loaded, you could add a "batch number" column. Instead of searching for records where foo="bar", you could search for records where batch=1234 and foo="bar". You load new records with batch 1235. When that's done, you start searching for batch 1235 instead of 1234 and you delete the records with batch 1234. Repeat ad nauseum. Or you could use two sets of tables and just drop the old tables. (The batch number is probably considered a better design but I have a suspicion that it's much faster to just drop an entire table)

You might also be able to compress the parts of the records that you don't need to index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.