If you want to keep them in S3, consolidate into sorted parquet files. You get random access to row groups, and only the columns you need are read so it’s very efficient. DuckDB can both build and access these files efficiently. You could compact files hourly/nightly/weekly whatever
Of course you could also use Aurora for a clean scalable Postgres that can survive zone failures for a simpler solution
Of course you could also use Aurora for a clean scalable Postgres that can survive zone failures for a simpler solution