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
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.