This table will always be queried on the transaction_date column as one of the filters criteria. But the querying/search criteria can span from a day to a month worth of transaction date data.
In that case, and based on the numbers you provided, daily partitioning seems a decent solution.
What I mean was, we will definitely need the data for querying purposes by the users, but just to keep the storage space incontrol (and to help the read queries), we were thinking of having the historical partitions compressed. And for that , if any compression strategy should we follow on postgres?
There is no compression strategy, per se. There are ways Postgres internally compresses the data (see "TOAST"), but in general, the table is either there or not. If space is a concern you should examine if you truly need 127 columns, make sure they are arranged correctly (search for 'postgres column tetris'), and move unused and older partitions elsewhere - like a separate cheaper Postgres server, or something offline.
With regards to loading data to the table faster, wondering why you said 'NO' to load the data first and enabling/Creating the Primary key and Foreign key constraint later approach.
For an initial load, this is fine, if you do things very carefully. I'd build the child table indexes post load but still feed things into the main table as an initial tradeoff, but YMMV.
Just looked back and saw this is actually Aurora, not Postgres. Most of the advice on this thread should still apply, but be aware that things are not the same as Postgres, especially at the storage layer. For all the money you are giving them, don't forget to bug them as well.
Cheers,
Greg
