Re: Differences in key database design and architecture
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 15 Jan 2024 09:04:48 -0500
On 1/14/24 14:17, yudhi s wrote:
Now that organization is choosing AWS cloud as target state and thus
the databases we are exploring for some new development project are
mainly AWS aurora postgresql and mysql. So wanted to understand, if
there 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 or
in case of moving an existing Oracle database system to AWS
postgresql/mysql databases?
Well, of course there are many differences. The meaning of the phrase
"database agnostic application" is that the application sucks equally on
all supported databases. I don't have any experience on the Aurora
Postgres, so I don't know what version of Postgres database it is. There
are differences with XA (distributed transactions), there are
differences with the unique indexes, there are no packages in PGPL/SQL
and there are no global variables. There are local temporary tables,
which can be indexed, there are "bloom" indexes and many other things.
It will be an interesting journey. I've done it before and I expect to
do it again soon. Here is a teaser:
^
scott=> create table test_tab (
col1 integer not null,
col2 varchar(10),
col3 timestamp);
CREATE TABLE
scott=> alter table test_tab add constraint test_tab_uq unique(col1,col2);
ALTER TABLE
scott=> insert into test_tab values(1,null,current_timestamp);
INSERT 0 1
scott=> insert into test_tab values(1,null,current_timestamp);
INSERT 0 1
scott=> insert into test_tab values(1,null,current_timestamp);
INSERT 0 1
For the record, I inserted 3 rows with the NULL values into the table.
Now, the remedy which can only be done in Postgres 15 or later:
scott=> alter table test_tab drop constraint test_tab_uq;
ALTER TABLE
scott=> truncate table test_tab;
TRUNCATE TABLE
cott=> create unique index test_tab_uq on test_tab(col1,col2) *_nulls
not distinct_*;
CREATE INDEX
scott=> insert into test_tab values(1,null,current_timestamp);
INSERT 0 1
scott=> insert into test_tab values(1,null,current_timestamp);
ERROR: duplicate key value violates unique constraint "test_tab_uq"
DETAIL: Key (col1, col2)=(1, null) already exists.
scott=>
That is the default behavior for Oracle. Furthermore, such indexes are
not allowed in primary keys. Postgres requires all columns in the PK to
be not null:
scott=> alter table test_tab add constraint test_tab_pk primary
key(col1,col2);
ERROR: column "col2" of relation "test_tab" contains null values
scott=>
In Oracle, you can have NULL values as a part of the primary key. The
absolute worst difference that cannot be abridged is the fact that it is
not possible to create global indexes on the partitioned tables. You
will have to organize your application in such a way that it always
looks only into a single partition. BTW, the account I'm using for
testing may look familiar:
scott=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
scott | bonus | table | scott
scott | dept | table | scott
scott | emp | table | scott
scott | salgrade | table | scott
scott | test_tab | table | scott
(7 rows)
I am using Postgres 16.1, the latest and the greatest. I don't know
whether that is available as an Aurora version.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
Other related posts:
