Introduzione a PostgreSQL

Introduzione a PostgreSQL
(il piu' avanzato database OpenSource al mondo)
Ing. Luca Ferrari, PhD
[email protected]
Italian PostgreSQL Users Group
ITPug
Synopsis
Questa 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
2 di 139 Database
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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
3 di 139 Vantaggi nell'uso di un database
Il 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 stessi
Il database mantiene i dati coerenti (non significa
corretti, ma non sbagliati)
Il database gestisce gli accessi concorrenti
Il database consente backup, ripristino,
replicazione, …
Non si reinventa la ruota!
Introduzione a PostgreSQL e ai database – Luca Ferrari
4 di 139 Tipologie 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)
...
Introduzione a PostgreSQL e ai database – Luca Ferrari
5 di 139 Database 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).
Introduzione a PostgreSQL e ai database – Luca Ferrari
6 di 139 Database relazionali
I 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 e ai database – Luca Ferrari
7 di 139 Introduzione a PostgreSQL
Introduzione a PostgreSQL e ai database – Luca Ferrari
8 di 139 Introduzione a PostgreSQL
PostgreSQL è un ORDBMS (Object Relational
Database
Management
System)
di
classe
enterprise
E' un prodotto Open-Source mantenuto da un
team mondiale di sviluppatori ed esperti
E' conforme agli standard SQL (SQL 2003)
supportandone
caratteristiche
sintattiche
e
semantiche
Introduzione a PostgreSQL e ai database – Luca Ferrari
9 di 139 Storia
Il 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.
1986:
1986 il prof. Stonebreaker è a capo di un progetto finanziato da DARPA e
ARO per la creazione di POSTGRES;
1988:
1988 la prima versione di POSTGRES;
1990:
1990 POSTGRES v.2
1991:
1991 POSTGRES v.3
1994:
1994 Andrew Yu e Jolly Chen aggiungono il supporto per l’SQL; il pacchetto
viene rilasciato con il nome di POSTGRES95;
1996:
1996 continua lo sviluppo, si cambia nome in POSTGRESQL v.6;
…
2006:
2006 viene rilasciata la versione 8.2
2007:
2007 primo PGDay in Italia (international)
2008:
2008 viene rilasciata la versione 8.3
secondo PGDay in Italia (international) - ITPUG
2009:
2009 viene rilasciata la versione 8.4
terzo PGDay in Italia (National)
2010:
2010 viene rilasciata la versione 9.0
Introduzione a PostgreSQL e ai database – Luca Ferrari
10 di 139 La nascita del Core Team
La nascita del Core Team
Date: Mon, 08 Jul 1996 22:12:19­0400 (EDT) Date: Mon, 08 Jul 1996 22:12:19­0400 (EDT) From: "Marc G. Fournier" <[email protected]>
From: "Marc G. Fournier" <[email protected]>
Subject: [PG95]: Developers interested in improving PG95?
Subject: [PG95]: Developers interested in improving PG95?
To: Postgres 95 Users <[email protected]>
To: Postgres 95 Users <[email protected]>
Hi... A while back, there was talk of a TODO list and development moving forward on 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 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...
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 ... 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):
following should work (ie. I can access the sup server from my machines using this):
Thomas Lockhart (ritirato), Jan Wieck, Tom Lane, Marc Fournier
Vadim Mikheev, Bruce Momjian
Introduzione a PostgreSQL e ai database – Luca Ferrari
11 di 139 Caratteristiche principali
Modello 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 applicazioni
Concorrenza e Multi-utenza: supporta connessioni e query multiple
contemporaneamente
Integrità referenziale: garantisce la coerenza dei dati rispetto alle
business rules
Transazioni: esecuzione di più istruzioni in modo coerente (ACID
rules)
Sub-query: possibilità di innestare le query
Indici: diverse modalità di accesso rapido ai dati (anche per
controllo di coerenza)
Replica: possibilità di sincronizzare due o più istanze fra loro
Introduzione a PostgreSQL e ai database – Luca Ferrari
12 di 139 Caratteristiche principali
Query planner: le query sono analizzate per individuare il miglior
percorso di accesso ai dati
Triggers: reazione agli eventi di inserimento, cancellazione,
aggiornamento di tuple
Rules: possibilità di ordinare la riscrittura di query
Stored procedures: funzioni memorizzate presso il server
Ereditarietà: relazione padre-figlio fra le tabelle
Sequenze: produzione di valori auto-incrementati
Tipi di dato: possibilità di definire un tipo di dato personalizzato
Tablespaces: controllo sullo storage fisico
Partizionamento: possiblità di separare i dati e organizzarli secondo
le esigenze fisiche di storage
Connettività da diversi linguaggi: Perl, Java, C, Python, Ruby,
PHP, ...
Linguaggi integrati: PL/Perl, PL/Java, PL/PGSQL, …
Common Table Expressions: CTE (clausola WITH)
Introduzione a PostgreSQL e ai database – Luca Ferrari
13 di 139 Licenza BSD­like
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996­2010, 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
14 di 139 Un problema di diffusione?
It is a simple, permissive non­copyleft free software license, compatible with the GNU GPL.
We urge you not to use the original
BSD license for software you write.
Introduzione a PostgreSQL e ai database – Luca Ferrari
15 di 139 http://www.postgresql.org
Introduzione a PostgreSQL e ai database – Luca Ferrari
16 di 139 http://www.planetpostgresql.org
Esiste anche il planet italiano: http://www.planetpostgresql.it !
Introduzione a PostgreSQL e ai database – Luca Ferrari
17 di 139 http://pgfoundry.org
Introduzione a PostgreSQL e ai database – Luca Ferrari
18 di 139 http://www.itpug.org
Users' Group Italiano di PostgreSQL
Fornisce un front­end 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
19 di 139 http://www.pgday.it
Introduzione a PostgreSQL e ai database – Luca Ferrari
20 di 139 PostgreSQL Weekly News
Introduzione a PostgreSQL e ai database – Luca Ferrari
21 di 139 Community
Introduzione a PostgreSQL e ai database – Luca Ferrari
22 di 139 Installazione,
concetti base
e primo utilizzo
Introduzione a PostgreSQL e ai database – Luca Ferrari
23 di 139 Numeri di versione
8.4.4
Il Il primo primo numero numero è è un un major major number
number e e indica il “settore di mercato” in cui si indica il “settore di mercato” in cui si colloca la release.
colloca la release.
Ad esempio la 7.x.x era denominata no­
Ad esempio la 7.x.x era denominata no­
crash, la 8.x.x era quella con il port crash, la 8.x.x era quella con il port Windows.
Windows.
Il terzo numero è un minor Il terzo numero è un minor number e indica il patch level number e indica il patch level della release.
della release.
Richiede un nuovo initdb.
Non richiede initdb.
Il secondo numero è un major number e Il secondo numero è un major number e indica il numero annuale di release.
indica il numero annuale di release.
Deve Deve essere essere combinato combinato con con il il primo primo numero numero per per fare fare riferimento riferimento ad ad una una release specifica.
release specifica.
Introduzione a PostgreSQL e ai database – Luca Ferrari
24 di 139 Installazione
Su Linux distribuzioni Debian-like:
apt­get install postgresql
apt­get install postgresql­doc
apt­get install postgresql­contrib
Potrebbe 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
25 di 139 template1, template0
Appena 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
26 di 139 Connessione al database: psql
Si 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
27 di 139 Password prompt degli strumenti testuali
Tutti 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:XXX
dbserver:5432:ordinidb:luca:XXX
backupserver:5432:*:luca:XXX
Se viene trovata l'associazione, allora la password
non viene chiesta al prompt dei comandi (comodo per
l'automazione di script).
Introduzione a PostgreSQL e ai database – Luca Ferrari
28 di 139 Connessione al database: pgadmin3
pgadmin3 è un client grafico basato sulle librerie wxWidgets, grazie
alle quali funziona su piattaforma Linux, Unix, Microsoft, Apple
Introduzione a PostgreSQL e ai database – Luca Ferrari
29 di 139 Connessione al database: phpgadmin
Client Web basato su PHP
Introduzione a PostgreSQL e ai database – Luca Ferrari
30 di 139 Creazione 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
31 di 139 Creazione di un database
Una volta collegati al database template1 è possibile
creare un nuovo database con CREATE DATABASE.
Introduzione a PostgreSQL e ai database – Luca Ferrari
32 di 139 Creazione di un database
Si può anche usare lo script createdb direttamente da
shell.
Introduzione a PostgreSQL e ai database – Luca Ferrari
33 di 139 Creazione di una semplice tabella
Ci 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
34 di 139 Popolamento del database
Introduzione a PostgreSQL e ai database – Luca Ferrari
35 di 139 Introspezione
I 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
36 di 139 Introspezione: esempio
Introduzione a PostgreSQL e ai database – Luca Ferrari
37 di 139 Cluster di database
PostgreSQL è 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
38 di 139 postgresql.conf
Il file postgresql.conf contiene parametri fondamentali
configurazione del server. Alcuni interessanti sono:
per
la
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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
39 di 139 postgresql.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 = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_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 scale
random_page_cost = 4.0 # same scale as above
cpu_tuple_cost = 0.01 # same scale as above
cpu_index_tuple_cost = 0.005 # same scale as above
Introduzione a PostgreSQL e ai database – Luca Ferrari
40 di 139 pg_hba.conf
Il 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 CIDR­ADDRESS 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
41 di 139 Monitorare i processi
PostgreSQL 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
42 di 139 Monitorare le query in esecuzione
E' possibile interrogare il catalogo di sistema
visualizzare le attività in corso nel database:
per
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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
43 di 139 Statistiche usate dall'ottimizzatore
La 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
44 di 139 Creazione di tipi personalizzati
E' 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
45 di 139 Creazione di array
E' 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
46 di 139 Selezione/update dei valori di un array
Introduzione a PostgreSQL e ai database – Luca Ferrari
47 di 139 Funzionamento interno
Introduzione a PostgreSQL e ai database – Luca Ferrari
48 di 139 Layout fisico
Tutti 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
49 di 139 Layout fisico: identificare gli oggetti
Dal catalogo di sistema è possibile identificare i vari oggetti su disco:
Introduzione a PostgreSQL e ai database – Luca Ferrari
50 di 139 Funzionamento interno
Ad
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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
51 di 139 Shared Memory
Tutti i processi postgres condividono una zona di memoria (shared memory)
che contiene le pagine dati caricate.
Introduzione a PostgreSQL e ai database – Luca Ferrari
52 di 139 Transazioni
Introduzione a PostgreSQL e ai database – Luca Ferrari
53 di 139 Livelli di isolamento delle transazioni
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!
Livello di isolamento
Read uncommitted Read committed Repeatable read Serializable Dirty Read
Nonrepeatable Read
Phantom Read
Supportato in PostgreSQL
Possibile
Impossibile
Impossibile
Impossibile
Possibile
Possibile
Impossibile
Impossibile
Possibile
Possibile
Possibile
Impossibile
NO
SI
NO
SI
Dirty Read: si leggono dati non ancora committati da un'altra transazione
Nonrepeatable 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 transazioni
e 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
54 di 139 Transazioni
Le 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)
StartTransactionCommand
CommitTransactionCommand
AbortTransactionCommand
Livello Utente (gestione BEGIN/END)
BeginTransactionBlock
EndTransactionBlock
UserAbortTransactionBlock
DefineSavepoint/RollbackSavepoint
ReleaseSavepoint
Livello Gestione (backend)
StartTransaction/CommitTransaction/AbortTransaction
StartSubTransaction/CommitSubTransaction/AbortSubTransaction
Introduzione a PostgreSQL e ai database – Luca Ferrari
55 di 139 Transazione e Savepoint: esempio
Lo stato di una transazione (committed, in progress, aborted, subcommitted) viene
memorizzato su disco all'interno della directory pg_clog.
Introduzione a PostgreSQL e ai database – Luca Ferrari
56 di 139 Numerazione 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
57 di 139 Wraparound
In 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
58 di 139 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
59 di 139 Transazioni e log: pg_clog
Il 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)
Sub­committed (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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
60 di 139 pg_clog in dettaglio
00
01
11
00
Pagina pg_clog
00
01 11
00
00
01
11
00
Pagina pg_clog
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).
Introduzione a PostgreSQL e ai database – Luca Ferrari
61 di 139 Locking
PostgreSQL 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
62 di 139 Multi Version Concurrency Control
Introduzione a PostgreSQL e ai database – Luca Ferrari
63 di 139 MVCC
PostgreSQL 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).
Introduzione a PostgreSQL e ai database – Luca Ferrari
64 di 139 MVCC: implementazione
Internamente 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
65 di 139 MVCC: 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
66 di 139 MVCC: approccio visuale
xmin
cmin
10
18
21
xmax cmax
20
talkspk
talksid
1 pg01
2 pg02
3 pg03
descrizione
PostgreSQL
PostgreSQL/Python
PostgreSQL/JDBC
docente
Luca Ferrari
Luca Ferrari
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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
67 di 139 Vacuum
Il 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
68 di 139 Vacuum
L'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
Introduzione a PostgreSQL e ai database – Luca Ferrari
69 di 139 Vacuum: esecuzione
Introduzione a PostgreSQL e ai database – Luca Ferrari
70 di 139 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
71 di 139 HOT – 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
72 di 139 MicroVacuum
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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
73 di 139 WAL
Il 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).
Introduzione a PostgreSQL e ai database – Luca Ferrari
74 di 139 WAL: funzionamento in breve
La 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 dati
3) la pagina viene marcata come dirty
4) 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
75 di 139 Log in azione!
Introduzione a PostgreSQL e ai database – Luca Ferrari
76 di 139 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 riscritta
La query viene passata all'ottimizzatore per la
creazione del piano di esecuzione
Introduzione a PostgreSQL e ai database – Luca Ferrari
77 di 139 Vedere come viene trattata una query
2010­06­06 22:19:46 CEST STATEMENT: SELECT description FROM skill;
2010­06­06 22:19:46 CEST LOG: rewritten parse tree:
2010­06­06 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 Introduzione a PostgreSQL e ai database – Luca Ferrari
78 di 139 Pagine dati
Introduzione a PostgreSQL e ai database – Luca Ferrari
79 di 139 Pagine dati
Le pagine dati rappresentano una
astrazione
rispetto
al
blocco
dati
memorizzato su disco
Una pagina dati ha sempre la stessa
struttura in memoria
All'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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
80 di 139 Struttura di una pagina dati
Page Header Data
pd_lsn
pd_tli
pd_lower
pd_upper
pd_special
pd_pagesize_version
pd_linp[ ]
linp0
linp1
linp2
linp3
linp4
linp5
...
linpN
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), tupleN
pd_upper il punto “massimo” di fine tupleN­1
.
tuple. Il range delle tuple è quindi .
identificato da pd_lower e pd_upper.
.
Le tuple sono organizzate a stack: ogni tuple0
nuovo tupla viene inserita in fondo Special space
subito prima delle altre (crescita verso l'alto) mentre il linp viene inserito all'inizio dopo gli altri (crescita verso 81 di 139 Introduzione a PostgreSQL e ai database – Luca Ferrari
pd_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”.
Introduzione a PostgreSQL e ai database – Luca Ferrari
82 di 139 Gestione dei buffer
Le pagine dati vengono caricate in memoria
condivisa in zone dette buffer
Ogni processo utente può accedere ai buffer
(e quindi alle pagine dati) rispettando alcune
regole per evitare corse critiche
Siccome 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
83 di 139 Locking dei buffer
Ogni 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
84 di 139 Ottenere 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
85 di 139 Point In Time Recovery
Introduzione a PostgreSQL e ai database – Luca Ferrari
86 di 139 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();
Introduzione a PostgreSQL e ai database – Luca Ferrari
87 di 139 PITR: ripristino
Una volta in possesso dei log è possibile ordinare al database di
fare un re-do delle transazioni:
1) stop del cluster
2) pulizia delle directory del cluster, in particolare pg_xlog
3) copia dei log dentro a pg_xlog
4) creazione di un file recovery.conf nella root
del cluster:
restore_command = ’cp /backup/wal/%f "%p"’
recovery_target_time=’2007­06­16 10:33:59’
5) avvio del cluster
Introduzione a PostgreSQL e ai database – Luca Ferrari
88 di 139 PITR: esempio
Si crea una grossa tabella,
si effettua il backup PITR
e si cancella la tabella.
Si ferma PostgreSQL, si cancellano
gli xlog dal backup ripristinato e
si crea il file recovery.conf (che sarà
poi eliminato automaticamente).
Introduzione a PostgreSQL e ai database – Luca Ferrari
89 di 139 PITR: esempio
Al riavvio il database
trova il file recovery.conf
e inizia a ripetere le transazioni
presenti nei WAL.
Il risultato è che si trovano
tutte le tabelle che erano state
cancellate!
Introduzione a PostgreSQL e ai database – Luca Ferrari
90 di 139 Indici
Introduzione a PostgreSQL e ai database – Luca Ferrari
91 di 139 Indici
PostgreSQL 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
92 di 139 Indici
B-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 strette
GIST: 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
93 di 139 Indici in PostgreSQL
PostgreSQL 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
94 di 139 Indici e MVCC
A 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 tupla
Di 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)!
Introduzione a PostgreSQL e ai database – Luca Ferrari
95 di 139 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
96 di 139 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
97 di 139 Cool Features
cosa si può fare con PostgreSQL
Introduzione a PostgreSQL e ai database – Luca Ferrari
98 di 139 Utilities
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, ...)
Introduzione a PostgreSQL e ai database – Luca Ferrari
99 di 139 Cool Features: replicazione
Slony: sistema masterslaves
Pgpool: replica le
modifiche a tutti i nodi, e
le interrogazioni a solo un
nodo
Pgcluster: analizza un
cluster e replica le
modifiche
Introduzione a PostgreSQL e ai database – Luca Ferrari
100 di 139 Cool Features: DBI­Link
Sviluppato 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. Introduzione a PostgreSQL e ai database – Luca Ferrari
101 di 139 Cool Features: PostGIS
PostGIS è 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
102 di 139 Cool Features: Full Text Search
FTS utilizza i tsvector come tipi di dati sui quali
fare le ricerche. Le ricerche vengono specificate
tramite l'operatore @@.
Introduzione a PostgreSQL e ai database – Luca Ferrari
103 di 139 Cool 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
104 di 139 Partitioning
(tramite ereditarietà e trigger)
Introduzione a PostgreSQL e ai database – Luca Ferrari
105 di 139 Partitioning
Il 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
106 di 139 Partitioning: esempio di tabella
CREATE TABLE movmag (
CREATE TABLE movmag (
movmagpk serial NOT NULL,
movmagpk serial NOT NULL,
data date,
data date,
descrizione character varying DEFAULT 20,
descrizione character varying DEFAULT 20,
qta real )
qta real )
CREATE TABLE movmag2007 (
CREATE TABLE movmag2007 (
­­ Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass),
­­ Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass),
­­ Ereditato: data date,
­­ Ereditato: data date,
­­ Ereditato: descrizione character varying DEFAULT 20,
­­ Ereditato: descrizione character varying DEFAULT 20,
­­ Ereditato: qta real,
­­ Ereditato: qta real,
CONSTRAINT date_2007 CHECK CONSTRAINT date_2007 CHECK (data >= '2007­01­01'::date AND data <= '2007­12­31'::date)
(data >= '2007­01­01'::date AND data <= '2007­12­31'::date)
) INHERITS (movmag)
) INHERITS (movmag)
CREATE TABLE movmag2008 (
CREATE TABLE movmag2008 (
­­ Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass),
­­ Ereditato: movmagpk integer NOT NULL DEFAULT nextval('movmag_movmagpk_seq'::regclass),
­­ Ereditato: data date,
­­ Ereditato: data date,
­­ Ereditato: descrizione character varying DEFAULT 20,
­­ Ereditato: descrizione character varying DEFAULT 20,
­­ Ereditato: qta real,
­­ Ereditato: qta real,
CONSTRAINT date_2008 CHECK CONSTRAINT date_2008 CHECK (data >= '2008­01­01'::date AND data <= '2008­12­31'::date)
(data >= '2008­01­01'::date AND data <= '2008­12­31'::date)
) INHERITS (movmag)
) INHERITS (movmag)
Introduzione a PostgreSQL e ai database – Luca Ferrari
107 di 139 Partitioning con un trigger
CREATE OR REPLACE FUNCTION _inserimento_movimento_magazzino()
RETURNS trigger AS
$BODY$
BEGIN
­­ controllo la data di inserimento IF NEW.data >= '01­01­2007'::date AND NEW.data <= '31­12­2007' 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 originale
RETURN NULL;
ELSIF NEW.data >= '01­01­2008'::date AND NEW.data <= '31­12­2008'::date THEN
RAISE 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;
Introduzione a PostgreSQL e ai database – Luca Ferrari
108 di 139 Funzionamento del trigger
CREATE TRIGGER tr_inserimento
BEFORE INSERT ON movmag
FOR EACH ROW
EXECUTE PROCEDURE _inserimento_movimento_magazzino();
Introduzione a PostgreSQL e ai database – Luca Ferrari
109 di 139 Partitioning con una rule
CREATE OR REPLACE RULE inserimento_2007 AS
ON INSERT TO movmag
WHERE new.data >= '2007­01­01'::date AND new.data <= '2007­12­31'::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 >= '2008­01­01'::date AND new.data <= '2008­12­31'::date DO INSTEAD INSERT INTO movmag2008 (data, descrizione, qta) VALUES (new.data, new.descrizione, new.qta);
In questo caso il trigger non scatta!
Introduzione a PostgreSQL e ai database – Luca Ferrari
110 di 139 Partitioning: accesso ai dati
Qualunque 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
111 di 139 Explain:
capire come lavora l'ottimizzatore
Introduzione a PostgreSQL e ai database – Luca Ferrari
112 di 139 EXPLAIN
Explain è 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
113 di 139 Explain: 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
114 di 139 Explain: aggiornare le statistiche
La 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:
Introduzione a PostgreSQL e ai database – Luca Ferrari
115 di 139 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
116 di 139 Explain: perché non usa l'indice?
Introduzione a PostgreSQL e ai database – Luca Ferrari
117 di 139 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
118 di 139 Explain: utilizzo dell'indice
1) Si parte dal bitmap index scan (perché la condizione è di uguaglianza), quella che ha costo inziiale 0.00
2) 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
Introduzione a PostgreSQL e ai database – Luca Ferrari
119 di 139 Explain: ulteriore esempio
Si 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 > 0
2) Si applica un filtro in memoria sulla clausola durata < 0.5
3) Si effettua un nuovo scan, questa volta delle tuple in memoria
4) Si effettua un ordinamento delle tuple
Introduzione a PostgreSQL e ai database – Luca Ferrari
120 di 139 Explain: ulteriore esempio
1) Si parte dal sequential scan (perché non esiste indice sfruttabile), quella che ha costo inziiale 0.00
2) Dalla scansione in indice, si estraggono circa 4M di tuple (complete di dati)
3) Si effettua un sort in memoria delle tuple restituite
Introduzione a PostgreSQL e ai database – Luca Ferrari
121 di 139 Window Functions
Introduzione a PostgreSQL e ai database – Luca Ferrari
122 di 139 Window 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).
Introduzione a PostgreSQL e ai database – Luca Ferrari
123 di 139 Window 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().
Introduzione a PostgreSQL e ai database – Luca Ferrari
124 di 139 Common Table Expressions
Introduzione a PostgreSQL e ai database – Luca Ferrari
125 di 139 Commont Table Expressions
Le 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
126 di 139 CTE: 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> )
WITH temporary_table_name AS ( <query secondaria> )
<query principale>
<query principale>
Introduzione a PostgreSQL e ai database – Luca Ferrari
127 di 139 CTE: considerazioni
Il sistema genera una tabella temporanea con i risultati della
1
query secondaria
La query principale puo' fare riferimento alla tabella
temporanea 2
Quando la query principale termina la tabella temporanea
3
viene distrutta automaticamente
WITH random_number_table AS WITH random_number_table AS 1
( SELECT random() AS number )
( SELECT random() AS number )
SELECT 'now'::text::timestamp AS timestamp, SELECT 'now'::text::timestamp AS timestamp, random_number_table.number;
random_number_table.number;
2
3
Introduzione a PostgreSQL e ai database – Luca Ferrari
128 di 139 CTE: un esempio
Date 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);
skill
skillfamily
skillfamilypk
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;
Introduzione a PostgreSQL e ai database – Luca Ferrari
129 di 139 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
130 di 139 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
131 di 139 Senza CTE: un altro esempio
explain 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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
132 di 139 CTE: un altro esempio
explain 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
133 di 139 Con CTE: un altro esempio
explain 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
134 di 139 CTE 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!
Introduzione a PostgreSQL e ai database – Luca Ferrari
135 di 139 CTE ricorsive: esempio
Termine non ricorsivo
WITH RECURSIVE counter_table AS ( SELECT 1 AS initial_counter UNION ALL Congiunzione
SELECT ct.initial_counter + 1 Termine ricorsivo: FROM counter_table ct fa riferimento alla WHERE ct.initial_counter < 100 )
tabella temporanea!
SELECT * FROM counter_table;
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)
Introduzione a PostgreSQL e ai database – Luca Ferrari
136 di 139 CTE: come funziona la ricorsione?
Si valuta la query temporanea
Il 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.
Introduzione a PostgreSQL e ai database – Luca Ferrari
137 di 139 CTE 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 (
WITH RECURSIVE hierarchy_exploded AS (
SELECT pk, parentpk, ARRAY[pk] AS brothers SELECT pk, parentpk, ARRAY[pk] AS brothers FROM hierarchy WHERE parentpk IS NULL
FROM hierarchy WHERE parentpk IS NULL
UNION ALL
UNION ALL
SELECT h.pk, h.parentpk, he.brothers || SELECT h.pk, h.parentpk, he.brothers || ARRAY[h2.pk]
ARRAY[h2.pk]
FROM hierarchy h JOIN hierarchy_exploded FROM hierarchy h JOIN hierarchy_exploded he ON h.parentpk = he.pk JOIN hierarchy h2 he ON h.parentpk = he.pk JOIN hierarchy h2 ON h2.parentpk = h.parentpk )
ON h2.parentpk = h.parentpk )
SELECT * FROM hierarchy_exploded;
SELECT * FROM hierarchy_exploded;
Introduzione a PostgreSQL e ai database – Luca Ferrari
138 di 139 Conclusioni
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 e ai database – Luca Ferrari
139 di 139