RE: [External] : Re: PostgreSQL

From: Jeff Smith <"Jeff>
Date: Fri, 6 Jan 2023 20:33:52 +0000
Message-ID: <SJ0PR10MB4686FC617DD67D20EE741786A3FB9_at_SJ0PR10MB4686.namprd10.prod.outlook.com>



What you want, is possible, with Oracle.

Whether you want Oracle or not, is something I can’t help with.

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Ilmar Kerm Sent: Friday, January 6, 2023 3:17 PM
To: Oracle-L Freelists <oracle-l_at_freelists.org> Subject: [External] : Re: PostgreSQL

Postgres is a single instance database, so "Zero downtime patching with PostgreSQL" can't be possible... As is the case with Oracle single instance RDBMS. They are exactly the same in this regard. The link Jeff posted requires RAC. As would be the case also with Postgres - you need like Yugabyte or some similar multi-instance flavored Postgres-compatible software - not Postgres. Near zero isn't zero.

On Fri, Jan 6, 2023 at 8:34 PM Terrian Thomas J CTR DLA INFO OPERATIONS <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote: Jeff, that is not possible with Oracle on AWS RDS is it?

From: Jeff Smith <jeff.d.smith_at_oracle.com<mailto:jeff.d.smith_at_oracle.com>> Sent: Friday, January 6, 2023 1:15 PM
To: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil<mailto:Tom.Terrian.ctr_at_dla.mil>>; Clay Jackson (cjackson) <Clay.Jackson_at_quest.com<mailto:Clay.Jackson_at_quest.com>>; tim.evdbt_at_gmail.com<mailto:tim.evdbt_at_gmail.com>; dbakevlar_at_gmail.com<mailto:dbakevlar_at_gmail.com> Cc: oracle-l <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: [Non-DoD Source] RE: PostgreSQL

Well we can take down reason #1

https://docs.oracle.com/en/database/oracle/oracle-database/19/fppad/patching-oracle-database-without-downtime.html

Jeff

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Terrian Thomas J CTR DLA INFO OPERATIONS Sent: Friday, January 6, 2023 1:06 PM
To: Clay Jackson (cjackson) <Clay.Jackson_at_quest.com<mailto:Clay.Jackson_at_quest.com>>; tim.evdbt_at_gmail.com<mailto:tim.evdbt_at_gmail.com>; dbakevlar_at_gmail.com<mailto:dbakevlar_at_gmail.com> Cc: oracle-l <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: [External] : RE: PostgreSQL

The “Why” that I was given by the customer: 1. Zero downtime patching with PostgreSQL. 2. Cost less.

From: Clay Jackson (cjackson) <Clay.Jackson_at_quest.com<mailto:Clay.Jackson_at_quest.com>> Sent: Friday, January 6, 2023 11:58 AM
To: tim.evdbt_at_gmail.com<mailto:tim.evdbt_at_gmail.com>; dbakevlar_at_gmail.com<mailto:dbakevlar_at_gmail.com>; Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil<mailto:Tom.Terrian.ctr_at_dla.mil>> Cc: oracle-l <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: [Non-DoD Source] RE: PostgreSQL

Lots of great comments from Kellyn and Tim on the pitfalls and ways to avoid/mitigate them or at least know what you’re getting into.

Taking a step back – you haven’t shared the “high-level WHY”. As Tim stated, “it is what it is” and “if it ain’t broke, don’t fix it” can be pretty compelling.

Any “migration” or move is going to cost something, both “external hard costs” and “soft costs” including foregoing implementation of NEW systems. What’s the projected return on investment, and what BUSINESS gains do you expect as a result of this effort?

We’ve observed several cases where a “mandate from on high” to “move off Oracle” was tempered into, “Let’s do FUTURE development of NEW systems in PostgreSQL” when the “real” cost of “moving off Oracle” was discovered. You mentioned you have both OLTP and a DataWarehouse. Depending on “which PostgreSQL” you choose, you might consider moving the DataWarehouse first and then using some sort of replication or ETL to move the data from the OLTP system(s) to the DataWarehouse. Then you can move forward with NEW systems in PostgreSQL and let your Oracle OLTP systems die a “natural” death.

Clay Jackson

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Tim Gorman Sent: Friday, January 6, 2023 8:23 AM
To: dbakevlar_at_gmail.com<mailto:dbakevlar_at_gmail.com>; Tom.Terrian.ctr_at_dla.mil<mailto:Tom.Terrian.ctr_at_dla.mil> Cc: oracle-l <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: PostgreSQL

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

Expanding on Kellyn's last point, specifically about trying to cram 50 kg of Oracle database onto 5 kg of infrastructure...

In each of the source Oracle databases, have you measured...

  • I/O latency
    • a good indication is the average wait time on "db file sequential read" events from AWR/STATSPACK reports
  • I/O throughput
    • IOStats By Function and IOStats by FileType are two sections of the AWR/STATSPACK reports to reference

