Re: Pictorial/Tabular comparison resources of PostgreSQL and Oracle databases

On 1/26/23 09:32, kunwar singh wrote:
> Hi Listers,
> Any recommendation on which resources for understanding visually (even
> tabular format would be great) the processes, basic architecture,
> parameters, files between Oracle and PostgreSQL. Moving to  PostgreSQL
> after spending so much time in Oracle  is not that fun :).
>
> I bought two books, yet I still don't feel comfortable.

TL;DR

I have completed one such project and it was a success. We (the programmers working with me and me) had to rewrite all PL/SQL to PL/pgsql, but by far the worst problem was that any error within XA transaction makes the transaction "invalid". It still needs to be rolled back manually. Savepoints are not supported within XA transactions. All exceptions had to be handled WITHIN transaction. Since the application uses WAS, MQ Series and database, XA played a significant role, so significant portion of the code had to be changed.

The other problem was the fact that if you have multi-column unique key and one of the columns is NULL, the rest of the values can be the same, the constraint will not be violated. Here is what I have in mind:

[mgogala_at_umajor ~]$ psql -U scott
Password for user scott:
psql (14.6)
Type "help" for help.

scott=> create table test1(c1 int,c2 int,c3 varchar(10)); CREATE TABLE
scott=> alter table test1 add constraint test1_uq unique(c1,c2); ALTER TABLE
scott=> insert into test1 values (1,null,'Mladen'); INSERT 0 1
scott=> insert into test1 values (1,null,'Mladen'); INSERT 0 1
scott=> insert into test1 values (1,null,'Mladen'); INSERT 0 1
scott=> insert into test1 values (1,null,'Mladen'); INSERT 0 1
scott=> select * from test1;
  c1 | c2 |   c3
----+----+--------

   1 |    | Mladen
   1 |    | Mladen
   1 |    | Mladen
   1 |    | Mladen

Here is what happens with Oracle:

mgogala_at_umajor ~]$ sql scott/tiger_at_localhost/ORCLCDB

SQLcl: Release 22.4 Production on Thu Jan 26 20:47:23 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0

SQL> create table test1(c1 integer,c2 integer,c3 varchar2(10));

Table TEST1 created.

Elapsed: 00:00:00.010
SQL> alter table test1 add constraint test1_uq unique(c1,c2);

Table TEST1 altered.

Elapsed: 00:00:00.035
SQL> insert into test1 values (1,null,'Mladen');

1 row inserted.

Elapsed: 00:00:00.017
SQL> insert into test1 values (1,null,'Mladen');

Error starting at line : 1 in command -
insert into test1 values (1,null,'Mladen') Error report -
ORA-00001: unique constraint (SCOTT.TEST1_UQ) violated

Elapsed: 00:00:00.032
SQL> That was resolved by creating unique index on coalesce(c1,1),coalesce(c2,2):

mgogala_at_umajor ~]$ psql -U scott
Password for user scott:
psql (14.6)
Type "help" for help.

scott=> create table test1(c1 int,c2 int,c3 varchar(10)); CREATE TABLE
scott=> create unique index test1_uq on test1(coalesce(c1,1),coalesce(c2,1));
CREATE INDEX
scott=> insert into test1 values(1,null); INSERT 0 1
scott=> insert into test1 values(1,null); ERROR:  duplicate key value violates unique constraint "test1_uq" DETAIL:  Key (COALESCE(c1, 1), COALESCE(c2, 1))=(1, 1) already exists. scott=>

That has had several implications, as encoded values are frequently used, and forced us to re-architect things to significant degree.

Please note that the syntax is almost exactly the same as with Oracle. Also, in this particular case, PostgreSQL behavior is right. NULL isnt' equal to anything so if one of the values in a tuple is NULL, there cannot be anything equal to that tuple. PostgreSQL behaves as mandated by SQL ISO 2000 standard. Oracle behaves like Oracle. The fact that I've spent all my life with Oracle and am far more familiar with Oracle doesn't matter here.

Another thing that we weren't able to resolve is the fact that Postgres doesn't have global indexes. In other words, partitions are a system of views, similar to what used to be done in Oracle7. Hopefully, there are still some people alive on this group who remember Oracle7. You can't create unique index or a primary key on a partitioned table, unless the index is prefixed by the partitioning key. Apart from Oracle, DB2 is the only other database that has global indexes. We bypassed it by not using partitioned tables at all. That had some consequences on the cleanup procedures. In Oracle, we simply truncated the partition with the unnecessary data, rebuilt the global indexes and that was it. In Postgres, we had to use the DELETE command. Fortunately, one of the things that Postgres doesn't have is "ORA-01555 Snapshot too old" which brings joy to every DBA's heart. Postgres also doesn't have parallel DML, so we had to make do with GNU parallel (https://www.gnu.org/software/parallel/) to speed up the cleanup of old data.

Parallel query works rather fine. Something you definitely need is pg_hint_plan extension. It doesn't have use_concat, which is one of the hints I'm rather fond of. It does have an equivalent of opt_param hint, which is rather advanced stuff. PostgreSQL optimizer is quite pathetic when it comes to prepared transactions and bind values.

Generally speaking, you'll need much more powerful machine with PostgreSQL than with Oracle. However, the difference in price will easily buy you a monster with 32 16 core processors and 2 TB of DDR5 RAM like HPE Superdome Flex .

Oracle is still the most powerful database in the world. You will need a significant effort to rewrite any meaningful application to PostgreSQL. It took the application team and me (a dedicated development DBA/system architect) a better part of 2021 to do so. That is a significant expenditure of resources and money. Don't try it unless you're willing to do that. You can save yourself significant effort with rewriting PL/SQL by using Enterprise DB, but EDB is not free. It's 25% of the comparable price and you can't run on some of the newer commercial versions like Yugabyte or Aurora. Especially Aurora is highly optimized for I/O. A birdie told me that the same guy who has designed Exadata has something to do with that. It's only retired Oracle Ace in existence, as far as I know.

May the force be with you, my young padawan. Proud of you, I am.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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