Introduzione a PostgreSQL

Introduzione a PostgreSQL(il piu' avanzato database OpenSource al mondo)

Ing. Luca Ferrari, [email protected]

Italian PostgreSQL Users Group ITPug

Introduzione a PostgreSQL e ai database – Luca Ferrari 2 di 139

SynopsisSynopsisQuesta presentazione vuole introdurre PostgreSQL, il piu' avanzato database OpenSource al mondo. Verranno trattati diversi temi, fra i quali i principali sono:

La comunità internazionale e nazionale

La storia del progetto, lo stato attuale e gli sviluppi futuri

Installazione e utilizzo di base del sistema

Accenni al funzionamento interno

Transazioni

Window Functions e CTE

Cool Features

Partitioning


DatabaseDatabase

Un database è un contenitore di dati (generalmente strutturati).

Perché occorre usare un database?Principio di delega: il database si occupa solo dei dati, e li gestisce nel modo migliore indipendentemente dalle applicazioni che ne hanno bisogno.

Fruibilità: usando un database i dati vengono svincolati dalla applicazione, e quindi i dati possono essere accessibili da diverse applicazioni.


Vantaggi nell'uso di un databaseVantaggi nell'uso di un databaseIl database si occupa della gestione degli accessi allo spazio di memorizzazione (disco)Il database organizza i dati in modo da garantire il miglior accesso agli stessiIl database mantiene i dati coerenti (non significa corretti, ma non sbagliati)Il database gestisce gli accessi concorrentiIl database consente backup, ripristino, replicazione, …

Non si reinventa la ruota!


Tipologie di databaseTipologie di database

I database possono essere classificati in base alle loro caratteristiche in diversi insiemi ortogonali:

Relational Database

Object Database

Embedded (es. BerkelyDB, Derby)

Server (es. MySQL, PostgreSQL)

Document Oriented (CouchDB, Haddop)

...


Database maggiormente affermatiDatabase maggiormente affermati

I database maggiormenti affermati sono quelli di tipo relazionale, con tuple disposte per righe (esistono anche database per colonne!).

Il linguaggio (dichiarativo) standard per l'interazione con tali database è lo Structured Query Language (SQL).


Database relazionaliDatabase relazionaliI dati sono correlati fra loro sulla base del loro valore.I Relational Database (RDBMS) sfruttano le foreign keys (chiavi esterne) per imporre i vincoli di integrità referenziali.Ovviamente una chiave esterna deve identificare univocamente una tupla nell'entità referenziata.


Introduzione a PostgreSQL


Introduzione a PostgreSQLIntroduzione a PostgreSQLPostgreSQL è un ORDBMS (Object Relational Database Management System) di classe enterprise

E' un prodotto Open-Source mantenuto da un team mondiale di sviluppatori ed espertiE' conforme agli standard SQL (SQL 2003) supportandone caratteristiche sintattiche e semantiche


StoriaStoriaIl prof. Michael Stonebreaker dell'University of Berkely crea un database denominato Ingres. Lascia poi l'università per creare un'azienda che si occupi del business Ingres. Successivamente torna all'università con l'intenzione di creare un nuovo database, sfruttando quanto imparato dal progetto Ingres.

19861986: il prof. Stonebreaker è a capo di un progetto finanziato da DARPA e ARO per la creazione di POSTGRES;

19881988: la prima versione di POSTGRES;19901990: POSTGRES v.219911991: POSTGRES v.319941994: Andrew Yu e Jolly Chen aggiungono il supporto per l’SQL; il pacchetto

viene rilasciato con il nome di POSTGRES95;19961996: continua lo sviluppo, si cambia nome in POSTGRESQL v.6;…20062006: viene rilasciata la versione 8.220072007: primo PGDay in Italia (international)20082008: viene rilasciata la versione 8.3 secondo PGDay in Italia (international) - ITPUG20092009: viene rilasciata la versione 8.4 terzo PGDay in Italia (National)20102010: viene rilasciata la versione 9.0


La nascita del La nascita del Core TeamCore TeamDate: Mon, 08 Jul 1996 22:12:190400 (EDT) From: "Marc G. Fournier" <[email protected]>Subject: [PG95]: Developers interested in improving PG95?To: Postgres 95 Users <[email protected]>Hi... A while back, there was talk of a TODO list and development moving forward on Postgres95 ... at which point in time I volunteered to put up a cvs archive and sup server so that making updates (and getting at the "newest source code") was easier to do...... Just got the sup server up and running, and for those that are familiar with sup, the following should work (ie. I can access the sup server from my machines using this):

Date: Mon, 08 Jul 1996 22:12:190400 (EDT) From: "Marc G. Fournier" <[email protected]>Subject: [PG95]: Developers interested in improving PG95?To: Postgres 95 Users <[email protected]>Hi... A while back, there was talk of a TODO list and development moving forward on Postgres95 ... at which point in time I volunteered to put up a cvs archive and sup server so that making updates (and getting at the "newest source code") was easier to do...... Just got the sup server up and running, and for those that are familiar with sup, the following should work (ie. I can access the sup server from my machines using this):

Thomas Lockhart (ritirato), Jan Wieck, Tom Lane, Marc FournierVadim Mikheev, Bruce Momjian


Caratteristiche principaliCaratteristiche principaliModello client-server: i dati sono gestiti in modo centralizzato (server) e messi a disposizione di più fruitori (client)Avanzato supporto per lo standard SQL2003: garantisce la portabilità di applicazioniConcorrenza e Multi-utenza: supporta connessioni e query multiple contemporaneamenteIntegrità referenziale: garantisce la coerenza dei dati rispetto alle business rulesTransazioni: esecuzione di più istruzioni in modo coerente (ACID rules)Sub-query: possibilità di innestare le queryIndici: diverse modalità di accesso rapido ai dati (anche per controllo di coerenza)Replica: possibilità di sincronizzare due o più istanze fra loro


Caratteristiche principaliCaratteristiche principaliQuery planner: le query sono analizzate per individuare il miglior percorso di accesso ai datiTriggers: reazione agli eventi di inserimento, cancellazione, aggiornamento di tupleRules: possibilità di ordinare la riscrittura di queryStored procedures: funzioni memorizzate presso il serverEreditarietà: relazione padre-figlio fra le tabelleSequenze: produzione di valori auto-incrementatiTipi di dato: possibilità di definire un tipo di dato personalizzatoTablespaces: controllo sullo storage fisicoPartizionamento: possiblità di separare i dati e organizzarli secondo le esigenze fisiche di storageConnettività da diversi linguaggi: Perl, Java, C, Python, Ruby, PHP, ...Linguaggi integrati: PL/Perl, PL/Java, PL/PGSQL, …Common Table Expressions: CTE (clausola WITH)


Licenza BSDlikeLicenza BSDlikePostgreSQL Database Management System(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 19962010, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.


Un problema di diffusione?Un problema di diffusione?

It is a simple, permissive noncopyleft free software license,

compatible with the GNU GPL.

We urge you not to use the original BSD license for software you write.


http://www.postgresql.orghttp://www.postgresql.org


http://www.planetpostgresql.orghttp://www.planetpostgresql.orgEsiste anche il planet italiano: http://www.planetpostgresql.it !


http://pgfoundry.orghttp://pgfoundry.org


http://www.itpug.orghttp://www.itpug.org

Users' Group Italiano di PostgreSQLFornisce un frontend per le aziende e le istituzioni che vogliano usare PostgreSQL.

Si occupa della diffusione di PostgreSQL e della sua ideologia.Definisce una serie di chapter regionali per la localizzazione sul territorio.


http://www.pgday.ithttp://www.pgday.it


PostgreSQL Weekly NewsPostgreSQL Weekly News


CommunityCommunity


Installazione,concetti base

e primo utilizzo


Numeri di versioneNumeri di versione

8.4.4

Il primo numero è un major number e indica il “settore di mercato” in cui si colloca la release.Ad esempio la 7.x.x era denominata nocrash, la 8.x.x era quella con il port Windows.

Il primo numero è un major number e indica il “settore di mercato” in cui si colloca la release.Ad esempio la 7.x.x era denominata nocrash, la 8.x.x era quella con il port Windows.

Il secondo numero è un major number e indica il numero annuale di release.Deve essere combinato con il primo numero per fare riferimento ad una release specifica.

Il secondo numero è un major number e indica il numero annuale di release.Deve essere combinato con il primo numero per fare riferimento ad una release specifica.

Il terzo numero è un minor number e indica il patch level della release.

Il terzo numero è un minor number e indica il patch level della release.

Richiede un nuovo initdb.Non richiede initdb.


InstallazioneInstallazioneSu Linux distribuzioni Debian-like: aptget install postgresql aptget install postgresqldoc aptget install postgresqlcontribPotrebbe essere necessario inizializzare la directory che conterrà i dati con initdb.

Su *BSD: cd /usr/ports/databases/postgresql84server && make install

E' ovviamente possibile compilare dai sorgenti, per avere un ambiente ottimizzato.

Il demone PostgreSQL gestisce un cluster di database, ovvero un insieme di più database

possono risiedere sullo stesso host e possono essere gestiti dallo stesso processo.


template1, template0template1, template0Appena installato il cluster mette a disposizione un database vuoto, usato come template per la creazione di altri database: template1.

Tutte le caratteristiche di template1 possono essere modificate dall'utente, e saranno riflesse in ogni nuovo database creato.

Il database template0 rappresenta una copia di sicurezza di template1.


Connessione al database: psqlConnessione al database: psqlSi può usare il client testuale psql:

In modo analogo ad una shell Unix, il simbolo # identifica un prompt di amministratore, mentre un carattere > un prompt di utente normale.


Password prompt degli strumenti testualiPassword prompt degli strumenti testualiTutti gli strumenti testuali (psql, pg_dump, pg_dumpall, …) leggono un file particolare $HOME/.pgpass che puo' contenere l'associazione server, database, username e password.

<server>:<port>:<database>:<username>:<password>dbserver:5432:raydb:luca:XXXdbserver:5432:ordinidb:luca:XXXbackupserver:5432:*:luca:XXX

Se viene trovata l'associazione, allora la password non viene chiesta al prompt dei comandi (comodo per l'automazione di script).


Connessione al database: pgadmin3Connessione al database: pgadmin3pgadmin3 è un client grafico basato sulle librerie wxWidgets, grazie alle quali funziona su piattaforma Linux, Unix, Microsoft, Apple


Connessione al database: phpgadminConnessione al database: phpgadminClient Web basato su PHP


Creazione di un nuovo utenteCreazione di un nuovo utente Una volta collegati al database template1 è possibile creare un

nuovo utente tramite CREATE USER

E' possibile creare l'utente da una shell usando il comando create_user


Creazione di un databaseCreazione di un databaseUna volta collegati al database template1 è possibile creare un nuovo database con CREATE DATABASE.


Creazione di un databaseCreazione di un databaseSi può anche usare lo script createdb direttamente da shell.


Creazione di una semplice tabellaCreazione di una semplice tabellaCi si collega al database linuxdaydb e si impartiscono i comandi necessari: CREATE TABLE

Si noti la creazione dell'indice sulla chiave primaria e sul vincolo unique.

Viene inoltre creata una sequenza in automatico per il campo serial.


Popolamento del databasePopolamento del database


IntrospezioneIntrospezioneI comandi \ di psql consentono di effettuare introspezione sulla struttura del database. Ad esempio:

\d fornisce l'elenco degli oggetti nel database \d nome_tabella fornisce la struttura di una

tabella \df fornisce l'elenco delle stored procedure \df nome_funzione fornisce il codice sorgente

della funzione


Introspezione: esempioIntrospezione: esempio


Cluster di databaseCluster di databasePostgreSQL è un demone che gestische un cluster di database.E' possibile vedere i database che una istanza gestisce mediante i comandi \list (da prompt psql) o flag -l da linea di comando psql.


postgresql.confpostgresql.confIl file postgresql.conf contiene parametri fondamentali per la configurazione del server. Alcuni interessanti sono:

listen_address = 'localhost' {*, ip address}specifica per quali interfacce il server deve accettare connessioni

log_statement = 'none' {none, all, ddl, mod}consente di abilitare il logging dei comandi SQL eseguiti dal server, utile per il debugging o il monitoring dell'attività del server

shared_buffers = 24MB {almeno 16k a connessione}indica la memoria disponibile per PostgreSQL in cui conservare le pagine dati

work_mem = 1MB è la memoria usata per il sorting (clausole ORDER BY)


postgresql.conf: ottimizzatore e costipostgresql.conf: ottimizzatore e costi# Definiscono quali metodi l'ottimizzatore può usare per l'accesso ai dati# (ad esempio si potrebbe voler impedire di usare il seqscan...)enable_bitmapscan = onenable_hashagg = onenable_hashjoin = onenable_indexscan = onenable_mergejoin = onenable_nestloop = onenable_seqscan = onenable_sort = onenable_tidscan = on

# Costi (relativi) di accesso alle operazioni. Sostanzialmente sono # espressi in una scala arbitraria, si noti che il costo della pagina# è superiore a quello di un'operazione di CPU (es. sorting)seq_page_cost = 1.0 # measured on an arbitrary scalerandom_page_cost = 4.0 # same scale as abovecpu_tuple_cost = 0.01 # same scale as abovecpu_index_tuple_cost = 0.005 # same scale as above


pg_hba.confpg_hba.confIl file pg_hba.conf (host base access) contiene le regole per l'accesso al server PostgreSQL da parte dei client della rete. Occorre specificare il database, la maschera di rete dei client (o l'indirizzo ip) e il metodo di accesso (trust, md5,...):

