Re: Moving to Postgresql database

You can find a lot information in this link

https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html

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.

On Mon, Jan 15, 2024 at 1:43 PM Justin Clift <justin(at)postgresql(dot)org> 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