PostgreSQL 4 Oracle DBAs

PostgreSQL e' il piu' completo tra i DBMS relazionali Open Source, Oracle e' invece il piu' diffuso tra gli RDBMS commerciali. Non e' cosi' improbabile quindi che capiti di passare dall'uno all'altro... Questo documento cerca di presentare le principali differenze tra i due RDBMS permettendo cosi' ad un DBA Oracle di lavorare in breve tempo ed efficacemente con PostgreSQL.

Il documento e' organizzato in brevi capitoli: Ho fretta!, Elementi comuni, Data Types, Transazioni, Funzioni ed operatori, Performance ed Ottimizzatore, Stored Procedures, Installazione ed amministrazione, Configurazione, Security, Architettura Programmazione, Compatibilita' Oracle 100%, ...

Per una descrizione generale su PostgreSQL conviene leggere Introduzione a PostgreSQL, Qualcosa in piu' su PostgreSQL, Installazione di PostgreSQL su Ubuntu, ...
Naturalmente la migliore e piu' completa sorgente di informazioni e' il sito ufficiale PostgreSQL ed in particolare la documentazione on-line.

Su Oracle la documentazione e' sterminata... la documentazione ufficiale e' molto ampia e su web si trova anche qualche mia paginetta: C'era una volta Oracle, I piu' comuni errori su Oracle, Novita' presenti in Oracle 10g (R2 review), Oracle 12c R2 for DBAs, ... ed altri centinaia di ottimi documenti e siti web (ottimi anche perche' non scritti da me!).
Le migliori e piu' aggiornate sorgenti di informazione su Oracle sono la documentazione ufficiale ed il portale di supporto My Oracle Support (noto anche come MOS e Metalink, NB: con accesso riservato).

Questo documento e' stato preparato con la versione 14 di PostgreSQL e la versione 19c di Oracle su un Linux ma e', mutatis mutandis, valido anche per altre versioni.

Ho fretta!

Ho fretta e voglio lavorare subito con PostgreSQL!
In questo capitolo vengono riportati gli elementi per lavorare immediatamente. Ovviamente gli stessi argomenti verranno ripresi nel seguito in modo piu' completo e strutturato... o almeno lo speriamo!
Insomma, supponiamo di avere un PostgreSQL installato e funzionante su un bel sistema Linux. Come facciamo a farci un giro?

Al posto dell'sqlplus come interfaccia da linea di comando usiamo psql. Con un'installazione di default non c'e' bisogno di specificare username e password: e' supportato l'utente postgres come trusted nelle connessioni da localhost. Volendo accedere ad un differente sistema, con utenza e password il comando e':
 psql -h hostname -U username -W [database]

Il psql funziona come l'sqlplus, con l'aggiunta di innumerevoli comandi interni richiamati con il backslash. Con \? otteniamo la lista dei comandi psql con \h otteniamo la lista dei comandi SQL e con \h select otteniamo l'help sulla clausola di select. Con \dX si ottengono informazioni sugli oggetti dove X puo' essere un fracco di cose! Con \l si ottiene l'elenco dei database.
Sulla struttura e la gerarchia dei database c'e' una grande differenza con Oracle.
Per lavorare su database differente e' necessario eseguire una nuova connessione. In pratica postgres gestisce un cluster di database sulla stessa porta di connessione, un poco come avviene con i pluggable database di Oracle. Con l'installazione sono sempre presenti un database postgres e due template; pero' e' fortemente consigliabile creare database specifici per le applicazioni ospitate.

PostgreSQL database schema user role Utilizzare in Client/Server PostgreSQL e' molto semplice. Disponendo di un'unica installazione e di un unico servizio (che risponde alla porta 5432) e' sufficiente il nome dell'host ed il nome del database per accedere ai dati. Non e' quindi necessario configurare un tnanames.ora o utilizzare complesse stringhe di connessione. Tipicamente ogni applicazione utilizza un database differente quindi la configurazione "applicativa" richiede: host, user, password e database. Attenzione che database, utenti e schema sono concetti completamente differenti in PostgreSQL e che la visibilita' degli oggetti non si gestisce con sinonimi (come in Oracle) o con il comando di USE (come in MySQL) ma impostando il search_path.

Con le impostazioni di default non serve impostarle ma anche PostgreSQL ha variabili di ambiente simili all'ORACLE_HOME o all'ORACLE_SID: PGDATA indica la directory in cui e' fisicamente posta l'istanza con i dati, i file di configurazione, i log, ... (il default dipende dal sistema operativo, tipicamente e' /var/lib/pgsql/data/ e sicuramente e' opportuno modificarlo per un installazione di produzione), mentre PGPORT indica la porta di ascolto (default 5432).

Dopo l'installazione viene generato un utente superuser postgres con accesso da localhost e con tutti i diritti. E' poi possibile definire gli accessi per gli altri utenti con i normali comandi di CREATE USER e di GRANT. Attenzione che l'abilitazioni degli utenti richiede una configurazione del ph_hba.conf e grant specifici a livello di database e di schema.

Il controllo degli errori SQL in PostgreSQL e' rigido come in Oracle, anzi forse anche di piu', ma naturalmente gli errori sono differenti.

Elementi comuni

Oracle e PostgreSQL sono due ottimi database relazionali basati sull'SQL. In realta' gli elementi comuni sono piu' delle differenze. L'SQL e' al 95% identico anche se entrambi i DBMS hanno estensioni e personalizzazioni. Entrambi i database hanno una gestione completa delle transazioni con il completo rispetto delle proprieta' ACID. I tipi di applicazioni supportate e supportabili, le piattaforme su cui sono disponibili, il linguaggio con cui sono stati scritti (ovviamente il linguaggio C) ... sono gli stessi per entrambi i database! PostgreSQL ha piu' caratteristiche object-oriented mentre il PL/SQL di Oracle e' piu' completo e potente.
Quindi il primo tentativo quando non si sa se e' supportata una funzionalita', se una clausola e' presente, se ... e' provare con la stessa sintassi!

I linguaggi di programmazione utilizzabili con PostgreSQL ed Oracle sono moltissimi e non e' significativo riportane l'elenco. Ovviamente sono disponibili driver JDBC per entrambi e le modalita' di connessione sono simili per i due database.