# TYPE DATABASE USER CIDRADDRESS METHOD

local all all trust

host all all 127.0.0.1/32 md5

host linuxdaydb linux 192.168.1.0/24 md5

host all all ::1/128 md5


Monitorare i processiMonitorare i processiPostgreSQL usa uno schemi a processi; oltre al server (chiamato postmaster) esiste un processo per ogni connessione utente. Unitamente vi sono dei processi per il WAL, per il BGWRITER e per lo STATISTIC COLLECTOR.


Monitorare le query in esecuzioneMonitorare le query in esecuzioneE' possibile interrogare il catalogo di sistema per visualizzare le attività in corso nel database:

In alternativa ogni processo backend collegato ad un client compare nella lista processi con il nome di postgres e ha una descrizione che rappresenta l'attività del processo in quel momento.


Statistiche usate dall'ottimizzatoreStatistiche usate dall'ottimizzatoreLa pseudo-tabella pg_stats contiene le informazioni sui valori che compaiono nelle colonne di una tabella. L'indice valuta questi valori, il loro istogramma e decide se vale la pena usare l'indice per la clausola di selezione indicata.


Creazione di tipi personalizzatiCreazione di tipi personalizzatiE' possibile creare dei tipi di dato personalizzati, in modo da rendere più strutturata la definizione di una tabella. I tipi possono essere usati in ogni oggetto del database.


