Help in deciding on architecture in fintech.

Hi everyone.

We work at a fintech company and we need to reduce costs associated with closed customer invoices stored in an RDS database in a table.

We need to purge the immutable, read-only data from this table into cold storage, leaving only the mutable data in RDS.

However, the REST API needs to query both the cold and hot data. The cold data has a smaller volume than the hot data.

The initial architectural idea was to copy the cold data to S3 in JSON format using AWS Glue. However, I'm not sure if it's ideal for an API to read JSONs directly from S3.

What do you think? Perhaps using an analytical database for the cold data? The idea is that the storage supports a volume load about 20% lower than the hot storage, and that this percentage will gradually decrease over time.

Thank you.