PostgreSQL tenta di minimizzare, o meglio di ritardare il piu' possibile, gli accessi allo storage fisico. In questo port voglio mostrare come anche una operazione apparentemente banale come la cancellazione di una colonna da una tabella non produca immediati effetti su disco.
Per prima cosa occorre creare una tabella di esempio da popolare con un po' di dati di prova per valutare come cambi l'uso dello storage secondo le modifiche alla struttura della tabella stessa:
CREATE TABLE dropping_test( pk SERIAL NOT NULL, description text, PRIMARY KEY(pk) );
WITH RECURSIVE counter(pk) AS
( SELECT 1 UNION SELECT pk + 1
FROM counter WHERE pk < 1000000 )
INSERT INTO dropping_test(pk, description)
SELECT c.pk, 'A fake description for entry ' || c.pk
FROM counter c;
La query di cui sopra vuota la tabella e la riempie di un milione di tuple sostanzialmente identiche. La dimensione finale della tabella risulta essere di circa 60 MB:
SELECT pg_size_pretty( pg_relation_size( 'dropping_test' ) );
pg_size_pretty
----------------
65 MB
A questo punto si puo' procedere alla rimozione della colonna description, che e' quella che occupa la maggior parte dello spazio di storage essendo formata da testo libero. Da notare che dopo la rimozione della colonna il catalogo di sistema mostra l'attributo (colonna) come "deprecato". Cio' significa che l'attributo e' stato eliminato logicamente dalla tabella ma non fisicamente (poiche' ancora presente nel catalogo di sistema).
ALTER TABLE dropping_test DROP COLUMN description;
ALTER TABLE
Time: 92.774 ms
Da notare la velocita' di eliminazione (logica) della colonna: di fatto non e' successo molto sullo storage, e quindi il sistema non ha perso tempo nel rimuovere la colonna.
Una colonna rimossa diventa sostanzialmente invisibile al parser, ossia non e' usabile lato SQL. Se si interroga il catalogo di sistema si ottiene l'informazioni circa la rimozione della colonna, che pero' ha perso il proprio nome (il drop di una colonna non e' una operazione recuperabile):
SELECT attname, attisdropped FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid WHERE c.relname = 'dropping_test';
attname | attisdropped
------------------------------+--------------
tableoid | f
cmax | f
xmax | f
cmin | f
xmin | f
ctid | f
pk | f
........pg.dropped.2........ | t
In realta', se e' vero che si e' perso il nome della colonna, si ha qualche informazione circa la sua posizione: il nome nel catalogo degli attributi riporta la posizione ordinale della colonna appena eliminata. In altre parole, 'pg.dropped.2' indica che e' stata eliminata la colonna numero 2 della tabella, come si puo' evincere dal file sorgente heap.c:
/*
* Change the column name to something
* that isn't likely to conflict
*/
snprintf(newattname, sizeof(newattname),
"........pg.dropped.%d........", attnum);
E la tabella, dal punto di vista fisico, come e' cambiata? Di fatto non e' cambiata, visto che lo storage non e' stato toccato:
SELECT pg_size_pretty( pg_relation_size( 'dropping_test' ) ); pg_size_pretty
----------------
65 MB
A questo punto si puo' far intervenire vacuum per vedere cosa succede lato fisico e logico; in particolare il processo di vacuuming andra' a riscrivere le pagine dati rimuovendo lo spazio occupato dalla colonna rimossa. Il risultato, al termine dell'operazione, e' una riduzione della dimensione della tabelle e la scomparsa dell'attributo droppato nel catalogo di sistema:
VACUUM FULL VERBOSE dropping_test;
INFO: vacuuming "public.dropping_test"
INFO: "dropping_test": found 0 removable, 1000000 nonremovable row versions in 8334 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.61s/0.53u sec elapsed 2.29 sec.
VACUUM
Time: 4593.219 ms
Che ci porta ad avere una tabella "compressa":
SELECT pg_size_pretty( pg_relation_size( 'dropping_test' ) );
pg_size_pretty
----------------
31 MB
Si presti attenzione: tutto questo e' stato possibile perche' autovacuum era disabilitato:
SHOW autovacuum;
autovacuum
------------
off
Se autovacuum fosse stato attivo, come normalmente e', la riorganizzazione della tabella sarebbe avvenuta quasi subito e quindi la dimensione della tabella sarebbe cambiata sotto al naso dell'operatore.
Si noti che l'operazione inversa, l'aggiunta di una colonna, si comporta sostanzialmente in modo analogo: quando viene aggiunta una colonna il sistema memorizza nel catalogo la disponibilita' della nuova colonna, ma non opera sullo storage.
ALTER TABLE dropping_test ADD COLUMN description text;
ALTER TABLE
Time: 19.337 ms
SELECT pg_size_pretty( pg_relation_size( 'dropping_test' ) );
pg_size_pretty
----------------
31 MB
E si noti anche che la scrittura di una tupla non implica la riorganizzazione di tutta la tabella: di fatto, grazie ad MVCC, le tuple "nuove" sono in append sui dati e quindi le tuple "vecchie", prive di colonna aggiunta, non vengono riorganizzate:
UPDATE dropping_test
SET description = 'Hello World' WHERE pk = 1;
SELECT pg_size_pretty( pg_relation_size( 'dropping_test' ) );
pg_size_pretty
----------------
31 MB
Discorso differente se si aggiunge una colonna con valore di default, cosa che forza il sistema a scrivere il valore di default su ogni tupla e quindi ad invalidare ogni tupla vecchia con conseguente raddoppiamento delle tuple e aumento di spazio:
ALTER TABLE dropping_test
ADD COLUMN description_initialized text
default 'A fake description as default here!';
ALTER TABLE
Time: 4356.180 ms
SELECT pg_size_pretty( pg_relation_size( 'dropping_test' ) ); pg_size_pretty
----------------
65 MB
In conclusione, PostgreSQL cerca di utilizzare lo storage fisico in modo molto efficiente ritardando il piu' possibile le scritture (o riscritture) su disco. Ancora una volta si evince come il comportamento di PostgreSQL sia molto guidato dal catalogo di sistema.