Creazione di arrayCreazione di arrayE' possibile inserire in una tabella un array di tipi (anche personalizzati) usando il costrutto []. Gli array possono anche diventare multi-dimensionali.

L'indice dell'array parte sempre da 1!


Selezione/update dei valori di un arraySelezione/update dei valori di un array


Funzionamento interno


Layout fisicoLayout fisicoTutti gli oggetti del database si trovano nella directory $PGDATA. Al suo interno vi sono diverse sotto-directory:

base: contiene tutti i database e gli oggetti del database

pg_clog: contiene lo stato delle transazioni in corso pg_xlog: contiene i log WAL pg_twophase: contiene le informazioni sul 2-ph commit pg_tblspc: contiene dei link ai tablespace definiti


Layout fisico: identificare gli oggettiLayout fisico: identificare gli oggettiDal catalogo di sistema è possibile identificare i vari oggetti su disco:


Funzionamento internoFunzionamento internoAd ogni connessione il processo postmaster effettua una fork e crea un processo postgres (backend) per gestire la connessione.

Il parser analizza la query sintatticamente

Il Traffic Cop decide se il comando è standard o di manutenzione (es. vacuum).

Il rewriter considera le regole e riscrive la query corretta (es. query su una vista). L'ottimizzatore, tramite un algoritmo genetico, calcola il percorso migliore.

L'executor esegue la query.


Shared MemoryShared MemoryTutti i processi postgres condividono una zona di memoria (shared memory) che contiene le pagine dati caricate.


Transazioni


Livelli di isolamento delle transazioniLivelli di isolamento delle transazioni

Livello di isolamento

Possibile Possibile Possibile NOImpossibile Possibile Possibile SIImpossibile Impossibile Possibile NOImpossibile Impossibile Impossibile SI

Dirty Read Nonrepeatable Read Phantom Read Supportato in PostgreSQL

Read uncommitted Read committed Repeatable read Serializable

Dirty Read: si leggono dati non ancora committati da un'altra transazioneNonrepeatable read: dati letti in precedenza sono ora modificati dal commit di un'altra transazione.Phantom Read: il set di dati che soddisfa una certa condizione è ora cambiato.

Read Committed: ogni comando vede uno snapshot dei dati già committati prima dell'inizio del comando stesso. Non sono visibili tuple committate da transazioni concorrenti se il co0mmit avviene durante il comando stesso. Nel caso di update concorrente la transazione attende la fine delle altre transazionie rivaluta la riga prima di procedere.Serializable: ogni transazione agisce emulando la serializzazione, ossia vede solo i dati che hanno subito commit all'inizio della transazione. Lo snapshot in questo caso non cambia per nessun comando della transazione.

Read Committed isolamento a livello di comando→Serializable isolamento a livello di transazione→

PostgreSQL supporta i quattro livelli di isolamento standard, ma internamente implementa tutto con solo due livelli: serializable e read committed (il default). La ragione di questa scelta implementativa è da ricercarsi nell'uso di MVCC!


TransazioniTransazioniLe transazioni sono attivate con i classici comandi BEGIN e COMMIT/ROLLBACK; le transazioni possono essere innestate (sottotransazioni).Anche i SAVEPOINT sono implementati come sottotransazioni.

Livello Base (gestione di un singolo comando)StartTransactionCommandCommitTransactionCommandAbortTransactionCommand

Livello Utente (gestione BEGIN/END)BeginTransactionBlockEndTransactionBlockUserAbortTransactionBlockDefineSavepoint/RollbackSavepointReleaseSavepoint

Livello Gestione (backend)StartTransaction/CommitTransaction/AbortTransactionStartSubTransaction/CommitSubTransaction/AbortSubTransaction


Transazione e Savepoint: esempioTransazione e Savepoint: esempio

Lo stato di una transazione (committed, in progress, aborted, subcommitted) vienememorizzato su disco all'interno della directory pg_clog.


Numerazione delle transazioniNumerazione delle transazioni

Le transazioni seguono la numerazione delle tuple: ogni transazione è identificata da un numero intero positivo e progressivo denominato XID.Lo XID è un intero a 4 byte (32 bit).L'età di una transazione è stabilita in modo relativo rispetto ad un'altra transazione: uno XID minore indica una transazione iniziata prima e quindi piu' vecchia.


WraparoundWraparoundIn un sistema con molte transazioni si puo' assistere ad un wraparound: due transazioni finiscono con l'avere lo stesso XID, e quindi non possono piu' essere comparate!Considerando un bit di segno, questa eventualità si verifica ogni 231 transazioni, superato il quale si ha una inversione di segno e le transazioni piu' vecchie appaiono come piu' giovani!Siccome le transazioni “nel futuro” non possono essere visibili (READ_COMMITTED), al wraparound si ottiene la perdita dei dati!Vacuum analizza l'età di ogni tupla (XMIN), se questo è maggiore di una soglia vicina al wraparound (min_freeze_age) allora resetta lo XMIN ad un valore di default FROZENXID.FROZENXID è l'id piu' vecchio presente nel sistema (valore numerico 3), così da apparire sempre nel passato e non essere piu' sottoposto a Vacuum.


Quanto manca al wraparound?Quanto manca al wraparound?

La funzione age(..) fornisce l'età di una tupla, ovvero quante transazioni fa la tupla è stata aggiornata.Il parametro min_freeze_age determina dopo quante transazioni si deve forzare un Vacuum.


Transazioni e log: pg_clogTransazioni e log: pg_clogIl log pg_clog memorizza (in modo permanente) lo stato delle transazioni avviate (ed eventualmente completate) nel sistema. Le transazioni possono essere in uno dei seguenti quattro stati (quindi richiedendo 2 bit):

In progress (transazione avviata ma non completata)

Committed (transazione completata con successo)

Aborted (transazione completata con rollback forzato o manuale)

Subcommitted (sottotransazione/savepoint passato con successo)

I dati di pg_clog sono mappate in memoria, così da accelerare l'accesso ai dati. L'unico caso in cui si richiede di rileggere lo stato da disco è per una

transazione idle da diverso tempo.


Pagina pg_clogPagina pg_clog

pg_clog in dettagliopg_clog in dettaglio

01 1100 00 01 1100 00 01 1100 00

Le transazioni sono memorizzate secondo l'ordine dato dal loro XID (identificativo del numero di transazione). Ne consegue che, sapendo la dimensione della pagina dati, sapendo che il blocco di una singola transazione è lungo 2 bit, e sapendo l'id della transazione è possibile risalire al dato circa il suo stato. Si tenga presente che in un byte ci sono le informazioni di 4 transazioni (1 byte = 2 bit * 4).

n_pagina_pg_clog = ceiling( XID / ( page_size * 4 ) )byte_interno_alla_pagina = round( XID mod( page_size * 4 ) / 4 )

offset_nel_byte = ( XID mod 4 ) 1

Qualora lo stato di transazione non sia già in cache, la lettura da disco (funzione TransactionLogFetch) mette in cache il dato. Solitamente si tiene solo uno stato in cache (ossia una sola porzione di log), viceversa la scrittura dello stato di una transazione (functione TransactionLogUpdate) può accettare anche piu' XID contemporaneamente ( ad esempio transazioni annidate).


LockingLockingPostgreSQL supporta diversi tipi di locking:

Share: prevalentemente comandi di lettura (che possono anche aggiornare)

ACCESS SHARE (pura lettura)

ROW SHARE (lettura/modifica, es. SELECT FOR UPDATE)

Exclusive: scrittura o alterazione della struttura ACCESS EXCLUSIVE (alterazione, ALTER TABLE)

