I’m looking for a data store to back an API, and since I know of no data store that handles it’s data they way I’d like, I’m thinking good ol’ PostgreSQL might be suitable for now.

Only problem is that I have no idea how to implement it (RDMS never really was my strong side).

The are the constraints/limits I’m working with:

  1. Data is written to the data store in identically sized chunks (say 4kb)
  2. Data must be persisted to disk
  3. Each chunk of data must be read at most once by consumers (read, then delete)
  4. Data need not be read in the same order it was written
  5. I’ve got complete control of the process writing the data and the process reading the data
  6. Scaleability - Doesn’t need to scale to google-level, but I’d like for it to be somewhat scaleable.

My biggest concern is how to structure a a table to allow efficient read-once-then-delete. I was thinking of using row locks, but haven’t had much luck looking at the documentation to figure out if it’s feasible.

I was also planning on handling the reading of data in the database instead of in the application using stored procedures. I think it would work, but I can’t wrap my head around the whole locks thing.

So, is this feasible to do using Postgres, and if so, go any pointers as to how you’d achieve it?

  • Does the reader ask for specific out of order chunks, or does the server decide which chunk to deliver? If there is a crash right after a read, does that chunk need to be readable again or should it just be lost?
    – jjanes
    Aug 6, 2020 at 16:16

1 Answer 1


There are some issues worth clarifying on your end. For example, is the 4kb a single 'blob' or a set of entities/rows, and if it's a set of rows, why exactly do you need to batch it up into 4kb chunks. Otherwise it's better to let Postgres handle the 'chunking' for you. On a different note, the best place to start with locks is the select ... for update skip locked query and from there you can build a work-queue/ pub-sub atop Postgres

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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