Una volta era importante il confronto sui limiti che avevano i DB... con le attuali versioni, a meno di requisiti particolari, i limiti di PostgreSQL e di Oracle sono talmente ampi che non ha senso confrontarli. Quindi la dimensione massima del DB, la dimensione massima di un BLOB, il numero massimo di colonne per tabella, di colonne in un indice, il massimo ed il minimo numero rappresentabili, il numero di cifre significative di un numero, la lunghezza massima dei nomi delle colonne, ... oramai sono limiti talmente ampi su entrambi gli RDBMS che non hanno importanza per la maggioranza delle configurazioni. Anzi in generale i limiti di PostgreSQL generalmente sono piu' elevati di quelli di Oracle [NdA questo e' particolarmente vero con le versioni di Oracle precedenti alla 12].
L'assenza di stored procedures o la necessita' di eseguire manualmente operazioni di VACUUM... sono spesso riportati come difetti di PostgreSQL, ma sono relativi a versioni vecchie del prodotto. Le versioni piu' recenti di PostgreSQL supportano perfettamente, anche se in modo diverso da Oracle, tali funzionalita' [NdA PG 8.1 ed 8.3 per l'autovacuum, PG 11 per le stored procedures].

Riassumendo: le parti comuni sono molte!

Nel seguito riporteremo le principali differenze organizzate (o disorganizzate) come segue: Data Types, Transazioni, Funzioni ed operatori, Performance ed Ottimizzatore, Replication, Varie ed eventuali, Stored Procedures, Strumenti, Installazione ed amministrazione, Configurazione, Security, Architettura, Programmazione, Compatibilita' Oracle 100%, Futuro, ...

Data Types

I Data Type supportati da entrambi i DB sono molteplici e vi sono parecchie differenze, anche per la presenza in PostgreSQL di funzionalita' object-oriented piu' sviluppate rispetto ad Oracle.

I principali datatype per stringhe PostgreSQL sono: VARCHAR(N), CHAR(N), TEXT.
Non vi sono differenze prestazionali tra questi datatype in Postgres (se non l'onere del padding per i CHAR) e possono essere gestite stringhe fino ad 1GB.

I principali datatype sulle date PostgreSQL sono: TIMESTAMP, DATE, TIME, INTERVAL.
Le colonne TIMESTAMP e TIME possono essere definite con oppure senza timezone.

I principali datatype numerici PostgreSQL sono: SMALLINT (2 byte, -32768 to +32767), INTEGER (4 byte, -2.147.483.648 to +2.147.483.647), BIGINT (8 byte, -9.223.372.036.854.775.808 to +9.223.372.036.854.775.807); DECIMAL (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) o NUMERIC (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point); REAL (4 byte, inexact, 6 decimal digits precision), DOUBLE PRECISION (8 byte, inexact, 15 decimal digits precision).
Per i datatype numerici le differenze principali con Oracle sono sulla scala e la precisione. Per riportare valori numerici esatti Oracle utilizza: NUMBER[(precision [, scale])] e l'analogo PostgreSQL e' NUMERIC[(precision [, scale])].
Un numero intero puo' essere autoincrementale se definito con SMALLSERIAL, SERIAL o BIGSERIAL. Postgres dispone anche delle SEQUENCE, in effetti la dichiarazione: 

CREATE TABLE tablename (
    colname SERIAL
);
corrisponde a: 
CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Vi sono parecchi altri datatype in PostgreSQL: MONEY, BYTEA, BOOLEAN, geometrici, JSON, XML, UUID, ...
In PostgreSQL e' anche possibile creare datatype enumerati, range, vettori, ...

Naturalmente e' possibile indicare attributi sulle colonne (eg. NOT NULL) con alcune piccole differenze tra i due RDBMS. PostgreSQL ha un ampio numero di CONSTRAINT che consentono di controllare i valori inseriti nelle colonne: Primary Keys, Unique Constraints, Foreign Keys, Not-Null Constraints, Check Constraints, Exclusion Constraints.

Ogni tabella in Postgres ha una serie di system columns presenti in ogni tabella: tableoid, xmin, cmin, xmax, cmax, ctid.

Per maggiori dettagli si puo' fare riferimento all'ottima documentazione online.

Data Type conversion from Oracle to Postgres

Dovendo migrare da Oracle a Postgres le conversioni possibili sono molteplici... ovviamente e' sempre possibile convertire i dati in stringhe e vi sono datatype specifici che non presentano le stesse funzionalita' (eg. rowid), a seconda delle versioni di Oracle i datatype possono avere lunghezze massime differenti. La matrice di conversione quindi e' molto variabile e complessa.

Volendo riassumere i casi piu' frequenti puo' essere utile questa tabella:

Oracle data type PostgreSQL data type Note
Strings
CHAR(n) CHARACTER(n) NCHAR(n) CHAR(n)
VARCHAR(n) VARCHAR2(n) NVARCHAR2(n) VARCHAR(n) In Oracle vi sono limiti differenti per n a seconda delle versioni, in Postgres il limite e' nettamente superiore
LONG CLOB NCLOB TEXT
RAW(n) LONG RAW BLOB BYTEA
Dates
DATE TIMESTAMP(0)
TIMESTAMP(p) TIMESTAMP(p)
Numbers
SMALLINT SMALLINT 2 byte
INT INTEGER INTEGER NUMERIC(38,0) INTEGER se bastano 4 byte...
NUMBER(p,s) NUMERIC(p,s)
FLOAT(p) REAL DOUBLE PRECISION DOUBLE PRECISION
Others
ROWID UROWID(n) VARCHAR
XMLTYPE XML
BOOLEAN BOOLEAN

Non tutte le conversioni riportate sono compatibili al 100%... ma la tabella riportata, a mio avviso, e' gia' una buona base di partenza.

Transazioni

Con PostgreSQL vi e' il supporto completo delle transazioni (ACID), i lock sono mantenuti a livello di riga e vi e' una gestione completa della deadlock detection.
In PostgreSQL per default e' attivo l'AUTOCOMMIT (si puo' disattivare con SET AUTOCOMMIT=OFF;), quindi ogni statement SQL esegue automaticamente il commit. Per iniziare una transazione con piu' statement basta indicarne l'inizio con BEGIN. In una transazione i comandi SQL possono venire confermati con COMMIT o essere cancellati con ROLLBACK in una transazione PostgreSQL. In Postgres anche le DDL possono far parte di una transazione mentre in Oracle questo non e' possibile.

PostgreSQL implementa tutti gli isolation level previsti dallo standard ANSI: read uncommitted, read committed, repeatable read, serializable. In realta' la modalita' read uncommitted comunque non permette di leggere righe non commitate e si comporta come read committed. Il comando per variare l'isolation level e'
  SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED };

Il default di PostgreSQL e' READ COMMITTED come in Oracle.

Funzionalmente quindi la gestione delle transazioni con PostgreSQL e' simile a quella di Oracle. Le differenze maggiori sono nell'implementazione. Entrambi utilizzano il logging delle transazioni (redo log in Oracle, WAL in Postgres) ma mentre Oracle utilizza i rollback o UNDO segments, PostgreSQL mantiene le righe con le versioni precedenti dei dati rendendo necessarie periodiche attivita' di VACUUM (pulizia) che avvengono comunque in automatico. Di converso in Postgres non si avra' mai un errore del tipo snapshot too old perche' e' sempre garantita la presenza dei dati per le SELECT attive.

Funzioni ed operatori

L'insieme delle funzioni e gli operatori che Oracle fornisce a corredo del proprio SQL e' molto ampio. Anche PostgreSQL ha un ampio numero di funzioni ed operatori. Trattandosi di estensioni dell'SQL le differenze presenti sono notevoli. In particolare con Postgres il controllo dei tipi di dati e' piu' stretto e spesso sono necessari cast espliciti per effettuare le conversioni richieste.
Inoltre storicamente Postgres disponeva solo di funzioni e di stored functions, quindi per molti compiti amministrativi va richiamata una funzione specifica; nel seguito vedremo le principali.

PostgreSQL ha un insieme completo di funzioni logiche: CASE, COALESCE(), ... che sostituiscono egregiamente DECODE(), NVL(), ... di Oracle anche se con sintassi differenti: 

SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other',
       colaesce(b, ' ')
       END
  FROM test;

SELECT a,
       decode(a, 1,'one', 2,'two', 'other'),
       nvl(b, ' ')
  FROM test;

Il numero di funzioni matematiche e numeriche fornito da PostgreSQL e' molto completo e comprende anche funzioni random.
Come in Oracle l'operatore || esegue la concatenazione di stringhe. Sono fornite molteplici funzioni per il confronto di stringhe compresa la gestione delle espressioni regolari: upper(), lower(), substring(), position(), initcap(), regex_match(), replace(), ...
Le funzioni di gestione delle date e le conversioni di formato sono completamente differenti da quelle Oracle! Per sapere che ora e': now(). CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP riportano un valore costante all'interno della stessa transazione. Altre funzioni utili sono: date_trunc(), extract(), ...

Vi e' un repertorio completo di funzioni di compressione e crittografia ma va abilitata la relativa extension pgcrypto.
Qualche funzione informativa: version(), current_database(), current_schema(), current_user, inet_client_addr(), pg_backend_pid(), pg_postmaster_start_time(), ...
Con string_agg() vengono riportati in modo concatenato i valori di una colonna non raggruppata.

Postgres implementa l'intero insieme standard delle funzioni di gruppo, delle window functions e delle common table expression (anzi alcune le ha introdotte prima di Oracle).

Postgres ha un gran numero di funzioni di amministrazione e gestione, anche perche' storicamente inizialmente non disponeva di stored procedures: pg_cancel_backend(pid), pg_terminate_backend(pid), current_setting(setting_name), set_config(setting_name, new_value, is_local), pg_reload_conf(), pg_switch_wal(), ... ovviamente per richiamarle basta eseguire una select:
 SELECT pg_cancel_backend(69);

Come anticipato PostgreSQL e' piu' preciso di Oracle nella gestione dei data type. Ad esempio la funzione round() e' definita con un secondo parametro per definire la scala solo per i numeric e non per i float (per validissime ragioni teoriche). Pero' nel caso in cui servisse eseguire l'arrotondamento di un float si puo' utilizzare l'overloading della funzione: 

\df round()
                            List of functions
   Schema   | Name  | Result data type |    Argument data types    | Type 
------------+-------+------------------+---------------------------+------
 pg_catalog | round | double precision | double precision          | func
 pg_catalog | round | numeric          | numeric                   | func
 pg_catalog | round | numeric          | numeric, integer          | func

CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $f$
  SELECT ROUND( CAST($1 AS numeric), $2 )
$f$ language SQL IMMUTABLE;

Prestazioni ed ottimizzatore

Il discorso sulle prestazioni dei due RDBMS e' molto ampio e controverso. PostgreSQL ha un'impegno inferiore di risorse, ha un ottimizzatore molto efficiente basato su statistiche configurabili in grado di trattare query molto complesse. Di converso la gestione delle transazioni, con un numero elevato di utenti e di accessi concorrenti, viene tipicamente meglio supportata da Oracle.
Insomma entrambi i database sostengono di essere i migliori dal punto di vista prestazionale!

Anche se entrambi utilizzano un'ottimizzatore statistico le differenze tra i due database sono parecchie. PostgreSQL supporta diverse tipologie di indici: B-Tree, Hash, GiST, SP-GiST, GIN, BRIN. Il partitioning e' supportato in PostgreSQL dalle prime versioni mediante il meccanismo dell'ereditarieta', quindi prima che in Oracle, ma con una certa complessita' di definizione e prestazioni non sempre ottimali. Di recente pero' [NdA PG 10] e' stato introdotto il partitioning nativo che offre prestazioni nettamente migliori. Il partitioning nativo di Postgres e' tecnicamente simile a quello Oracle anche se non sono consentiti indici globali e tutte le chiavi univoche debbono contenere le colonne di partizionamento.

La configurazione di PostgreSQL e' un poco piu' semplice di quella Oracle ma, per una base dati che non sia solo di prova, e' necessario un tuning adeguato. Infatti alcuni parametri di default hanno valori molto bassi, che consentono di utilizzare PostgreSQL su sistemi con memoria molto limitata ma che sono un forte limite per le prestazioni. Il file di configurazione e' postgresql.conf ed e' un file di testo contenente molti utili commenti.

Per quanto riguarda il tuning degli statement SQL ottenere l'EXPLAIN PLAN con PostgreSQL e' molto semplice: 

EXPLAIN [ANALYZE] SQL_statement
Con l'opzione analyze lo statement viene eseguito e le indicazioni contengono anche il dettaglio sull'elapsed ed il numero effettivo di righe.

Postgres non dispone di optimizer hint ma e' possibile governare il livello di dettaglio delle statistiche sulle colonne raccolto dall'ANALYZE e vi sono molteplici parametri che possono essere impostati on-line e che influiscono sulle scelte dell'ottimizzatore: 

SET work_mem = '64MB';

SELECT ...

Per individuare gli statement da ottimizzare, anche senza conoscere la parte applicativa, e' possibile utilizzare la potente extension pg_stat_statements.
Nell'ottimizzazione delle query vi sono parecchi elementi in comune (eg. indici) con Oracle ma anche profonde differenze (eg. MVCC); i dettagli delle ottimizzazioni SQL in PostgreSQL sono meglio descritti in questa pagina.

PostgreSQL Extensions vs Oracle Options

Per abilitare ulteriori funzionalita' in Oracle sono disponibili le Options; l'analogo in PostgreSQL sono le Extensions.

Le Oracle Options sono disponibili solo per l'Enterprise Edition ed hanno un costo aggiuntivo; al contrario PostgreSQL pubblica un'unica versione Community con decine di core extension disponibili.

L'installazione di una extension e' molto semplice:
 CREATE EXTENSION Extension_name;

Tra le piu' utilizzate ed interessanti: plpgsql (il linguaggio procedurale PL/pgSQL), pg_stat_statements (raccoglie le statistiche sugli statement SQL utilizzati), adminpack (utility per l'amministrazione), auto_explain (esegue l'auto_explain degli statement tracciati nel log), citext (gestisce le ricerche Case Insensitive in modo ottimizzato), pgcrypto (funzioni di crittogtrafia), pgstattuple (analizza i dettagli di occupazione dello spazio), postgres_fdw (consente la creazione di foreign data wrapper tra database PostgreSQL: l'analogo dei DB link di Oracle), sslinfo (riporta i dettagli delle connessioni crittografate), pg_buffercache (riporta i dettagli del contenuto della buffer cache), pg_trgm (per effettuare ricerche testuali), ...
L'elenco completo delle extension core di Postgres si trova sulla documentazione ufficiale.

Anche terze parti possono sviluppare extension che, una volta installate sul sistema ospite, si creano ed utilizzano in PostgreSQL come le core extension.
Per ultimo ma non da ultimo ricordiamo PostGIS, TimescaleDB e pgvector che tecnicamente sono solo extension ma trasformano PostgreSQL in un database cartografico, in un TSDB (Time Series Database) ed in un database vettoriale rispettivamente.

Varie ed eventuali

In PostgreSQL non c'e' la famosa tabella DUAL ma non c'e' bisogno: e' possibile utilizzare una SELECT senza la clausola FROM!
[NdA probabilmente sara' possibile farlo in Oracle dalla 23c]

Due differenze significative nell'uso di Postgres sono la presenza di piu' database sulla stessa istanza e l'assenza di sinonimi.

Il Data Dictionary e le varie viste prestazionali fornite da PostgreSQL sono molto completi ma con notevoli differenze rispetto a quanto fornito da Oracle. [NdA Manca pero' in Postgres un analogo dell'AWR quindi tutte le statistiche ed i contatori si riferiscono al momento attuale e non e' disponibile una storia che si puo' ottenere solo con strumenti esterni al DB].
I system catalogs/views riportano i dettagli della struttura di tutto il database. In generale i nomi indicano di cosa si tratta: i contenuti di pg_tables, pg_index, pg_user, pg_role, ... si comprendono facilmente!
Naturalmente c'e' qualche nome inatteso per chi non ricorda la natura object-oriented di Postgres: il catalogo piu' importante e' pg_class che contiene tutto quello che ha forma di una tabella: tabelle, indici, viste, sequenze, tabelle TOAST, ... mentre per le colonne si utilizza pg_attribute.
In generale il contenuto dei catalog e' differente per database ma alcuni oggetti sono comuni a tutti i database (eg. gli utenti ed i ruoli sono gli stessi per tutti i database). Dal punto di vista teorico bisognerebbe distinguere tra tabelle e viste di sistema... ma l'aspetto importante e' che sono disponibili anche una serie di oggetti che riportano lo stato del sistema: pg_settings, pg_locks, pg_stat_activity, pg_available_extensions, pg_cursors, ...
In alternativa ai system catalog, che contengono tutti i dettagli relativi all'implementazione postgres, e' possibile consultare l'information_schema che ha la struttura previata dallo standard SQL [NdA quindi per pigrizia non lo descrivo].

L'implementazione delle viste e' particolare con PostgreSQL perche' utilizza un sistema di rules per trasformare le viste nell'accesso alle tabelle cui fanno riferimento.

L'evoluzione dei due database nel tempo e' stata notevole, su questa fragrante pagina e' disponibile una cronologia dei rilasci sia per Oracle che per Postgres.

I sorgenti di PostgreSQL e gli eseguibili (forniti in diverse tipologie di pacchetti a seconda del sistema ospite) sono tutti liberamente scaricabili dal sito PostgreSQL. Molte distribuzioni unix/linux hanno gia' i pacchetti di Postgres configurati anche se spesso le versioni non sono aggiornate quanto il sito di download ufficiale che e' la fonte consigliata.
PostgreSQL e' distribuito con la PostgreSQL License che e' una licenza molto libera simile alle licenze BSD o MIT.
Sono molti i database e gli ambienti che hanno chiare origini Postgres o interfacce molto simili come: EnterpriseDB, Vertica, Aurora PostgreSQL, Netezza, Greenplum, Redshift, YugabyteDB, Everest, HadoopDB, CockroachDB, Google Spanner, AlloyDB, ...

Alcune extensioni di PostgreSQL sono particolarmente diffuse e vanno citate per la loro importanza, anche perche' ne aumentano le funzionalita' in modo particolarmente significativo tanto da poter quasi essere considerati un altro database: Postgis e TimescaleDB.

Best Practice

Come in Oracle anche in PostgreSQL vi sono una serie di best pratice che e' opportuno seguire in una configurazione di produzione.

E' assolutamente opportuno applicare il principio del minimo privilegio nella definizione degli utenti e nell'assegnazione dei grant. Tipicamente si distingue tra l'utente proprietario della struttura, le utenze in Read-Write che accedono dagli application server, e le utenze in Read-Only.
In PostgreSQL e' possibile assegnare la proprieta' di un database o di uno schema ad un utente; e' possibile definire grant specifici per ogni oggetto e/o impostare grant di default a livello di schema; e' possibile limitare gli accessi a database specifici indicando gli utenti e gli accessi di rete.

E' opportuno non utilizzare l'utente postgres per la creazione delle tabelle applicative assegnando la proprieta' all'owner dell'applicazione; e' opportuno non utilizzare il database postgres ma creare ed utilizzare database applicativi; e' opportuno non utilizzare lo schema public ma creare schemi applicativi ed impostare il search path.

Come con altri RDBMS vi sono molteplici possibilita' di configurazione ed impostazione... se si gestiscono piu' ambienti di produzione e' molto importante definire e mantenere uno standard di gestione specifico per PostgreSQL perche' ha caratteristiche architetturali differenti.

Sia Oracle che Postgres consentono di utilizzare caratteri maiuscoli e/o speciali per i nomi delle tabelle, colonne, ... entrambi i database possono utilizzare nomi particolarmente lunghi per gli oggetti... beh anche se e' possibile non fatelo!

Programmi di supporto

Oracle fornisce un'ampia collezione di tool per le piu' comuni attivita' di amministrazione dei dati. Anche PostgreSQL fornisce strumenti simili. Per rendere pratico il confronto la lista seguente riporta i piu' conosciuti programmi Oracle e come ottenere funzionalita' simili con PostgreSQL:

Stored Functions e Stored Procedures

PostgreSQL fornisce da sempre le Stored Functions ma il supporto alle Stored Procedures e' stato introdotto solo piu' di recente. Le differenze sintattiche e funzionali tra il PL/SQL di Oracle ed il PL/pgSQL PostgreSQL sono importanti:

Parole, parole, parole, ... forse e' piu' utile un esempio sulla stessa procedura in Postgres PL/pgSQL ed in Oracle PL/SQL; la procedure simula il benchmark TPC-B) ed evidenzia le differenze sintattiche che sono relativamente ridotte.

PostgreSQL Oracle 
/*
tpcb v.1.0.0
Stored Procedure PostgreSQL PL/pgSQL per la transazione TPC-B
*/

CREATE OR REPLACE PROCEDURE tpcb
 (Aidv INTEGER, Bidv INTEGER, Tidv INTEGER, deltav FLOAT, Abalancev OUT FLOAT) 
LANGUAGE plpgsql AS $$
BEGIN 
UPDATE accounts
 SET Abalance = Abalance + deltav
 WHERE Aid = Aidv;
SELECT Abalance 
 INTO Abalancev
 FROM accounts
 WHERE Aid = Aidv;
UPDATE tellers
 SET Tbalance = Tbalance + deltav 
 WHERE Tid = Tidv;
UPDATE branches
 SET Bbalance = Bbalance + deltav 
 WHERE Bid = Bidv ;

INSERT INTO history (Tid,Bid,Aid,delta,Xtime)
 VALUES (Tidv, Bidv, Aidv, deltav, now());
COMMIT ;
END $$;

/*
tpc_b v.1.0.0
Stored Procedure Oracle PL-SQL per la transazione TPC-B
*/

CREATE OR REPLACE PROCEDURE tcpb
 (Aidv INTEGER, Bidv INTEGER, Tidv INTEGER, deltav FLOAT, Abalancev OUT FLOAT) 
as
BEGIN 
UPDATE accounts
 SET Abalance = Abalance + deltav
 WHERE Aid = Aidv;
SELECT Abalance 
 INTO Abalancev
 FROM accounts
 WHERE Aid = Aidv;
UPDATE tellers
 SET Tbalance = Tbalance + deltav 
 WHERE Tid = Tidv;
UPDATE branches
 SET Bbalance = Bbalance + deltav 
 WHERE Bid = Bidv ;

INSERT INTO history(Tid,Bid,Aid,delta,Xtime)
 VALUES (Tidv, Bidv, Aidv, deltav, SYSDATE);
COMMIT ;
END;
/

Dall'esempio e' chiaro che le differenze sintattiche sono limitate e, per l'SQL, praticamente nulle. Pero' in realta' gli elementi di cui tenere conto sono altri...

La semantica delle Stored Procedures e' abbastanza simile a quella Oracle (ma cosa significa semantica?). Una cosa che manca, ma che ha dato sempre qualche grattacapo ai DBA Oracle, e' l'INVALIDATION. Le Stored Procedure non vengono compilate al momento della creazione ma al momento dell'esecuzione. Questo significa un costo maggiore al momento del primo lancio (poi sono mantenute in cache) ma una piu' semplice gestione.
Un'altra differenza e' sulla gestione delle eccezioni, dove PostgreSQL aderisce in modo piu' stretto all'ANSI, con l'utilizzo degli HANDLER.

Anche con PostgreSQL e' possibile definire trigger. I trigger lanciano una function e possono essere definiti su tabelle, viste e foreing table. I trigger possono essere eseguiti prima o dopo di una INSERT, UPDATE, DELETE, per ogni riga o una volta per statement. I trigger possono anche trattare gli statement TRUNCATE, sulle viste i trigger possono effettuare un INSTEAD OF per agire sulle tabelle sottostanti.
Insomma i trigger in PostgreSQL sono molto potenti... ma molto diversi da quelli di Oracle.

La quantita' di funzioni e packages disponibili nel PL/SQL Oracle non ha eguali in PostgreSQL. E' disponibile l'estensione orafce che implementa una serie di funzioni tipiche di Oracle ma la differenza e' comunque molto grande.

Installazione ed amministrazione

L'installazione di PostgreSQL e' terribilmente piu' semplice di quella di Oracle. E' sufficiente scaricare il software dal sito ufficiale PostgreSQL e seguire i pochi passi di installazione. In pochi minuti si ha un'installazione completa e funzionante di PostgreSQL.
Il processo postmaster PostgreSQL si comporta anche da listener ed e' quindi immediatamente disponibile per un accesso da rete sulla porta 5432.

Il DBA PostgreSQL tipicamente utilizza per l'amministrazione il psql e la GUI PG Admin. Anche un semplice ps -efa sul sistema operativo fornisce informazioni utili.

L'avvio della base dati e' molto semplice poiche' viene configurato come servizio Unix e richiede solo qualche secondo. I comandi, leggermente diversi a seconda della distribuzione unix/linux, sono: 

# systemctl [start|stop|status] postgresql@14

Gli upgrade di minor release sono molto semplici in PostgreSQL e fortemente consigliati. Un poco piu' laboriosi i passaggi di release ma comunque semplificati dal comando di upgrade. Le differenze applicative ed incompatibilita' tra una versione e l'altra, tra le versioni di client e di server, ... sono molto ridotte in PostgreSQL. Da questo punto di vista un upgrade su Oracle e' molto piu' rischioso e complesso che su PostgreSQL.

Generalmente non sono richieste pesanti attivita' amministrative su una base dati PostgreSQL. I principali controlli riguardano l'utilizzo di risorse del sistema (File System e CPU) ed il corretto operare dei backup.
La gestione delle transazioni con l'MVCC comporta l'utilizzo di spazi che debbono essere recuperati quando opportuno. Generalmente le procedure automatiche di autovacuum sono sufficienti ma in alcuni casi e' necessario un tuning specifico e/o schedulare il lancio di
  VACUUM ANALYZE
In casi di emergenza si utilizza anche l'opzione FULL, che pero' e' sospensiva poiche' richiede un lock sulle tabelle.

Configurazione

La configurazione di Oracle non e' banale. Sia che si utilizzi un initX.ora o un spfile l'uso delle opzioni possibili e la loro configurazione ottimale richiede molta esperienza.
Con PostgreSQL la configurazione e' piu' semplice. Innanzi tutto PostgreSQL funziona benissimo anche senza alcuna configurazione! E' pero vero che la configurazione di default e' minimale perche' consente di attivare postgres anche su server con risorse molto limitate. La configurazione di default non e' quindi adatta ad un'installazione di produzione: e' assolutamente necessario eseguire un tuning iniziale.

I principali parametri da impostare nel file postgresql.conf sono:

Maggiori dettagli su questo argomento si possono trovare nel documento Tuning in PostgreSQL.

Altro file molto importante per la configurazione di PostgreSQL e' il pg_hba.conf: contiene la configurazione degli accessi alla base dati. 

# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local         DATABASE  USER  METHOD  [OPTIONS]
# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

Security

Per PostgreSQL un utente e' identificato da uno username ed e' valido per tutto il database cluster.

A prima vista la gestione delle utenze e dei privilegi su Oracle e PostgreSQL appare molto differente. In realta' le differenze principali sono dovute ai default utilizzati da PostgreSQL durante l'installazione. I database creati dall'installazione sono 3: postgres e due template. Al termine dell'installazione e' definito un utente postgres senza password con tutti i diritti per l'accesso da localhost; in pratica e' l'analogo della connessione / as sysdba presente in Oracle.
Per assegnare una password, o modificarne una presente, il comando e': 

ALTER USER myuser PASSWORD 'mysecret';
Le clausole SQL GRANT e REVOKE operano in PostgreSQL in modo analogo a quello in Oracle.

E' importante sottolineare la differenza presente tra gli SCHEMA Oracle ed i Database PostgreSQL e le modalita' di gestione piu' tipiche per le base dati complesse:

Pur consentendo un analogo livello di controllo la "filosofia" PostgreSQL e' piu' semplice e non costringe ad aggiornamenti nel caso in cui vengano create nuove tabelle, oggetti, ...

La definizione delle autorizzazioni esterne si effettua con sul file pg_hba.conf che consente di indicare quali utenze accedono a quali database da quali indirizzi con quale autenticazione ed eventualmente con quale protocollo di crittografia. La configurazione e' piu' semplice e con maggiori possibilita' rispetto ad Oracle.

La gestione della crittografia delle password, cosi' come la procedura di autenticazione tra client e server in rete, sono state notevolmente migliorate in PostgreSQL nel tempo e risultano particolarmente robuste [NdA dalla versione 10+ e' disponibile l'autenticazione con SCRAM-SHA-256].
E' possibile configurare PostgreSQL per utilizzare l'SSL per il dialogo tra client e server. In questo modo il dialogo (invio degli statement SQL e dati) avviene sempre in modalita' cifrata e non in chiaro.
PostgreSQL fornisce ulteriori funzionalita' (eg. autenticazione LDAP esterna) che Oracle fornisce come parte dell'Advanced Network/Security Option.

Per la crittografazione dei dati PostgreSQL offre diverse funzioni che richiedono unicamente l'attivazione dell'estensione pgcrypto.

Il logging di PostgreSQL e' gia' sufficiente nella configurazione di default ma e' possibile attivare ulteriori livelli di logging agendo sui relativi parametri di configurazione. E' disponibile l'estensione PGAudit che consente un ulteriore controllo sui database/schema/utenti/statement da monitorare.
Con PostgreSQL e' possibile realizzare efficienti e completi log applicativi con i trigger.

L'aggiornamento delle versioni ed il rilascio di patch e' costante per PostgreSQL. Vengono indicati in modo preciso gli eventuali security fix.

La versione community di PostgreSQL non fornisce il TDE; se necessario puo' essere implementato a livello di file system sul sistema operativo.

In generale il livello di sicurezza di PostgreSQL, se correttamente configurato, e' elevato e soddisfa i piu' recenti requisiti delle autorita' di certificazione anche se inferiore a quello ottenibile con Oracle Enterprise e le sue Option. Maggiori dettagli sono riportati in questo documento.

Architettura

Sia PostgreSQL che Oracle utilizzano un'architettura complessa con parecchi differenti processi di background dedicati a task specifici, un processo per ogni sessione utente ed un'area di memoria condivisa.

PostgreSQL e' scritto principalmente in linguaggio C ed utilizza i meccanismi di IPC standard su Unix. L'accesso alla base dati e' gestito dalla libreria libpq che e' l'interfaccia di programmazione a PostgreSQL per il C ed e' anche la base per le interfacce per C++, Perl, Python, Tcl ed ECPG. Per il coordinamento dei processi vengono utilizzati alcuni semafori ed un ampio segmento di shared memory, naturalmente la dimensione di questo segmento dipende dai parametri utilizzati nella configurazione (eg. shared_buffers).

Il processo postmaster e' il processo principale che si occupa della gestione delle connessioni (e' in LISTEN sulla porta socket di default 5432) ed e' il "padre" di tutti i processi, sia di sistema (eg. wal writer) sia quelli relativi alle connessione utente. Su ogni processo utente in argv e' riportata l'origine e l'attivita' in corso... molto comodo per il DBA e per il sistemista unix! Tutti i processi girano come utente postgres ed eseguono un attach al segmento di shared memory su cui vengono mantenuti i buffer, i lock e tutte le aree condivise. Nel tempo l'architettura dei processi di PostgreSQL si e' mantenuta sempre simile anche se nelle versioni piu' recenti si sono aggiunti alcuni processi di sistema.

