Re: [Non-DoD Source] Re: PostgreSQL

Have you run your schemas through AWS Schema Conversion Tool to see how big a job you have with the PL/SQL? Have you settled yet on either Aurora or RDS Postgres or Redshift?

Like Kellyn implied, one kind or another of PG or some other part of your chosen cloud ecosystem can do most of what you’re asking for, but you’ll need a little expertise in PG and that cloud to make the right decisions. AWS has Oracle to Postgres database specialist solution architects who help customers with this every day, called the "DB Freedom" team. Reach out to your AWS account team and ask them to put you in touch with them. You can reach out directly to me if you need any AWS-specific assistance.

About AWS: For data warehouses, customers generally move to Redshift, which offers the correct features for that use case. For OLTP they choose either Aurora Postgres or RDS Postgres. Aurora is AWS’s flagship OLTP database with more enterprise and cloud-native features and capabilities, while RDS Postgres is an automated web service implementation of community PG on native AWS components.

Tim is right that you want to know about peak sustained average active sessions and throughput (TPS) for sizing. You haven't mentioned your top end needs there, but since you're already on RDS Oracle, and the same instance classes are available for PG, you will likely be able to support your workload. Seems from what you have mentioned so far, the largest amount of work will be the PL/SQL. As I mentioned, Schema Conversion Tool can help with that.

As for specific quirks of PG vs. Oracle: One is that PG uses an append-only tuple (aka row) versioning model while Oracle uses undo for tuple versioning. That means PG needs "vacuuming" which is basically old tuple-version garbage collection, to run periodically to keep the tables from bloating. Another is that there is no global equivalent of the library cache. Parsed cursors and PL/SQL are not stored in shared memory and shared amongst sessions. This has implications mainly on monitoring, as most Oracle folks are used to being able to look at v$sql and v$sqlplan to see what SQL runs most frequently, longest, etc. and with which plan. You can do some of this in PG in a limited fashion but it isn't as straightforward. In Aurora and RDS, the Performance Insights dashboard collects some of these things and helps make PG feel comfortable for Oracle practitioners.

Thanks

Jeremiah

On Fri, Jan 6, 2023 at 7:38 AM Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil> wrote:

> Ok. The current Oracle databases are 19c in AWS (RDS) and we are staying
> in AWS. OLTP and Data warehousing. No RAC. Lots of stored procedures,
> functions, materialized views, scheduler jobs. Lots of partitioning. No
> replication. No DataGuard. A couple of thousand connections at a time.
> 14 databases. Around 50TB total disk space.
>
>
>
> *From:* Jeremiah Wilton <jcwilton93_at_earlham.edu>
> *Sent:* Friday, January 6, 2023 10:00 AM
> *To:* jeff.d.smith_at_oracle.com
> *Cc:* Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <
> Tom.Terrian.ctr_at_dla.mil>; oracle-l <oracle-l_at_freelists.org>
> *Subject:* [Non-DoD Source] Re: PostgreSQL
>
>
>
> Well this is a fact free discussion so far….
>
>
>
> What kinds of workloads? TPS? Read/write ratios? How much PL/SQL and
> stored procedures, functions, packages etc.? What other features, like
> replication, partitioning, IOTs, RAC, DataGuard, etc.? Give us something to
> work with here!
>
>
>
> Jeremiah
>
>
>
> Sent from my iPhone
>
>
>
> On Jan 6, 2023, at 5:09 AM, Jeff Smith <dmarc-noreply_at_freelists.org>
> wrote:
>
> 
>
> My initial thought is you’re moving in the wrong direction.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Terrian Thomas J CTR DLA INFO OPERATIONS
> *Sent:* Friday, January 6, 2023 7:37 AM
> *To:* oracle-l <oracle-l_at_freelists.org>
> *Subject:* [External] : PostgreSQL
>
>
>
> Here are some open ended question for the group…We are starting to look at
> migrating our databases from Oracle to PostgreSQL. I know nothing about
> PostgreSQL.
>
>
>
> Has anyone done a pro’s and con’s list of Oracle vs. PostgreSQL?
>
> Anyone have a lesson’s learned list from migrating from Oracle to
> PostgreSQL?
>
> Any thoughts/comments on PostgreSQL?
>
> I kind of think that you get what you pay for…wouldn’t that mean that
> Oracle would outperform PostgreSQL in every way?
>
>
>
> Any comments would be appreciated.
>
>
>
> Tom
>
>

--
http://www.freelists.org/webpage/oracle-l