Re: [Non-DoD Source] Re: PostgreSQL

I'm glad Jeremiah spoke up- I didn't want to be the Azure person touting AWS DB Freedom and running the migration assessment tool, but yeah...what he said. :)

*Kellyn Gorman*
DBAKevlar Blog <http://dbakevlar.com>
about.me/dbakevlar

On Fri, Jan 6, 2023 at 9:44 AM Jeremiah Cetlin Wilton < jcwilton93_at_earlham.edu> wrote:

> 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