ROW EXCLUSIVE (scrittura, INSERT/UPDATE)


Multi Version Concurrency Control


MVCCMVCCPostgreSQL utilizza il Multi-Version Concurrency Control per implementare l'isolamento nelle transazioni.

Concettualmente ogni transazione vede uno snapshot del database e agisce su quello snapshot.

Lo snapshot è costruito considerando solo le transazioni precedenti la corrente che hanno effettuato il commit.

Presuppone un livello di isolamento read-committed.

E' concettualmente simile alle tecniche COW (Copy On Write, come ad esempio ZFS).


MVCC: implementazioneMVCC: implementazioneInternamente il database deve registrare non solo le informazioni utente (i dati veri e propri) ma anche i metadati per la gestione dell'MVCC.

Ogni tupla viene estesa con 4 campi particolari, usati per i controlli del sistema MVCC:

xmin: indica il minimo valore di transazione che ha accesso ai dati (sostanzialmente la transazione che ha creato la tupla)

xmax: indica il massimo valore di transazione che ha accesso ai dati (sostanzialmente l'id dell'ultima transazione che ha cancellato/modificato la tupla)

cmin: indica il minimo valore di comando che ha accesso ai dati

cmax: indica il massimo valore di transazione che ha accesso ai dati


MVCC: esempioMVCC: esempio

Sostanzialmente il sistema controlla se la tupla è ancora visibile per l'id di transazione corrente

(xmin <= transaction_id <= xmax)&& (xmin committed) && (! (xmax committed || locked))

e se è visibile per il comando corrente all'interno delle transazione

(cmin <= command_id <= cmax)&& (xmin committed || xmin == current_transaction)

&& (! (xmax committed || locked))

Il test è in realtà più complesso !(lock, xmax nullo se abort, commit)


MVCC: approccio visualeMVCC: approccio visuale

descrizione docente10 1pg0118 20 2pg0221 3pg03

xmin cmin xmax cmax talkspk talksidPostgreSQL Luca FerrariPostgreSQL/Python Luca FerrariPostgreSQL/JDBC Luca Ferrari

La riga con xmin e xmax impostati ha subito una delete (o un update) e quindi non è più valida. Tutte le transazioni successive alla numero 20 non potranno vedere quella riga, che però esiste ancora nel database.

Problema: il database rimane pieno di righe non più valide!


VacuumVacuumIl sistema esegue (manualmente o automaticamente) un comando di utilità, denominato vacuum, che controlla una tabella o un database alla ricerca delle tuple che non sono più visibili da nessuna transazione (ossia che hanno un xmax inferiore al prossimo id di transazione) e le cancella. In questo modo i dati si ricompattano e il database riduce il suo spazio.


VacuumVacuumL'esecuzione di vacuum è un'operazione pesante, perché il sistema deve acquisire un lock esclusivo sull'oggetto.Vacuum può essere usato per eliminare le tuple espirate (vacuum full), oppure per aggiornare le statistiche di sistema (vacuum analyze).Ogni volta che vacuum esegue, lo xmin di tutte le tuple visibili viene resettato (freeze) così da prevenire problemi ad un successivo wrap-around.Può essere abilitato di default nel file postgresql.conf:

autovacuum = on


Vacuum: esecuzioneVacuum: esecuzione


Informazioni di visibilitàInformazioni di visibilità

Un'operazione di Vacuum sposta e compatta le tuple, arrivando anche a cancellare quelle espirate.Come ci si comporta con l'indice che puntava a quelle tuple?

L'indice non contiene il riferimento alla tupla ma solo alla pagina dati → le tuple cancellate (espirate) non comportano modifiche all'indice

Le tuple spostate da Vacuum comportano un nuovo aggiornamento dell'indice.


HOT – Heap Only TuplesHOT – Heap Only Tuples

A partire dalla versione 8.3 il sistema consente anche il microvacuum, ovvero un vacuum limitato ad una sola pagina dati.Quando viene aggiornata una tupla, e l'aggiornamento non necessità di una modifica all'indice (ossia si modificano solo le colonne fuori dagli indici):

La tupla vecchia viene marcata come expired;

La nuova tupla viene inserita;

La tupla vecchia contiene un puntatore alla tupla nuova.

Seguendo i link si trova rapidamente la tupla piu' ricente nella pagina dati.


MicroVacuumMicroVacuum

Il sistema può operare un vacuum ridotto (microvacuum) all'interno della pagina dati, rimuovendo le tuple expired e i relativi link alla tupla piu' recente.In questo modo la pagina dati rimane sempre “ordinata” e non c'è bisogno di grossi carichi per un vacuum globale.


WALWALIl Write Ahead Log mantiene le informazioni sull'andamento delle transazioni. E' fisicamente archiviato all'interno della directory pg_xlog, e a differenza dei log in pg_clog non mantiene solo lo stato finale di una transazione, ma anche le informazioni (progressive) sui dati “toccati”.

La teoria vuole che il WAL sia scritto prima dei dati, ma questo produrrebbe un sovraccarico di scritture.Per ovviare al problema PostgreSQL divide i WAL log in segments (LSN). A questo punto i log WAL sono mantenuti in memoria, e ogni volta che si scrive una pagina di dati “sporca” si fa precedere la scrittura dalla scrittura di tanti segmenti di log fino al raggiungimento del numero LSN corrispondente alla pagina.Analogamente, durante un recovery tutte le transazioni con XID inferiori al LSN che si sta attualmente analizzando sono da considerarsi stabili (ripristinate completamente).


WAL: funzionamento in breveWAL: funzionamento in breveLa scrittura del WAL avviene secondo lo schema seguente:1) acquisizione di un PIN e del lock sulla pagina dati da modificare (pin indica che la pagina non deve essere scartata dalla memoria)2) modifica della pagina di dati3) la pagina viene marcata come dirty4) si richiama xloginsert per inserire la pagina di log e si imposta il LSN nella pagina dati al valore risultante dall'insert

Le pagine WAL contengono modifiche in modo incrementale (delta).Problema: ci si fida delle scritture del sistema operativo? Il sistema mantiene dei checkpoints, ossia ogni n transazioni forza una scrittura di una pagina dati completa nel WAL: alla prima modifica di una pagina sotto la condizione

LSN < checkpoint

si scrive la pagina dati completa nel WAL.


Log in azione!


Come viene trattata una query?Come viene trattata una query?Nel file di configurazione è possibile abilitare dei parametri per visualizzare come una singola query viene trattata.

La query viene tradotta in un AST (Abstract Syntax Tree)Vengono risolti gli alias e la query viene riscrittaLa query viene passata all'ottimizzatore per la creazione del piano di esecuzione