Un'istanza Postgres serve un'intero cluster di database, ciascuno con un suo data dictionary e strutture proprie. Da questo punto di vista e' un poco simile all'architettura multitenant introdotta in Oracle dalla versione 12.

PostgreSQL

Oracle

Architettura PostgreSQL Architettura Oracle

Il Clustered Database e' la struttura del file system che contiene i dati. All'interno vengono mantenuti i file di configurazione ($PGDATA/*.conf), i log delle transazioni (nella directory $PGDATA/pg_xlog) e le strutture interne della base dati che contengono tabelle ed indici (suddivise per database nella directory $PGDATA/base). In Postgres non sono presenti i datafile: ogni tabella ed ogni indice sono memorizzati su uno o piu' file del sistema operativo ospite. Il prefisso del nome del file tipicamente corrisponde all'OID dell'oggetto ma puo' cambiare a fronte di comandi di riorganizzazione dei dati come VACUUM FULL, REINDEX o CLUSTER; il riferimento corrente e' quello indicato da pg_class.relfilenode. PostgreSQL relation size Sono tipicamente presenti file ulteriori per la gestione degli spazi come la Free Space Map (suffisso _fsm), la Visibilty Map (suffisso _vm) e l'INITtialization fork (suffisso _init). La gestione degli spazi e' quindi completamente differente tra PostgreSQL ed Oracle.
Se la dimensione dell'oggetto supera 1G vengono creati piu' file numerati in modo progressivo. Ogni file e' organizzato come una serie di blocchi. Di default il blocco ha dimensione 8KB mentre i wal log hanno come dimensione 16MB. Le dimensioni del blocco possono essere variate ma e' necessario ricompilare PostgreSQL e generalmente tale modifica non presenta vantaggi significativi. Per identificare una riga e' possibile utilizzare il ctid che indica il blocco e la posizione all'interno del blocco di una riga [NdA il ctid in Postgres e' simile al ROWID in Oracle]. Una tupla deve essere contenuta in un solo blocco altrimenti viene utilizzato il TOAST (The Oversized-Attribute Storage Technique) che consente di memorizzare dati fino ad 1GB (230) per riga [NdA la tecnica e' relativamente semplice: quando il contenuto di una riga supera una dimensione prefissata le colonne con i datatype adeguati vengono compresse e/o spezzate in chunk di circa 2KB su una tabella TOAST associata]. Sono utilizzabili diverse organizzazioni dei dati come struttura interna per soddisfare i piu' specifici requisiti di memorizzazione (eg. storage strategy: PLAIN, MAIN, EXTERNAL, EXTENDED; CLUSTER TABLE). Tutti gli indici sono trattati come indici secondari: hanno quindi una struttura dedicata ciascuno (un file). Sono disponibili diversi tipi di indice in PostgreSQL: B-tree, Hash, GIN (Generalized Inverted Index), GiST (Generalized Search Tree), SP-GiST, BRIN, ...
Anche in PostgreSQL sono presenti i tablespace come in Oracle ma hanno un significato diverso rispetto ad Oracle: i tablespace si utilizzano quando i dati vanno ospitati su file system differenti. In pratica in Postgres un tablespace individua semplicemente una directory del sistema ospite. Utilizzare tablespace montati sullo stesso file system in PostgreSQL generalmente non e' utile, mentre utilizzare tablespace come sottodirectory una dell'altra e' un errore.

Con Oracle la parte di accesso ai dati e' un componente fondamentale del motore e lo stesso avviene con PostgreSQL. Le differenze presenti sono pero' molte perche' PostgreSQL utilizza maggiormente le strutture del sistema operativo creando file per ogni oggetto mentre Oracle gestisce tutto internamente e richiede la preallocazione delle strutture dei tablespace e dei datafile. Con PostgreSQL la file system cache del sistema operativo e' molto piu' importante che con Oracle e sul sistema ospite va lasciata libera sufficiente memoria per ottenere le migliori prestazioni.

Data Block Oracle Sia in Oracle che in Postgres i dati sono memorizzati in pagine tipicamente di 8KB [NdA in PostgreSQL e' piu' raro variare la dimensione del blocco, dal punto di vista pratico si utilizza sempre una pagina di 8KB] e con una struttura che prevede un header, una serie di puntatori alle righe, quindi lo spazio libero ed infine i dati di ogni riga inserendo le tuple nello spazio libero a partire dal basso. In Oracle viene lasciata libera una parte del blocco dati per gestire future update (PCTFREE default 10%), mentre in PostgreSQL si utilizza il FILLFACTOR (default 100% per le tabelle e 90% per gli indici). In Oracle i datafile raccolgono i dati di piu' tabelle mentre in Postgres ogni tabella ha un suo file chiamato heap.
Con entrambi i database tutte le transazioni vengono gestite in modalita' ACID. Analogo all'SCN (System Change Number) 48bit di Oracle e' LSN (Log Sequence Number) 64bit di PostgreSQL, il corrispondente dei redo log di Oracle sono i WAL (Write-Ahead Log), ... da questo punto di vista i due DB sembrano simili.

Anche se funzionalmente la gestione delle transazioni e' simile ed alcuni componenti hanno un comportamento analogo l'implementazione e' molto differente. Oracle utilizza gli UNDO segments per eseguire i rollback e per una corretta gestione del MVCC (MultiVersion Concurrency Control). Quando esegue una SELECT con uno specifico SCN Oracle salta i blocchi con SCN maggiore e li legge dai rollback segment (cfr. figura a destra). Con Oracle una SELECT di lunga durata puo' incorrere nel famigerato errore ORA-1555 snapshot too old: rollback segment too small [NdE il link fa riferimento ad una pagina piuttosto vecchia ma ancora valida].

Data Block PostgreSQL A differenza di Oracle, PostgreSQL mantiene diverse versioni delle tuple nel file della tabella modificata senza utilizzare segmenti di undo: ogni singola tupla ha una sua indicazione di visibilita' nel blocco dati.
Quando PostgreSQL inserisce una nuova riga in un blocco indica nel campo T_XMIN il TXID della transazione che ha inserito il dato e mette null nel campo T_XMAX. Le righe vengono inserite in un blocco fino a quando c'e' spazio (o e' stato raggiunto il fillfactor che pero' per default e' il 100%). Quando una riga viene cancellata da SQL in realta' viene solo aggiornato il campo T_XMAX con il TXID della transazione che ha eseguito la DELETE. Un'UPDATE in PostgreSQL viene implementata come una DELETE+INSERT. Quando viene eseguita una SELECT vengono controllati i valori T_XMIN ed T_XMAX per sapere se la transazione corrente puo' gia' vedere i dati oppure se non puo' piu' vederli.
Il vantaggio dell'implementazione di PostgreSQL e' che le SELECT non possono fallire per mancanza di dati sullo snapshot come in Oracle; lo svantaggio e' che la quantita' di spazio richiesto da una tabella con frequenti modifiche puo' crescere in modo elevato. Per evitare questo il processo di VACUUM si occupa di cancellare dai blocchi fisici i dati per cui non vi sono piu' TXID precedenti.
Naturalmente e' molto importante che non vi siano transazioni troppo a lungo nello stato Idle in transaction altrimenti il VACUUM non puo' essere eseguito e possono presentarsi problemi sia di bloat nelle tuple che di transaction ID wraparound (il TXID viene gestito come valore progressivo circolare ed e' solo a 32 bit).

All'interno di ogni database viene mantenuto un ricco Catalog che consente di controllare con query SQL gli oggetti presenti nella base dati (eg. pg_database, pg_class, pg_tables, ...) e l'andamento delle attivita' (eg. pg_locks, pg_settings, pg_statistic, pg_stats,...). Ogni database ha il suo catalogo ed e' completamente indipendente; solo gli utenti/ruoli ed i tablespace sono definiti a livello di istanza e sono comuni a tutti i database.

Ottimizzatore PostgreSQL

PostgreSQL supporta i seguenti algoritmi di join:

Inoltre PostgreSQL supporta i seguenti metodi per raccogliere i dati da una tabella:

Oracle utilizza modalita' di accesso ai dati simili ma non vi e' un'equivalenza perfetta (eg. il Bitmap Index Scan e' un algoritmo e non un tipo di indice).

Quando viene sottomesso uno statement SQL l'ottimizzatore determina il query tree da utilizzare con un algoritmo genetico basato sulle statistiche. PostgreSQL utilizza un ottimizzatore cost-based molto evoluto. L'algoritmo genetico e' utilizzato per ridurre il numero delle combinazioni dei possibili percorsi di ricerca. L'attivita' di analyze (raccolta delle statistiche necessarie all'ottimizzatore) viene schedulata in automatico come in Oracle e, se necessario, puo' essere personalizzata per ogni singola colonna.
In PostgreSQL non sono forniti gli HINT ma sono disponibili una cinquantina di diversi parametri impostabili anche a livello di una singola query per guidare l'ottimizzatore nel caso in cui l'execution plan non sia il migliore.
Sia Oracle che PostgreSQL forniscono indicazioni dettagliate sui piani d'esecuzione delle query con la clausola di EXPLAIN.

Replica e distribuzione dati

PostgreSQL offre una completa gestione della replica basata sull'applicazione dei WAL che puo' essere utilmente impiegata per fornire funzionalita' di DR ed istanze aperte in sola lettura.
In pratica viene definito il database primary su cui vengono raccolti i log delle transazioni ed uno o piu' server secondary su cui vengono ribaltate le modifiche contenute nei WAL.
La replica su PostgreSQL fisica e basata sul log delle transazioni e' ben confrontabile con il Data Guard fornito da Oracle. Entrambe le soluzioni sono disponibili da tempo con un'elevatissima affidabilita' come soluzione per il Disaster Recovery mantenendo istanze identiche all'ambiente di produzione.
PostgreSQL permette, in modo semplice ed efficiente, la replicazione di una base dati. Dalla versione 9 la replica fa parte dei componenti core di PostgreSQL ed e' molto robusta.

PostgreSQL

Oracle

Architettura replica PostgreSQL Architettura replica Oracle con Data Guard

Le strutture interne di Oracle e di PostgreSQL sono profondamente diverse tra loro ma la modalita' con cui viene garantita la durabilita' dei dati e' simile. PostgreSQL utilizza i WAL (Write-Ahead Log) su cui indica le modifiche mentre in Oracle il processo Log Writer riporta sui redo log i blocchi da aggiornare.

Entrambe le repliche sono di tipo fisico e sono basate sulla trasmissione dei log delle transazioni. Sia con Postgres che con Oracle la modalita' di trasmissione dei log avviene in streaming fornendo un RPO molto ridotto [NdA nelle prime versioni si doveva attendere il completamento di un WAL prima di trasferirlo ed applicarlo, ma non e' piu' cosi' da oltre un decennio].
La configurazione in Hot Standby di PostgreSQL consente di accedere al database secondario in modalita' di lettura, analogamente avviene su Oracle con l'Active Data Guard Option [NdA attenzione: in Oracle e' una Option]. L'utilizzo di tale modalita' aumenta in modo significativo le funzionalita' della replica dati sia dal punto di vista di continuita' operativa (per la verifica della replica, per la verifica dei lag, ...) che di bilanciamento del carico (spostando sul secondario report, analisi dati, ...).
Con Oracle e' possibile impostare tre differenti protection mode: MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION} ed ha una varieta' di configurazione molto ampia. Postgres consente invece la scelta tra la modalita' asincrona e quella sincrona. Ma le modalita' di default di entrambe le basi dati sono praticamente analoghe (privilegiando le performance e la disponibilita' del servizio rispetto ad un applicazione sincrona dei risultati).

Con Oracle Primary e Standby hanno strutture dati identiche bit per bit e lo stesso avviene con PostgreSQL.
Su Oracle e' disponibile l'Option Active Dataguard che consente di utilizzare in lettura il database di Standby. Con PostgreSQL la funzionalita' analoga e' chiamata Hot Standby ed e' attiva per default.

La soluzione Oracle Data Guard e' utilizzata soprattutto per il Disaster Recovery... La replicazione su PostgreSQL e' sfruttata anche per disporre di un numero elevato di server su cui eseguire statement di SELECT o per backup. Possono essere facilmente configurate decine di repliche di una base dati PostgreSQL permettendo una notevole scalabilita' orizzontale (per le operazioni in sola lettura). In realta' la differenza di utilizzo non e' tanto tecnica quanto dovuta a ragioni economiche: anche Oracle fornisce l'accesso in lettura e la cascading replication ma ogni istanza richiede un licensing.

Postgres non fornisce un analogo all'opzione RAC di Oracle. La replica di Postgres pero' e' molto piu' sfruttabile in architetture di produzione perche' di piu' semplice gestione ed aperta in lettura senza costi aggiuntivi (se non quelli del sistema ospite).
E' importante ricordare che il Data Guard di Oracle richiede l'Enterprise Edition, tutti i nodi configurati debbono essere licenziati e l'Active Data Guard per aprire un'istanza in lettura e' una Option ovvero un'estensione a pagamento.

La Logical Replication e' una nuova funzionalita' presente in PostgreSQL dalla versione 10. La replica logica si differenzia dalla replica tradizionale PostgreSQL sia dal punto di vista tecnico che funzionale: la replica fisica riguarda l'intero database cluster e garantisce l'HA (High Availability), mentre la replica logica e' eseguita su tabelle specifiche ed e' orientata alla distribuzione dei dati. Nonostante questa differenza la configurazione di base e l'implementazione sono simili: la replica logica e' basta sugli stessi processi utilizzati dalla replica fisica e sull'analisi del contenuto dei WAL. Maggiori informazioni sulla replica logica si trovano in questa pagina.
Vi e' qualche analogia con la replica logica di Oracle Data Guard... ma in questo caso le differenze sono significative anche in termini di evoluzioni future.

Simili ai database link di Oracle sono l'extension dblink e, sopratutto, i FDW ovvero i Foreign Data Wrappers sono stati introdotti nella versione PostgreSQL 9.1. Una descrizione piu' completa dei FDW si trova in questa pagina.

Cluster

Oracle RAC Architecture

PostgreSQL puo' essere installato su bare metal, su ambienti virtualizzati, su container, on premises, on cloud, ... Tecnicamente non vi sono grandi differenze con Oracle anche se Oracle ha un footprint maggiore quindi richiede sistemi ospite con una quantita' di memoria maggiore. La grande differenza e' nel licensing: PostgreSQL e' un Open Source con una licenza molto libera, non ha alcun costo di licenza e l'eventuale supporto puo' essere acquistato a parte.

La configurazione di PostgreSQL e di Oracle con cluster Active-Passive a livello di sistema operativo non presenta differenze significative. Dati e configurazioni vanno posti su dischi associati al servizio cluster, mentre l'installazione del motore puo' essere mantenuta sui dischi di sistema (tipico su PostgreSQL) o su un file system a parte (tipico su Oracle). Molti prodotti di cluster forniscono gia' script o agenti predisposti per i servizi cluster dei due RDBMS.

PostgreSQL Pgpool-II Architecture Con Oracle RAC vengono acceduti dischi condivisi contemporaneamente da tutte le istanze del cluster. Particolari meccanismi di locking distribuito e di cache fusion consentono di avere il massimo delle prestazioni. In caso di caduta di uno dei nodi del RAC gli altri nodi continuano ad erogare il servizio.

Su PostgreSQL non e' disponibile una architettura analoga ad Oracle RAC.
Le alternative in PostgreSQL si basano sulla streaming replication, eventualmente configurata in modo sincrono, controllata da un load balancer come Pgpool-II. Con una configurazione corretta si possono ottenere RPO =0 o near 0 ed RTO sotto il minuto. E' utile sottolineare che queste configurazioni sono in shared-nothing quindi proteggono anche da problemi sullo storage che invece in Oracle RAC e' condiviso ed e' quindi un potenziale SPOF.

Interessanti evoluzioni sull'HA di PostgreSQL sono quelle che sfruttano la tecnica dei container e kubernets. Anche se tecnicamente sarebbe possibile anche con Oracle con le attuali politiche di licensing non e' una scelta frequente [NdA e' disponibile una versione non di produzione di OraOperator].
Nella figura seguente le applicazioni utilizzano l'indirizzo di un load balancer che instrada le connessioni all'istanza postgres primary. Le istanze standby sono allineate con replica in modalita' sincrona in modo da garantire la consistenza: PostgreSQL on Kubernetes

Programmazione

Con entrambe le basi dati e' fortemente consigliabile utilizzare interattivamente l'SQL per controllare le prestazioni degli statement utilizzati nei programmi. E' opportuno anche utilizzare l'EXPLAIN per controllare il percorso scelto dall'ottimizzatore.

Entrambi i database sono precisi e rigidi nei controlli sulla sintassi. Forse un poco di piu' Postgres che richiede in qualche caso il casting dei datatype per accettare alcune conversioni. Postgres non richiede la clausola FROM mentre Oracle utilizza il trucco FROM DUAL.

A PostgreSQL si accede spesso con una connessione ODBC o JDBC. I driver Java di Postgres sono molto efficienti e sono disponibili le interfacce per i principali linguaggi di programmazione.
Dal punto di vista funzionale non vi sono differenze significative tra PostgreSQL ed Oracle.
Il tempo richiesto per la connessione ad un DB PostgreSQL e' generalmente inferiore a quanto richiesto per una connessione ad Oracle. Con Oracle l'utilizzo del connection pooling e' quasi un obbligo per applicazioni web mentre con PostgreSQL l'esigenza e' meno sentita. Sono comunque disponibili diversi strumenti di pooling delle connessioni per PostgreSQL (eg. PgBouncer).

Compatibilita' Oracle 100%

In questo paragrafo vengono riassunti i comandi e le opzioni che rendono il comportamento di PostgreSQL il piu' vicino possibile a quello di Oracle. Naturalmente raggiungere il 100% di compatibilita' non e' possibile, e neanche ragionevole, ma... il titolo mi piaceva!

Un punto importante e' sicuramente l'utilizzo delle transazioni. Sia Oracle che PostgreSQL utilizzano l'isolation level READ COMMITTED (altre modalita' sono configurabili ma utilizzate di rado). Gli utenti Oracle sono abituati ad effettuare il COMMIT esplicito mentre in PostgreSQL e' impostato l'autocommit ad ogni statement. In psql, si puo' impostare "\set AUTOCOMMIT off" mentre in JDBC si richiama java.sql.Connection.setAutoCommit(boolean) per richiedere il commit esplicito come in Oracle.

Le utenze di default di PostgreSQL sono differenti da quelle di Oracle, anche se e' banale impostare quanto segue... 


CREATE USER sys WITH LOGIN SUPERUSER PASSWORD 'change_on_install';
CREATE USER system WITH LOGIN SUPERUSER PASSWORD 'manager';

CREATE USER scott WITH LOGIN PASSWORD 'tiger';
CREATE SCHEMA scott  AUTHORIZATION scott;
ALTER ROLE scott SET search_path = scott,public;
Cosi' si hanno gli utenti cui si e' abituati con Oracle e non solo l'utente postgres [NdA non fatelo! Sono le password piu' famigerate di Oracle].

L'estensione orafce implementa una serie di funzioni, datatype, operatori tipici di Oracle, fornisce una serie di packages e per finire crea la fondamentale tabella DUAL.

Convertire una base dati da Oracle a PostgreSQL e' un'attivita' che richiede diversi passi. Innanzi tutto va disegnata la nuova base dati con datatype corrispondenti a quelli Oracle. Quindi vanno preparati programmi di scarico/carico (eg. sqlplus+COPY), ...
In realta' conviene utilizzare tool specifici di cui e' disponibile un'ampia scelta tra Open Source e commerciali. I miei preferiti? ora2pg e Kettle o Pentaho Data Integration. In questo modo la conversione e' molto piu' semplice, automatizata e veloce.

Vi sono alcune versioni commerciali di PostgreSQL che forniscono una compatibilita' molto piu' elevata per Oracle rispetto alla versione Community. EDB distribuisce Postgres Advanced Server (EPAS: EDB Postgres Advanced Server) che e' sicuramente il fork piu' noto e diffuso. La configurazione e' molto semplice perche' richiede un solo parametro e poi l'utilizzo della sintassi Oracle e' disponibile da psql o per le applicazioni: 

#INITDBOPT="--noredwood-compat"

show db_dialect;

select * from dual; 
select sysdate;

Con quanto visto in questo capitolo e' possibile rendere PostgreSQL molto vicino all'RDBMS Oracle. Ma siete sicuri di volerlo fare? I due DB sono diversi ed hanno funzionalita' differenti. Cercare di rendere i due database simili in ogni aspetto introduce piu' problemi e limitazioni di quanti ne vengano risolti: meglio prendere il meglio da ogni tecnologia senza pretendere che sia esattamente uguale ad altre.

Evoluzione e futuro

Come per tutti i prodotti vivi l'evoluzione su Oracle e PostgreSQL e' continua. Entrambi hanno una lunga storia che inizia nel secolo scorso e sono cambiati in modo notevole rispetto alle prime releases disponibili.

Sicuramente ora un trend importante e' l'utilizzo in Cloud dei servizi di database ed entrambi gli RDBMS sono ben presenti, anche se PostgreSQL e' disponibile su molte piu' piattaforme grazie alla licenza Open Source molto libera. Molti sono anche i servizi in cloud che utilizzano fork di PostgreSQL o interfacce compatibili, l'elenco e' molto lungo, tra i piu' noti: Amazon Aurora PostgreSQL, Citus, PolarDB, Yugabyte, CockroachDB, AlloyDB, Greenplum, TimescaleDB, Redshift, ...

Il documento Your Server stinks! riporta i principali rilasci di Oracle e di PostgreSQL.

Per il futuro... vedremo!

Titolo: PostgreSQL 4 Oracle DBAs
Livello: Avanzato (3/5)
Data: 14 Febbraio 2022
Versione: 1.0.2 - 15 Agosto 2023
Autore: mail [AT] meo.bogliolo.name