5

I'm building an app where I need access to quite large reports about different publicly accessible URLs, JSON objects about 200kb to 500kB in size. Each user would generate hundreds of these reports per month and I would need to store them for some months at least.

First I thought I'll just write them into to my Postgres DB, but soon I realized this might not be feasible.

Here's what I'm thinking:

  1. First, generate the report
  2. Upload it to an S3 compatible object storage service, think I can write the files straight from memory with a PUT request
  3. Make a reports column in the Postgres DB with fields: timeStamp, id, partialReport, and relational field to the URL the report belongs to
  4. Write the most vital bits of the report to the partialReport field in reports column for quick access
  5. Have a button in my app: "View the full report", and only then go to the storage
  6. LRU-cache them

Or should I:

  1. Simply use MongoDB, people say it's really good at handling JSON
  2. Use Redis between the storage and my app, Redis would replenish from the storage when new reports are added (some sort of push thing), and my app would only ever query the reports from Redis

What do you think?

Thanks!

2
  • 1
    What issues do you foresee with Postgres? Also, which advantages/disadvantages do you see with S3? It's definitely easier to add a JSON column to an existing Postgres DB than to manage an additional object storage engine.
    – amon
    Apr 8, 2023 at 15:09
  • I'm not sure. I feel like the database will become absolutely massive very quickly and big JSON queries are slow.
    – any_h
    Apr 8, 2023 at 20:12

1 Answer 1

4

You have an interesting scenario with lots of solution that might be appropriate.

You raise a good point that the amount of data might be an issue. As a worst case estimate, you might have 0.5MB per document × 500 documents per user per month × 6 months retention period, equalling up to 1.5GB per user. Even a multi-terabyte database is absolutely not an issue, but other technologies than Postgres might have an easier time distributing that across multiple replicas (if needed).

For example, MongoDB would make it easier to maintain a cluster of database instances, and has more convenient JSON queries, but other than that would have no advantages over Postgres here. Both Postgres and MongoDB support a more compact binary JSON representation. (In Postgres, declare the column as jsonb rather than json).

If you want to move the report contents to a different database, then you'll face the issue of keeping the two data sources in sync. It's a good idea to ensure that those external documents are immutable, perhaps also content-addressable. The difficult part is deleting the external documents whenever the record in the main database is deleted. In practice, this might require scheduled scans to find unreferenced documents. That's not necessarily a problem, but it's a fairly expensive operation (also potentially expensive in terms of money if you use DBaaS).

Introducing a cache can be a good idea if your data is easily cacheable and if this actually saves you from performing expensive operations. Caching the JSON documents sounds dubious though – they are comparatively large (meaning fewer entries in your cache), and not terribly expensive to retrieve (no complicated queries with scans or joins involved). Especially for Redis, keep in mind that it is memory-limited. It is infeasible to keep all your data in this cache.

What I would recommend:

  • do a cost estimation for architectures like Postgres-only and split Postgres/S3 (or some other bulk key-value store). Take into account that binary JSON in Postgres will be more compact than a JSON document in object storage. Take into account that a split solution will take more development effort.

  • consider starting with a Postgres-only approach, because keeping everything in one DB is going to be simpler. Once the storage overhead becomes problematic, you can migrate the JSON documents into a different storage solution and replace the JSON column with a column of URLs.

  • depending on your access patterns and relevant costs, introducing a cache may be useful – but that's not required for an initial working solution.

5
  • I'm using Prisma, it stores JSONB right out the box. Keeping the DB and the storage in sync will be little challenging. Don't think I can use the DB ID to link them, because I need it before I write it to the DB. And the method responsible for deleting data from the DB should also delete the corresponding data in the storage: if either one of the operations fail, neither of the entries should be deleted. This will be interesting to implement, since if the storage deletion operation succeeds and the following DB operation fails, then I need to rewrite the data back to the storage.
    – any_h
    Apr 10, 2023 at 8:25
  • @any_h The likelihood of such failures is low, but it may be easier to design your system from the start in an eventually-consistent manner. This means failing gracefully if you have a DB record but can't load the JSON, and running regular scans to find JSON documents that aren't referenced from your DB. You cannot reliably run a transaction that involves two different database technologies. Regarding IDs, consider not using sequential IDs but either a content-addressable scheme (e.g. using a hash of immutable data), or using UUIDs as identifiers.
    – amon
    Apr 10, 2023 at 9:51
  • @any_h Is there a maximum time you need to keep a document or could a user retain a single report indefinitely?
    – JimmyJames
    Apr 10, 2023 at 15:09
  • @JimmyJames Yes there's a max time, don't know what that is yet, probably from a month to three months.
    – any_h
    Apr 11, 2023 at 12:56
  • 2
    @any_h One advantage of S3 might be that you can setup rules to automatically delete items based on age. I'm not sure how important it is for the reports to be deleted immediately upon request but from an application perspective, they could be irretrievable once the link was deleted from the DB. You could also make an effort to remove them immediately and let orphans age off.
    – JimmyJames
    Apr 11, 2023 at 17:52

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.