Vedere come viene trattata una queryVedere come viene trattata una query20100606 22:19:46 CEST STATEMENT: SELECT description FROM skill;20100606 22:19:46 CEST LOG: rewritten parse tree:20100606 22:19:46 CEST DETAIL: ( {QUERY :commandType 1 :intoClause <> :hasAggs false :hasWindowFuncs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :cteList <> :rtable ( {RTE :alias <> :eref {ALIAS :aliasname skill :colnames ("skillpk" "skillid" "description" "skillfamilypk") } :rtekind 0 :relid 21220 :inh true :inFromCl true :requiredPerms 2


Pagine dati


Pagine datiPagine dati

Le pagine dati rappresentano una astrazione rispetto al blocco dati memorizzato su discoUna pagina dati ha sempre la stessa struttura in memoriaAll'interno di una pagina dati la singola tupla è acceduta per mezzo di un offset, ma indirettamente per consentire una migliore gestione.E' come se le foglie dell'indice fossere

contenuto nella pagina dati stessa!


Struttura di una pagina datiStruttura di una pagina datiPage Header Data

pd_lsnpd_tlipd_lowerpd_upperpd_specialpd_pagesize_versionpd_linp[ ]

linp0 linp1 linp2 linp3

linp4 linp5 linpN...

tupleN

tupleN1

tuple0

.

.

.

Special space

La pagina contiene una serie di puntatori lineari (linear pointer – linp) che indirizzano le tuple contenute nella pagina stessa. L'offset della tupla non identifica la tupla stessa, ma il puntatore linp. In questo modo è possibile spostare la tupla (es. compattazione dello spazio) senza doverne cambiare l'offset.

pd_lower indica il punto “minimo” di inizio tuple (ossia dove finiscono i linp), pd_upper il punto “massimo” di fine tuple. Il range delle tuple è quindi identificato da pd_lower e pd_upper.Le tuple sono organizzate a stack: ogni nuovo tupla viene inserita in fondo subito prima delle altre (crescita verso l'alto) mentre il linp viene inserito all'inizio dopo gli altri (crescita verso l'alto).


pd_lsn: sincronizzazione con il WALpd_lsn: sincronizzazione con il WAL

Ogni pagina dati include un campo importante: pd_lsn (Log Sequence Number).Tale campo contiene il valore di segmento del log WAL corrispondente all'ultima transazione che ha “toccato” la pagina.Questo numero serve a permettere la gestione delle transazioni: la pagina dati può essere scaricata su disco solo dopo che il log WAL è stato scritto almeno fino al segmento indicato dal pd_lsn.Così facendo, il bgwriter è sicuro che in caso di crash la transazione sia stata scritta nel WAL e la pagina possa essere “ricreata”.


Gestione dei bufferGestione dei buffer

Le pagine dati vengono caricate in memoria condivisa in zone dette bufferOgni processo utente può accedere ai buffer (e quindi alle pagine dati) rispettando alcune regole per evitare corse criticheSiccome portare una pagina da disco ad un buffer richiede tempo, il sistema contiene un flag IO_IN_PROGRESS per indicare che la pagina/buffer sarà presto disponibile


Locking dei bufferLocking dei bufferOgni buffer contiene due astrazioni utili per il locking, la loro combinazione permette la gestione di piu' casi:

Buffer Content Lock: è un lock esplicito di tipo esclusivo, lavora a livello di tupla

Page Pin: è un contatore del numero di processi che stanno “agendo” sulla pagina dati, lavora a livello di pagina

I possibili casi sono:Uno scan e un'analisi della visibilità di una tupla richiede un pin e un lock

Un Vacuum richiede un pin esclusivo (pin = 1) e un lock esclusivo sulla tupla da cancellare. E' possibile che altri processi prendano il pin nel frattempo (pin > 1) ma non che “vedano” la tupla o possano iniziare lo scan su di essa

E' possibile rilasciare il lock mantenendo il pin (tupla interessata da altro scan). La tupla non può essere rimossa (pin > 1) e se la sua visibilità cambia ciò non rappresenta un problema poiché la visibilità è legata allo scan

Cambiare i dati di visibilità XMIN/XMAX richiede un pin e un lock esclusivo, per evitare che altri processi possano fare scan sulla tupla


Ottenere un nuovo bufferOttenere un nuovo buffer

Ottenere un nuovo buffer risulta facile nel caso ci sia spazio di memoria, mentre richiede di scartare delle pagine qualora la memoria condivisa sia piena.In una simile situazione si procede come segue:

Si sceglie un buffer fra quelli disponibili seguendo il puntatore NextVictimBuf (algoritmo clock-skew).

Se il buffer ha un pin oppure è stato appena rimosso il suo pin (contatore unpin > 0) allora non può essere scelto, si passa oltre.

Se il buffer non ha pin e ha contatore di unpin pari a zero (pin = unpin = 0) allora può essere scaricato: entra in gioco il BackgroundWriter

Il BackgroundWriter acquisisce un pin e scrive le pagine su disco


Point In Time Recovery


Point In Time Recovery (PITR)Point In Time Recovery (PITR)PostgreSQL può sfruttare i log WAL per effettuare un backup del sistema ad un determinato momento. L'idea è quella di archiviare i log e di indicare al sistema che deve effettuare nuovamente le transazioni archiviate.

0) impostazione del comando di archivio dei log (postgresql.conf):archive_command = 'cp i %p /backup/wal//%f '1) informare il database che si inizia il backup:SELECT pg_start_backup('backup_pitr');2) archiviazione della directory PGDATA (tar, ...)3) informare il database che il backup è finito:SELECT pg_stop_backup();


PITR: ripristinoPITR: ripristinoUna volta in possesso dei log è possibile ordinare al database di fare un re-do delle transazioni:

1) stop del cluster2) pulizia delle directory del cluster, in particolare pg_xlog3) copia dei log dentro a pg_xlog4) creazione di un file recovery.conf nella rootdel cluster:restore_command = ’cp /backup/wal/%f "%p"’recovery_target_time=’20070616 10:33:59’5) avvio del cluster


PITR: esempioPITR: esempio

Si crea una grossa tabella,si effettua il backup PITRe si cancella la tabella.

Si ferma PostgreSQL, si cancellanogli xlog dal backup ripristinato e

si crea il file recovery.conf (che saràpoi eliminato automaticamente).


PITR: esempioPITR: esempio

Al riavvio il databasetrova il file recovery.confe inizia a ripetere le transazionipresenti nei WAL.

Il risultato è che si trovanotutte le tabelle che erano state

cancellate!


Indici


IndiciIndiciPostgreSQL consente l'utilizzo di diversi tipi di indice, anche lossy (che necessitano di un recheck). Il costo di accesso tramite indice viene effettuato mediante la stima del numero di tuple restituite e dell'operatore applicato, assieme alle statistiche sulle colonne coinvolte

costo_indice = costo_seq * num_page_index // costo di acceso indice

+ (cpu_index_cost + index_tuple_cost) * num_index_tuple

// costo di analisi delle tuple indice


IndiciIndiciB-tree: sono il default, vengono usati per query di uguaglianza e range

con operatori <, =, >. Su indice multi-colonna solo l'uguaglianza limita le pagine di indice, gli altri operatori vengono rivalutati al volo. Viene usato nel caso di pattern matching solo all'inizio del valore!

Bitmap: si costruisce una mappa di bit dove per ogni tupla si indica con un bit se soddisfa (1) o non soddisfa (0) la condizione. Le mappe possono poi essere unite con operatori logici per ridurre il set di risultati velocemente.

Hash: utili solo per uguaglianze stretteGIST: Generalized Index Search Tree, fornisce una interfaccia per

l'integrazione di indici user-defined (es. indici R-Tree, per similarità, ecc.). L'utente deve fornire l'implementazione di 7 funzioni C (consistent, union, picksplit, compress, decompress, same, penalty)

GiN: indice inverso per la ricerca full-text (su colonne tsvector). E' più veloce che un generico GiST e non è lossy. Utilizzano una coppia <chiave, posting_list> con i posting_list pointer che puntano ai documenti che contengono la chiave. L'idea è che conviene fare la comparazione sulla chiave che sul documento.


Indici in PostgreSQLIndici in PostgreSQLPostgreSQL memorizza gli indici in modo disgiunto dalla relazione alla quale puntano (indici “secondari”)Un indice viene memorizzato e gestito come una normale relazione, e quindi è

Presente nel catalogo di sistema

Acceduto e gestito dal buffer manager