Most importantly, are you generating AWR/STATSPACK reports from "peak workload" periods of time? Or are you just using any old snapshots from any old time period? If the latter, then don't bother. As with any use of AWR/STATSPACK reports, the time period captured is the whole point of the exercise. When one is assessing workloads to move to new infrastructure, it is best to assess the worst-case scenario, which are peak workloads. If you don't know when peak workloads occur in your database, one bit of help might be the "busiest_awr.sql" script posted on Github HERE<https://urldefense.com/v3/__https:/nam12.safelinks.protection.outlook.com/?url=https*3A*2F*2Fgithub.com*2FAzure*2FOracle-Workloads-for-Azure*2Fblob*2Fmain*2Faz-oracle-sizing*2Fbusiest_awr.sql&data=05*7C01*7Cclay.jackson*40quest.com*7Cc54f796bb74142539fee08daf00269d4*7C91c369b51c9e439c989c1867ec606603*7C0*7C0*7C638086190405673374*7CUnknown*7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*3D*7C3000*7C*7C*7C&sdata=gQ7r1rD2mJGq7mODn4*2FMWL*2Fo*2BlOTw5KGwi6Agn7MVp4*3D&reserved=0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJQ!!ACWV5N9M2RV99hQ!Jnj4LOGjOKOySonbc1dnXcKzptHymZ1CvPS2Kzyv7mnlVPIP6HaLTLW6yhid3xjiitOmJxJ66Bkae2rjTVx-Fw$>. It queries the DBA_HIST_SYSMETRIC_HISTORY views to display AWR snapshots with the highest values for CPU and I/O. I don't yet have a version of the script for STATSPACK, but I expect to have one posted by the end of next week, if not sooner. Since STATSPACK doesn't capture SYSMETRIC information, I anticipate having to use SYSSTAT information, which I believe is less useful and has more problems for this sort of analysis.

Anyway, if an AWR/STATSPACK report generated under peak workload indicates an Oracle database with the following characteristics...

  • 73 average active sessions (AAS) for significant periods of time
  • I/O latency averaging 0.3 ms
  • I/O throughput averaging greater than 3000 MB/s

...and the plan is to replatform it into a PaaS service or infrastructure capped at 300 MB/s, then it is a non-starter. Same if the PaaS service or infrastructure has a maximum of 64 vCPUs.

Of course, my own self from 10-15 years ago would have stepped in now and pontificated about how any Oracle workload can be tuned to run on an iPhone, but the reality is that it is unlikely. I still know that there is always room for improvement, but also that not many organizations are interested in optimizing. The dreaded phrases "it is what it is" and "if it ain't broke don't fix it" come into play.

This platform-agnostic baseline information is so easy to obtain, and it provides immediate value when assessing rehosting or replatforming. On 1/6/2023 7:33 AM, Kellyn Pot'Vin-Gorman wrote: I'm going to jump in here and start with the reason why- Although Tim and I both do Oracle on Azure IaaS, we receive a lot of Oracle databases that fail migrating to PostgreSQL. The reasons that this commonly happens is as follows:

1.  The team underestimated all the applications and connectors post the database migration that required refactoring.  Oracle is never just a database-  there's always more to consider.
2.  The database housed complex PL/SQL functions and advanced Oracle features that weren't able, (and remember, I'm not the one who did the migration, just received the database back and hearing this second hand) to duplicate it with PostgreSQL.
3.  The performance had years to be optimized in Oracle and refactoring takes considerable time and dedication.  If the customer and the team performing the migration isn't willing to not just refactor but dedicate a full redesign to use the best of PostgreSQL to optimize the workload, that workload "explosion" can impact performance so significantly that it's unacceptable.
4.  The flavor of PostgreSQL MATTERS.  What EnterpriseDB Big Animal can handle is different than CosmosDB Hyperscale Citus and that's different than Amazon Redshift or what Yugabyte offers.  Use the right PostgreSQL for the job.  If I see one more 3000 MBPs Oracle workload attempted in a PaaS PostgreSQL that can only do 300MBPs max, I'm going to scream. :)


Kellyn Gorman
DBAKevlar Blog<https://urldefense.com/v3/__https:/nam12.safelinks.protection.outlook.com/?url=http*3A*2F*2Fdbakevlar.com*2F&data=05*7C01*7Cclay.jackson*40quest.com*7Cc54f796bb74142539fee08daf00269d4*7C91c369b51c9e439c989c1867ec606603*7C0*7C0*7C638086190405673374*7CUnknown*7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*3D*7C3000*7C*7C*7C&sdata=PETWViiX7giuv3UXS8n5ngnV24L7V7a43pd*2BMMT4whY*3D&reserved=0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUl!!ACWV5N9M2RV99hQ!Jnj4LOGjOKOySonbc1dnXcKzptHymZ1CvPS2Kzyv7mnlVPIP6HaLTLW6yhid3xjiitOmJxJ66Bkae2q99LtbPw$> about.me/dbakevlar<https://urldefense.com/v3/__https:/nam12.safelinks.protection.outlook.com/?url=http*3A*2F*2Fabout.me*2Fdbakevlar&data=05*7C01*7Cclay.jackson*40quest.com*7Cc54f796bb74142539fee08daf00269d4*7C91c369b51c9e439c989c1867ec606603*7C0*7C0*7C638086190405830668*7CUnknown*7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*3D*7C3000*7C*7C*7C&sdata=DgG3zkfEw3*2F7z2M8J7TwqVi8bWYb8*2BBH2MHYtU9nGNI*3D&reserved=0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJQ!!ACWV5N9M2RV99hQ!Jnj4LOGjOKOySonbc1dnXcKzptHymZ1CvPS2Kzyv7mnlVPIP6HaLTLW6yhid3xjiitOmJxJ66Bkae2oVTRt_-g$>

On Fri, Jan 6, 2023 at 4:37 AM Terrian Thomas J CTR DLA INFO OPERATIONS <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote: 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

--
Ilmar Kerm

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 06 2023 - 21:33:52 CET

Original text of this message