Re: Moving to Postgresql database

To actually perform the migration, you can use some AWS tools such as SCT + DMS: https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-an-oracle-database-to-aurora-postgresql-using-aws-dms-and-aws-sct.html

Postgresql is a relational database just like Oracle, so database designs (OLTP vs OLAP) are the same.
However, implementations of partitioning, indexing, isolation, etc... Those have the same concept but some implementation differences.

Isolation, for instance, in Oracle you have the UNDO tablespace. In Postgresql you have a different method that uses MVCC (Multi Version Concurrency Control), which keeps deleted records (dead tuples), until a vacuum process (or autovacuum) runs.
Performance, you have different memory parameters to adjust, hints. For execution plan management, you need to install the Postgresql extension apg_mgt_plan. I would recommend the following extension: 

• pgstattuple - row level statistics
• pg_stat_statements - planning and execution of statistics
• apg_mgmt_plan - for management of query plans
• pg_cron - to schedule jobs
  

Index fragmentation occurs in Postgresql as well, so be sure to monitor it because you may need to run a REINDEX CONCURRENTLY (in oracle INDEX REBUILD ONLINE) when high fragmentation is observed.
Indexes in Postgresql have the INCLUDE option (which is similar to SQL Server), which are columns to append to the index so you dont need to scan the table.

Since you are going to Aurora Postgresql, it has the Performance Insights tool which provides statistics and queries run against your Aurora Cluster. You can enable it for free to have 7 days of statistics. It comes handy if you are used to AWR in Oracle to investigate issues.