Ogni indice memorizza una coppia <key, TIDs>

dove i Tuple Identifier sono coppie<numero_blocco_dati, offset_linp>

per recuperare la pagina dati e la tupla all'interno della pagina dati


Indici e MVCCIndici e MVCCA causa di MVCC di una tupla ne possono esistere piu' versioni (teoricamente una valida e le altre espirate o che stanno espirando)L'indice memorizza la chiave comune a tutte le versioni di una tupla e i TID di ogni versione di una tuplaDi conseguenza l'indice sa come recuperare ogni versione temporale di una tupla!

L'indice non contiene le informazioni di validità di una tupla (XMIN, XMAX, CMIN, CMAX)!


Indici e MVCC (2)Indici e MVCC (2)Perché un indice non contiene le informazioni di visibilità di una tupla?

Una tupla potrebbe essere recuperata tramite scansione sequenziale o un altro indice, e quindi le informazioni di visibilità andrebbero replicate per ogni indice (spreco di spazio)!Piu' indici possono essere combinati assieme per ridurre il numero di tuple da recuperare, indipendentemente dalla loro visibilità, e quindi è bene controllare la visibilità delle tuple trovate solo alla fine!

La separazione tra indice e informazioni di visibilità può creare alcuni disallineamenti fra indici e tuple:

INSERT → la tupla viene inserita prima nella pagina dati e poi nell'indice. Ciò significa che non è immediatamente visibile, ma ciò è coerente con il livello di isolamento READ_COMMITTED

VACUUM → la tupla viene cancellata prima dall'indice, in modo da non essere piu' visibile ad altri processi (resta la scansione sequenziale)


Indici e MVCC (3)Indici e MVCC (3)La separazione fra indice e tuple dati puo' causare una corsa critica nel passaggio (fly) dall'indice al dato (ad esempio se la tupla viene modificata/rimossa da un altro processo)

Quando si accede ad una pagina indice si aggiunge un PIN alla pagina dell'indice, così da notificare gli altri processi che le tuple indicizzate non possono essere modificate (si ricordi che Vacuum cancella prima la tupla dall'indice)


Cool Featurescosa si può fare con PostgreSQL


UtilitiesUtilities pg_dump: effettua un dump di un database on-line,

producendo un file SQL (eventualmente compresso) che contiene i comandi per la creazione del database, dello schema e dei dati

pg_restore: ricostruisce un database, uno schema o i dati partendo da un file di dump

vacuumdb: effettua il vacuum su un database reindex: ricostruisce gli indici copy, \copy: bulk loading di grosse moli di dati (il

default usato da pg_dump) Initdb: inizializza una directory da usare per il cluster

(crea le cartelle pg_clog, pg_xlog, base, ...)


Cool Features: replicazioneCool Features: replicazioneSlony: sistema master-

slaves

Pgpool: replica le modifiche a tutti i nodi, e le interrogazioni a solo un nodo

Pgcluster: analizza un cluster e replica le modifiche


Cool Features: DBILinkCool Features: DBILinkSviluppato principalmente da David Fetter, implementa parzialmente la specifica di SQL:2003 SQL/MED (Management of External Data).Consente di accedere da un database PostgreSQL a data che risiede in un altro database (anche di tipo diverso) in modo trasparente.Sostanzialmente si effettua una query verso una normale tabella PostgreSQL, che in realtà è collegata ad una fonte dati remota (es. database Oracle).

Si basa su Perl, e viene eseguito da PL/Perl.


Cool Features: PostGISCool Features: PostGISPostGIS è un contrib che consente l'utilizzo di sistemi GIS.E' conforme alle specifiche OpenGIS "Simple Features Specification for SQL" .Esiste anche un client basato su Eclipse (uDig)


Cool Features: Full Text SearchCool Features: Full Text SearchFTS utilizza i tsvector come tipi di dati sui quali fare le ricerche. Le ricerche vengono specificate tramite l'operatore @@.


Cool Features: TOASTCool Features: TOAST

TOAST (The Oversized-Attribute Storage Technique) è una tecnica per la memorizzazione trasparente di dati che non possono essere contenuti in una singola riga.L'idea è di dividere il dato “largo” in pezzi (chunks) che vengono memorizzati off-line in una tabella separata (TOAST-table). Questo viene fatto ogni volta che la compressione del dato non permette la sua memorizzazione in linea.Il sistema ricollega all'esigenza i dati TOAST-ati.TOAST può memorizzare i dati con quattro specifiche:

PLAIN: nessuna compressione o off-line, dati non TOAST-abili EXTENDED: compressione e off-line (default) EXTERNAL: no compressione, off-line MAIN: compressione, no off-line (finché c'è spazio)


Partitioning(tramite ereditarietà

e trigger)


PartitioningPartitioningIl partitioning è una tecnica per la divisione fisica dei dati pur mantenendoli logicamente uniti.L'idea è quella di posizionare dati differenti su supporti fisici (o posizioni) differenti, mantenendo l'accesso ai dati trasparente alla suddivisione dei dati stessi.

Ad esempio si immagini di avere una tabella che riassume i movimenti di magazzino. Siccome tale tabella può diventare molto grande nel tempo, è opportuno separare fisicamente i dati in base, ad esempio, alla data cui questi fanno riferimento.


Partitioning: esempio di tabellaPartitioning: esempio di tabellaCREATE TABLE movmag ( movmagpk serial NOT NULL, data date, descrizione character varying DEFAULT 20, qta real )

CREATE TABLE movmag ( movmagpk serial NOT NULL, data date, descrizione character varying DEFAULT 20, qta real )

CREATE TABLE movmag2007 ( Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass), Ereditato: data date, Ereditato: descrizione character varying DEFAULT 20, Ereditato: qta real,

CONSTRAINT date_2007 CHECK (data >= '20070101'::date AND data <= '20071231'::date)) INHERITS (movmag)

CREATE TABLE movmag2007 ( Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass), Ereditato: data date, Ereditato: descrizione character varying DEFAULT 20, Ereditato: qta real,

CONSTRAINT date_2007 CHECK (data >= '20070101'::date AND data <= '20071231'::date)) INHERITS (movmag)

CREATE TABLE movmag2008 ( Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass), Ereditato: data date, Ereditato: descrizione character varying DEFAULT 20, Ereditato: qta real, CONSTRAINT date_2008 CHECK (data >= '20080101'::date AND data <= '20081231'::date)) INHERITS (movmag)

CREATE TABLE movmag2008 ( Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass), Ereditato: data date, Ereditato: descrizione character varying DEFAULT 20, Ereditato: qta real, CONSTRAINT date_2008 CHECK (data >= '20080101'::date AND data <= '20081231'::date)) INHERITS (movmag)


Partitioning con un triggerPartitioning con un triggerCREATE OR REPLACE FUNCTION _inserimento_movimento_magazzino() RETURNS trigger AS$BODY$BEGIN

controllo la data di inserimento IF NEW.data >= '01012007'::date AND NEW.data <= '31122007' THEN

RAISE INFO 'Inserimento in tabella movimento 2007';INSERT INTO movmag2007(data, descrizione, qta) VALUES (NEW.data, NEW.descrizione, NEW.qta); non inserisco nulla nella tabella originaleRETURN NULL;

ELSIF NEW.data >= '01012008'::date AND NEW.data <= '31122008'::date THENRAISE INFO 'Inserimento in tabella movimento 2008';INSERT INTO movmag2008(data, descrizione, qta)VALUES (NEW.data, NEW.descrizione, NEW.qta); non inserisco nulla nella tabella originaria RETURN NULL;

