You can find a lot information in this link
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.
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:
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.
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
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.
On Mon, Jan 15, 2024 at 1:43 PM Justin Clift <justin@xxxxxxxxxxxxxx> wrote:
On 2024-01-15 14:16, veem v wrote:
> Hello Experts,
> If some teams are well versed with the Oracle database architecture and
> its
> optimizers working and designing applications on top of this. Now
> moving
> same team to work on AWS aurora postgresql databases design/development
> projects. Is any key design/architectural changes should the app
> development team or the database design team, should really aware
> about, so
> as to take right decision on any new development project in AWS aurora
> postgresql database?
> Or
> Is there any list of differences(as compared to Oracle database) in key
> concepts like for example basic design concepts, Normalization,
> Partitioning, clustering, backup and recovery, Indexing strategy,
> isolation
> level, performance which one should definitely be aware of?
Is this the kind of thing you're looking for?
https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative
Regards and best wishes,
Justin Clift
Best Regards,
_____________________________
Henrique S. G. Lima
Mobile: +1 (204) 951-6191
Henrique S. G. Lima
Mobile: +1 (204) 951-6191
