Question on overall design

Overall the approach of using Aurora for a highly OLTP system is the right approach. You can leverage your AWS account team who can bring in additional resources as needed from within AWS and also professional services like certified partners if needed. Your AWS team can work with you to come up with an initial design for your overall architecture, and subsequently can conduct technical deep dive via Workshops and Immersion Days so you gain a better understanding of particular AWS services. Also, a PoC can go a long way to validate the capabilities of a service using you own data OR even a pilot/prototype where you pick an end-to-end use case and not only design but also build it out.

As far as historical data goes, I suggest you leverage the S3 capabilities for data archival and do not narrow down to a singular service like Snowflake just yet. Having data on S3 in open formats, be it file formats like Parquet or table formats like Iceberg, gives you the freedom to choose any compute of your choice for querying the data. You can choose Athena, EMR, Redshift or even partner services like Snowflake - pick whichever service meets your price-performance needs and feel free to swap one out for another if required.

You can also leverage public resources to familiarize yourself with strategies, guides, and patterns to help accelerate your cloud migration, modernization, and optimization projects

1. AWS Prescriptive Guidance (https://aws.amazon.com/prescriptive-guidance/)
2. Large migrations to the AWS Cloud (https://aws.amazon.com/prescriptive-guidance/large-migrations)
3. Migration tools (https://aws.amazon.com/prescriptive-guidance/migration-tools/)

Also want to understand, as we used to see the data dictionary views (called AWR views) in Oracle to see the current and historical performance statistics like CPU, IO , Memory usage, object level contentions etc. in the oracle database. Do we have such exposure to the views available (apart from performance insights UI tool) in Aurora postgresql, so as to manually fetch the performance and get some idea of how well the load test goes and what capacity is available or are we saturating it?

How to prove aurora postgresql is going to serve the OLTP requirement here , similar to the current on premise Oracle exadata, for the OLTP use case? We are expecting ~15K TPS write and 2K TPS read and the response for the UI queries the response expected are within seconds. But yes, as mentioned individual transactions will be batched and then will be written to the database, so this should have lesser resource consumption and contention created.

To test if Aurora postgresql will be comparable to cater the above needs (in regards to the expected performance with nominal cost) ,how should we test it? As we won't be able to test everything right away, Should we test basic read and write performance and benchmark to have some confidence and go ahead with development?

Say for example if one transaction consists of ~8 Inserts we can create a sample target table on aurora Postgresql with required indexes/constraints and try running those inserts from multiple threads(for concurrency) using blazemeter and see/compare the response time, CPU, Memory etc. Similarly to see read performance we can run multiple select queries from blazemeter and compare the response time.

Is this above the correct approach for deciding the database here?

And another question coming to mind, I read in past Vaccum to be a problem in postgresql, is going to give trouble in Aurora prostgresql too, for such a highly transactional read/write system? How to validate that?