ELSE inserimento nella tabella trash, errore! RAISE WARNING 'Inserimento nella tabella trash, controllare i vincoli!';INSERT INTO movmag_trash(data, descrizione, qta)VALUES (NEW.data, NEW.descrizione, NEW.qta); non inserisco nulla nella tabella originaria RETURN NULL;

END IF;

END;$BODY$LANGUAGE 'plpgsql' VOLATILE;


Funzionamento del triggerFunzionamento del triggerCREATE TRIGGER tr_inserimento BEFORE INSERT ON movmag FOR EACH ROW EXECUTE PROCEDURE _inserimento_movimento_magazzino();


Partitioning con una rulePartitioning con una ruleCREATE OR REPLACE RULE inserimento_2007 AS ON INSERT TO movmag WHERE new.data >= '20070101'::date AND new.data <= '20071231'::date DO INSTEAD INSERT INTO movmag2007 (data, descrizione, qta) VALUES (new.data, new.descrizione, new.qta);

CREATE OR REPLACE RULE inserimento_2008 AS ON INSERT TO movmag WHERE new.data >= '20080101'::date AND new.data <= '20081231'::date DO INSTEAD INSERT INTO movmag2008 (data, descrizione, qta) VALUES (new.data, new.descrizione, new.qta);

In questo caso il trigger non scatta!


Partitioning: accesso ai datiPartitioning: accesso ai datiQualunque sia la scelta di partitioning effettuata, l'accesso ai dati è trasparente.Una volta interrogata la tabella movmag PostgreSQL va a cercare fra i dati di tutte le figlie:

Vanno generati indici sulle varie tabelle per consentire un accesso ai dati veloce!


Explain:capire come

lavora l'ottimizzatore


EXPLAINEXPLAINExplain è un comando SQL che consente di analizzare le scelte dell'ottimizzatore per l'esecuzione di una query.

Explain si basa sulle statistiche raccolte durante il funzionamento del database; le statistiche possono essere aggiornate anche eseguendo explain analyze.

Leggere l'output di explain è un'arte!


Explain: primo esempioExplain: primo esempio

Il piano di esecuzione indica: Una scansione sequenziale sulla tabella (Seq Scan) La dimensione delle tuple ritornate (152 byte) Il numero di righe ritornate (440 !) Il costo iniziale (0.00) e finale del piano (14.40)

C'è qualche cosa che non va!


Explain: aggiornare le statisticheExplain: aggiornare le statisticheLa tabella talks è stata appena creata, e le sue statistiche non sono aggiornate: l'ottimizzatore ipotizza che ci siano 440 righe al suo interno! Rifacendo il piano di esecuzione con le statistiche aggiornate si ha un valore migliore:


Explain per grosse quantità dati Explain per grosse quantità dati Eliminando i vincoli di unicità è possibile duplicare rapidamente le tuple all'interno di una tabella, arrivando fino a 6M tuple.


Explain: perché non usa l'indice?Explain: perché non usa l'indice?


Explain: l'ottimizzatore è furbo!Explain: l'ottimizzatore è furbo!La query è una banale SELECT * e quindi si sta chiedendo al database di restituire tutte le tuple della tabella. Non ha senso quindi usare un indice, perché tanto tutte le tuple devono essere restituite senza ordinamento e senza condizioni.La cosa cambia se si inseriscono clausole WHERE o ORDER BY.


Explain: utilizzo dell'indiceExplain: utilizzo dell'indice

1) Si parte dal bitmap index scan (perché la condizione è di uguaglianza), quella che ha costo inziiale 0.002) Dalla scansione in indice, si estraggono circa 2M di tuple (vuote – senza dimensione – si prendono i puntatori)3) Si ricontrolla la condizione su ogni tupla delle pagine dati e si estraggono le 2M tuple di lunghezza 32 byte


Explain: ulteriore esempioExplain: ulteriore esempioSi supponga di aver aggiunto una colonna durata (tipo real) e di averla riempita con dati random.

1) Si parte dal bitmap index scan sulla clausola durata > 02) Si applica un filtro in memoria sulla clausola durata < 0.53) Si effettua un nuovo scan, questa volta delle tuple in memoria4) Si effettua un ordinamento delle tuple


Explain: ulteriore esempioExplain: ulteriore esempio

1) Si parte dal sequential scan (perché non esiste indice sfruttabile), quella che ha costo inziiale 0.002) Dalla scansione in indice, si estraggono circa 4M di tuple (complete di dati)3) Si effettua un sort in memoria delle tuple restituite


Window Functions


Window FunctionsWindow Functions

Una window function è una funzione simile ad una di aggregazione capace però di operare su una partizione dei dati.In sostanza per ogni tupla in uscita da una query la window function è capace di operare una aggregazione su un insieme ristretto di tuple collegate a quella corrente.Si utilizza la parola chiave OVER per indicare la partizione sulla quale agire (che puo' essere lasciata vuota ad indicare tutte le tuple).


Window Functions: esempioWindow Functions: esempio

Si vuole contare, per ogni famiglia di competenze, quante competenze ci sono:

SELECT sf.skillfamilyid, sf.description, count(s.skillid) OVER (PARTITION BY s.skillfamilypk) FROM skillfamily sf JOIN skill s ON s.skillfamilypk = sf.skillfamilypk

In sostanza viene eseguito un “group-by” su s.skillfamilypk, e su questo si opera con la funzione di aggregazione count().


Common Table Expressions


Commont Table ExpressionsCommont Table ExpressionsLe Common Table Expressions (CTE) consento la scrittura di query complesse in modo piu' semplice.

Le CTE sono state introdotte con la versione 8.4 di PostgreSQL, anche se erano già presenti in altri motori relazionali.

I vantaggi nell'uso delle CTE sono: Query complesse scritte in modo piu' semplice e sintetico (simile per questo alle viste)

Maggiore velocità di esecuzione (l'ottimizzatore comprende meglio la query)


CTE: concetti generali e sintassiCTE: concetti generali e sintassi

Una query che usa la CTE può essere divisa in due parti principali:

La query principale

Una query secondaria

WITH temporary_table_name AS ( <query secondaria> )<query principale>

WITH temporary_table_name AS ( <query secondaria> )<query principale>


CTE: considerazioniCTE: considerazioniIl sistema genera una tabella temporanea con i risultati della query secondariaLa query principale puo' fare riferimento alla tabella temporaneaQuando la query principale termina la tabella temporanea viene distrutta automaticamente

WITH random_number_table AS ( SELECT random() AS number )SELECT 'now'::text::timestamp AS timestamp, random_number_table.number;

WITH random_number_table AS ( SELECT random() AS number )SELECT 'now'::text::timestamp AS timestamp, random_number_table.number;

1

1

2

2

3

3


CTE: un esempioCTE: un esempioDate le tabelle skill e skillfamily, collegate dalla chiave esterna skillfamilypk, la seguente query estrae tutte le skill che appartengono ad una famiglia con piu' di 3 skill:

select s.skillid, s.description from skill s where s.skillfamilypk in (select ss.skillfamilypk from skill ss group by ss.skillfamilypk having count(ss.skillid) > 3);

Mediante CTE la query può essere riscritta come:

with family_3 as (select skillfamilypk from skill group by skillfamilypk having count(skillpk) > 3)select s.skillid, s.description from skill s JOIN family_3 ON s.skillfamilypk = family_3.skillfamilypk;

skillfamily

skillskillfamilypk


Senza CTE cosa succede?Senza CTE cosa succede?

explain select s.skillid, s.description from skill s where s.skillfamilypk in (select ss.skillfamilypk from skill ss group by ss.skillfamilypk having count(ss.skillid) > 3); QUERY PLAN Hash Semi Join (cost=7.55..16.40 rows=246 width=50) Hash Cond: (s.skillfamilypk = ss.skillfamilypk) > Seq Scan on skill s (cost=0.00..5.46 rows=246 width=54) > Hash (cost=7.27..7.27 rows=23 width=4) > HashAggregate (cost=6.69..7.04 rows=23 width=13) Filter: (count(ss.skillid) > 3) > Seq Scan on skill ss (cost=0.00..5.46 rows=246 width=13)(7 rows)


Con CTE cosa succede?Con CTE cosa succede?

explain with family_3 as (select skillfamilypk from skill group by skillfamilypk having count(skillpk) > 3)select s.skillid, s.description from skill s JOIN family_3 ON s.skillfamilypk = family_3.skillfamilypk; QUERY PLAN Hash Join (cost=7.78..16.63 rows=246 width=50) Hash Cond: (s.skillfamilypk = family_3.skillfamilypk) CTE family_3 > HashAggregate (cost=6.69..7.04 rows=23 width=8) Filter: (count(skill.skillpk) > 3) > Seq Scan on skill (cost=0.00..5.46 rows=246 width=8) > Seq Scan on skill s (cost=0.00..5.46 rows=246 width=54) > Hash (cost=0.46..0.46 rows=23 width=4) > CTE Scan on family_3 (cost=0.00..0.46 rows=23 width=4)(9 rows)


Senza CTE: un altro esempioSenza CTE: un altro esempioexplain select c.* from cronologia c where c.visibile='t' and c.id_cronologia = (select max(cc.id_cronologia) from cronologia cc where c.id_elemento = cc.id_elemento);

QUERY PLAN Seq Scan on cronologia c (cost=0.00..526745.55 rows=149 width=39) Filter: (visibile AND (id_cronologia = (SubPlan 1))) SubPlan 1 > Aggregate (cost=14.59..14.60 rows=1 width=4) > Index Scan using idx_id_elemento_data_creazione on cronologia cc (cost=0.00..14.58 rows=3 width=4) Index Cond: ($0 = id_elemento)(6 rows)


CTE: un altro esempioCTE: un altro esempioexplain with max_cronologia as (select c.* from cronologia c where c.visibile='t' and c.id_cronologia = (select max(id_cronologia) from cronologia where id_elemento = c.id_elemento))

select * from max_cronologia;

QUERY PLAN CTE Scan on max_cronologia (cost=526745.55..526748.53 rows=149 width=65) CTE max_cronologia > Seq Scan on cronologia c (cost=0.00..526745.55 rows=149 width=39) Filter: (visibile AND (id_cronologia = (SubPlan 1))) SubPlan 1 > Aggregate (cost=14.59..14.60 rows=1 width=4) > Index Scan using idx_id_elemento_data_creazione on cronologia (cost=0.00..14.58 rows=3 width=4) Index Cond: (id_elemento = $0)(8 rows)

Il costo finale è lo stesso!Non sempre le CTE danno prestazioni migliori!


Con CTE: un altro esempioCon CTE: un altro esempioexplain with visibile_cronologia as ( select c.id_cronologia, c.id_elemento from cronologia c where c.visibile='t' ), max_cronologia as (select c.* from cronologia c where c.id_cronologia = (select max(v.id_cronologia) from visibile_cronologia v where v.id_elemento = c.id_elemento) )

select * from max_cronologia;

QUERY PLAN CTE Scan on max_cronologia (cost=24144719.85..24144723.45 rows=180 width=65) CTE visibile_cronologia > Seq Scan on cronologia c (cost=0.00..672.15 rows=29777 width=8) Filter: visibile CTE max_cronologia > Seq Scan on cronologia c (cost=0.00..24144047.70 rows=180 width=39) Filter: (id_cronologia = (SubPlan 2)) SubPlan 2 > Aggregate (cost=670.36..670.37 rows=1 width=4) > CTE Scan on visibile_cronologia v (cost=0.00..669.98 rows=149 width=4) Filter: (id_elemento = $1)

In questo caso il costo è addirittura peggiore!


CTE ricorsiveCTE ricorsive

Le CTE prevedono l'uso ricorsivo:Deve essere valutato prima un termine non ricorsivo

Si congiunge il termine non ricorsivo con quello ricorsivo (ad esempio UNION ALL)

Si scrive il termine ricorsivo che fa riferimento alla tabella temporanea stessa

In sostanza la tabella temporanea può fare riferimento a se stessa!

C'è il rischio di loop infiniti!


CTE ricorsive: esempioCTE ricorsive: esempioWITH RECURSIVE counter_table AS ( SELECT 1 AS initial_counter UNION ALL SELECT ct.initial_counter + 1 FROM counter_table ct WHERE ct.initial_counter < 100 )SELECT * FROM counter_table;

Termine non ricorsivo

Termine ricorsivo: fa riferimento alla

tabella temporanea!

Congiunzione

Termine per evitare loop infiniti!

CTE Scan on counter_table (cost=2.95..3.57 rows=31 width=4) CTE counter_table > Recursive Union (cost=0.00..2.95 rows=31 width=4) > Result (cost=0.00..0.01 rows=1 width=0) > WorkTable Scan on counter_table ct (cost=0.00..0.23 rows=3 width=4) Filter: (ct.initial_counter < 100)


CTE: come funziona la ricorsione?CTE: come funziona la ricorsione?

Si valuta la query temporaneaIl sistema valuta il termine non ricorsivo (ossia quello che può essere evaluato alla prima “passata”)

Si costruisce una working table vuota

Si effettua l'unione del termine non ricorsivo inserendo il risultato di ogni interazione nella working table

Quando la ricorsione finisce si valuta la query principale.


CTE ricorsive: ulteriore esempioCTE ricorsive: ulteriore esempio

Data la seguente tabella gerarchica possiamo estrarre i dati dei “fratelli”:select * from hierarchy ;

pk | parentpk

+

1 |

2 |

3 | 1

4 | 2

5 | 1

6 | 2

(6 rows)

WITH RECURSIVE hierarchy_exploded AS (SELECT pk, parentpk, ARRAY[pk] AS brothers FROM hierarchy WHERE parentpk IS NULLUNION ALLSELECT h.pk, h.parentpk, he.brothers || ARRAY[h2.pk]FROM hierarchy h JOIN hierarchy_exploded he ON h.parentpk = he.pk JOIN hierarchy h2 ON h2.parentpk = h.parentpk )SELECT * FROM hierarchy_exploded;

WITH RECURSIVE hierarchy_exploded AS (SELECT pk, parentpk, ARRAY[pk] AS brothers FROM hierarchy WHERE parentpk IS NULLUNION ALLSELECT h.pk, h.parentpk, he.brothers || ARRAY[h2.pk]FROM hierarchy h JOIN hierarchy_exploded he ON h.parentpk = he.pk JOIN hierarchy h2 ON h2.parentpk = h.parentpk )SELECT * FROM hierarchy_exploded;


ConclusioniConclusioni

PostgreSQL è un database maturo, scalabile, affidabile e soprattutto di classe enterprise.Il suo utilizzo è raccomandato quando l'integrità dei dati è molto importante, o meglio quando perdere una singola tupla può compromettere tutto il database.E' un ottimo database di ricerca, grazie alla possibilità di inserire oggetti user-defined al suo interno.E' un'ottima piattaforma per prendere confidenza con database enterprise.

Introduzione a PostgreSQL

Documents

Transcript of Introduzione a PostgreSQL