Quanto spazio occupa NULL?
dom, 2026-02-08 16:15 —
oliProva 1: riempire con
Tentativo 1: riempire con
Durante l’ultima consulenza presso il cliente, questi mi è venuto incontro raggiante dicendomi che aveva seguito il mio consiglio e aveva modificato tutte le colonne Primary Key da BIGINT (8 byte) a INT (4 byte) e che questo aveva portato grandi vantaggi! Il suo database MySQL 8.4 era ora più piccolo di 750 Gbyte (da 5,5 Tbyte). Fantastico!
E sì, so che questo contraddice le raccomandazioni di alcuni dei miei colleghi PostgreSQL (qui e qui). Nel mondo MySQL si dà più importanza a queste cose (fonte):
Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables
Inoltre, InnoDB funziona in modo leggermente diverso (tabella clusterizzata dell’indice e chiave primaria in tutte le chiavi secondarie) rispetto a PostgreSQL (tabella heap, indici con puntatore di riga (ctid)).
Ma in realtà non è questo il punto. Subito dopo mi ha chiesto se riempire le colonne di tipo DOUBLE (8 byte, in PostgreSQL DOUBLE PRECISION) con NULL avrebbe portato a un risparmio di spazio o se fosse meglio eliminare subito le colonne. La mia prima risposta istintiva per DOUBLE è stata: NULL è utile, seguito da OPTIMIZE TABLE (in PostgreSQL VACUUM FULL). Il secondo pensiero è stato però: DOUBLE è un tipo di dati fisso più lungo, questo vale anche con NULL o solo per i tipi di dati con lunghezza variabile? La prudenza non è mai troppa! Prima di tutto, consulta il manuale…
E lì c’è scritto (fonte):
Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.
e (fonte):
The variable-length part of the record header contains a bit vector for indicating NULL columns. … Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
Prova con MariaDB/MySQL
Configurazione di prova
In qualche modo la descrizione mi sembra un po’ troppo complicata. Forse un piccolo schizzo potrebbe aiutare? Allora facciamo una prova:
SQL> -- DROP TABLE IF EXISTS tracking;
SQL> CREATE TABLE tracking (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, d0 DOUBLE, d1 DOUBLE, d2 DOUBLE, d3 DOUBLE, d4 DOUBLE
, d5 DOUBLE, d6 DOUBLE, d7 DOUBLE, d8 DOUBLE, d9 DOUBLE
);
SQL> INSERT INTO tracking SELECT NULL, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0;
SQL> INSERT INTO tracking SELECT NULL, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 FROM tracking;
... bis 16 M rows
Sia in MariaDB che in MySQL, la tabella ha una dimensione di circa 1,8 GB con 16 milioni di righe. Poiché queste informazioni sono indicate in modo molto impreciso in INFORMATION_SCHEMA, controlliamo il file system:
MariaDB 11.8:
SQL> system ls -l tracking.ibd
-rw-rw---- 1 mysql mysql 1206 Feb 7 10:28 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb 7 10:32 tracking.ibd
MySQL 8.4:
SQL> system ls -l tracking.ibd
-rw-r----- 1 mysql mysql 1929379840 Feb 7 10:33 tracking.ibd
Deframmentazione della tabella
Quindi “deframmentiamo” la tabella con il comando OPTIMIZE TABLE:
SQL> OPTIMIZE TABLE tracking;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| test.tracking | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.tracking | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
Attenzione: la tabella viene copiata una volta! Quindi è necessario il doppio dello spazio su disco per un breve periodo! Lo si può osservare chiaramente mentre il comando OPTIMIZE TABLE è in esecuzione:
MariaDB:
$ watch -d -n 1 'ls -l trac* \#*'
-rw-rw---- 1 mysql mysql 1206 Feb 7 10:39 '#sql-alter-d57-8c.frm'
-rw-rw---- 1 mysql mysql 968884224 Feb 7 10:39 '#sql-alter-d57-8c.ibd'
-rw-rw---- 1 mysql mysql 1206 Feb 7 10:28 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb 7 10:32 tracking.ibd
MySQL:
$ watch -d -n 1 'ls -l trac* \#*'
-rw-r----- 1 mysql mysql 369098752 Feb 7 10:40 #sql-ib1594-4164062678.ibd
-rw-r----- 1 mysql mysql 1929379840 Feb 7 10:33 tracking.ibd
Il risultato è però sorprendente! In MariaDB la tabella è rimasta più o meno della stessa dimensione:
-rw-rw---- 1 mysql mysql 1206 Feb 7 10:39 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb 7 10:39 tracking.ibd
In MySQL, invece, dopo la “deframmentazione” la tabella è addirittura cresciuta di circa il 14%:
-rw-r----- 1 mysql mysql 2197815296 Feb 7 10:41 tracking.ibd
Se eseguiamo nuovamente il comando OPTIMIZE TABLE, la dimensione rimane costante sia in MariaDB che in MySQL:
MariaDB:
-rw-rw---- 1 mysql mysql 1206 Feb 7 10:46 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb 7 10:48 tracking.ibd
MySQL:
-rw-r----- 1 mysql mysql 2197815296 Feb 7 10:48 tracking.ibd
Prova 1: riempire con NULL
Ora sovrascriviamo i valori con NULL:
SQL> UPDATE tracking
SET d0 = NULL, d1 = NULL, d2 = NULL, d3 = NULL, d4 = NULL
, d5 = NULL, d6 = NULL, d7 = NULL, d8 = NULL, d9 = NULL
;
Dopo questo passaggio, le dimensioni dei file sono addirittura aumentate leggermente:
MariaDB (+1.3%):
-rw-rw---- 1 mysql mysql 1206 Feb 7 10:49 tracking.frm
-rw-rw---- 1 mysql mysql 1937768448 Feb 7 11:04 tracking.ibd
MySQL (+0.2%):
-rw-r----- 1 mysql mysql 2202009600 Feb 7 11:04 tracking.ibd
Successivamente, deframmentiamo nuovamente la tabella con il comando OPTIMIZE TABLE. Come previsto, le tabelle si riducono.
MariaDB (al 23%):
-rw-rw---- 1 mysql mysql 1206 Feb 7 11:09 tracking.frm
-rw-rw---- 1 mysql mysql 448790528 Feb 7 11:10 tracking.ibd
MySQL (al 24%):
-rw-r----- 1 mysql mysql 520093696 Feb 7 11:10 tracking.ibd
Un nuovo OPTIMIZE TABLE non apporta più alcuna modifica alla dimensione del file…
Prova 2: cancellazione delle colonne
Ora proviamo di nuovo con il comando DROP COLUMN. La situazione di partenza è la stessa descritta sopra:
MariaDB:
-rw-rw---- 1 mysql mysql 1206 Feb 7 11:15 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb 7 11:18 tracking.ibd
MySQL:
-rw-r----- 1 mysql mysql 1929379840 Feb 7 11:19 tracking.ibd
Dopo il comando OPTIMIZE TABLE, i valori sono simili a quelli della prima prova:
MariaDB:
-rw-rw---- 1 mysql mysql 1206 Feb 7 11:20 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb 7 11:21 tracking.ibd
MySQL:
-rw-r----- 1 mysql mysql 2197815296 Feb 7 11:21 tracking.ibd
Un nuovo OPTIMIZE TABLE non apporta ulteriori modifiche, come sopra:
MariaDB:
-rw-rw---- 1 mysql mysql 1206 Feb 7 11:22 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb 7 11:23 tracking.ibd
MySQL:
-rw-r----- 1 mysql mysql 2197815296 Feb 7 11:24 tracking.ibd
E ora il secondo tentativo vero e proprio con l’eliminazione delle colonne:
SQL> ALTER TABLE tracking
DROP COLUMN d0, DROP COLUMN d1, DROP COLUMN d2, DROP COLUMN d3, DROP COLUMN d4
, DROP COLUMN d5, DROP COLUMN d6, DROP COLUMN d7, DROP COLUMN d8, DROP COLUMN d9
;
Per prima cosa notiamo che il comando è INSTANTANEOUS, in quanto non apporta alcuna modifica ai dati, ma solo ai metadati. Da un lato questo è positivo, poiché l’influenza sull’applicazione è minima. D’altra parte, ciò significa anche che non si ottiene alcun risparmio di spazio.
Quindi affrontiamo nuovamente il problema con il comando OPTIMIZE TABLE:
MariaDB (al 93%):
-rw-rw---- 1 mysql mysql 925 Feb 7 11:28 tracking.frm
-rw-rw---- 1 mysql mysql 415236096 Feb 7 11:29 tracking.ibd
MySQL (al 92%):
-rw-r----- 1 mysql mysql 478150656 Feb 7 11:28 tracking.ibd
Conclusione
Sia la sostituzione dei valori delle colonne con NULL che la cancellazione delle colonne consentono un notevole risparmio di spazio. La cancellazione delle colonne comporta un risparmio maggiore di circa il 7% rispetto alla sostituzione con NULL. Se possibile dal punto di vista applicativo, è quindi consigliabile cancellare le colonne non più necessarie; se ciò non è possibile, sostituirle almeno con NULL.
Prova con PostgreSQL
Ora esaminiamo il tutto con PostgreSQL.
Configurazione della prova
La configurazione della prova è analoga a quella di MariaDB/MySQL:
postgres=# -- DROP TABLE IF EXISTS tracking;
postgres=# CREATE TABLE tracking (
id SERIAL PRIMARY KEY
, d0 DOUBLE PRECISION, d1 DOUBLE PRECISION, d2 DOUBLE PRECISION, d3 DOUBLE PRECISION, d4 DOUBLE PRECISION
, d5 DOUBLE PRECISION, d6 DOUBLE PRECISION, d7 DOUBLE PRECISION, d8 DOUBLE PRECISION, d9 DOUBLE PRECISION
);
postgres=# \timing
postgres=# INSERT INTO tracking (d0, d1, d2, d3, d4, d5, d6, d7, d8, d9)
SELECT 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0;
postgres=# INSERT INTO tracking (d0, d1, d2, d3, d4, d5, d6, d7, d8, d9)
SELECT 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 FROM tracking;
... bis 16 M rows
Per prima cosa vogliamo sapere quanto è diventata grande la tabella. PostgreSQL sembra conoscere queste informazioni con estrema precisione:
postgres=# SELECT pg_relation_size('tracking') AS tab_siz
, pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
, pg_indexes_size('tracking') AS idx_siz
, pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
, pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
, pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
, pg_total_relation_size('tracking') AS tot_rel_siz
, pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
tab_siz | tab_siz_prtty | idx_siz | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
1963417600 | 1872 MB | 376856576 | 359 MB | 2340274176 | 2232 MB | 2340798464 | 2232 MB
Poi vogliamo sapere dove si trovano questi file nel filesystem:
postgres=# SELECT oid AS db_oid FROM pg_database WHERE datname = current_database();
db_oid
--------
5
postgres=# SELECT oid AS table_oid, relname, relnamespace, relfilenode
FROM pg_class WHERE relname = 'tracking';
table_oid | relname | relnamespace | relfilenode
-----------+----------+--------------+-------------
40965 | tracking | 2200 | 40965
postgres=# SELECT i.indexrelid::regclass as index_name, i.indexrelid as index_oid
FROM pg_index i
JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'tracking';
index_name | index_oid
---------------+-----------
tracking_pkey | 40970
postgres=# SELECT pg_relation_filepath('tracking');
pg_relation_filepath
----------------------
base/5/40965
Dimensioni della tabella e dell’indice nel filesystem:
$ ls -ltr 40965* 40970*
-rw------- 1 mysql mysql 40960 Feb 7 18:33 40965_vm
-rw------- 1 mysql mysql 499712 Feb 7 18:33 40965_fsm
-rw------- 1 mysql mysql 889675776 Feb 7 18:34 40965.1
-rw------- 1 mysql mysql 1073741824 Feb 7 18:34 40965
-rw------- 1 mysql mysql 376856576 Feb 7 18:35 40970
*_fsmsignifica “free space map”*_vmsignifica “visibility map”*.1significa 2° segmento dell’oggetto (tabella o indice)
PostgreSQL sembra lavorare di default con segmenti di 1 GB e, a differenza di MariaDB/MySQL (INFORMATION_SCHEMA), sembra conoscere con estrema precisione le dimensioni dei propri file. La discrepanza di cui sopra (tra tot_rel_siz e tab_and_idx_siz) può essere spiegata dai file fsm e vm.
L’equivalente PostgreSQL di MariaDB/MySQL OPTIMIZE TABLE è il comando VACUUM FULL:
postgres=# VACUUM FULL tracking;
$ ls -ltr
-rw------- 1 mysql mysql 40960 Feb 7 18:39 40965_vm
-rw------- 1 mysql mysql 499712 Feb 7 18:39 40965_fsm
-rw------- 1 mysql mysql 1073741824 Feb 7 18:39 40965
-rw------- 1 mysql mysql 889675776 Feb 7 18:39 40965.1
-rw------- 1 mysql mysql 1073741824 Feb 7 18:39 40972
-rw------- 1 mysql mysql 889675776 Feb 7 18:39 40972.1
-rw------- 1 mysql mysql 0 Feb 7 18:39 40975
...
-rw------- 1 mysql mysql 49152 Feb 7 18:39 2704
-rw------- 1 mysql mysql 32768 Feb 7 18:39 2703
-rw------- 1 mysql mysql 32768 Feb 7 18:39 2696
-rw------- 1 mysql mysql 65536 Feb 7 18:39 2674
-rw------- 1 mysql mysql 81920 Feb 7 18:39 2673
-rw------- 1 mysql mysql 98304 Feb 7 18:39 2659
-rw------- 1 mysql mysql 139264 Feb 7 18:39 2658
-rw------- 1 mysql mysql 24576 Feb 7 18:39 2619_fsm
-rw------- 1 mysql mysql 163840 Feb 7 18:39 2619
-rw------- 1 mysql mysql 106496 Feb 7 18:39 2608
-rw------- 1 mysql mysql 491520 Feb 7 18:39 1249
-rw------- 1 mysql mysql 122880 Feb 7 18:39 1247
-rw------- 1 mysql mysql 32768 Feb 7 18:39 2662
-rw------- 1 mysql mysql 114688 Feb 7 18:39 1259
-rw------- 1 mysql mysql 16384 Feb 7 18:39 3455
-rw------- 1 mysql mysql 49152 Feb 7 18:39 2663
-rw------- 1 mysql mysql 1073741824 Feb 7 18:39 40972
-rw------- 1 mysql mysql 889675776 Feb 7 18:39 40972.1
-rw------- 1 mysql mysql 376864768 Feb 7 18:39 40975
-rw------- 1 mysql mysql 0 Feb 7 18:39 40965
-rw------- 1 mysql mysql 0 Feb 7 18:39 40970
La prima cosa che si nota è che PostgreSQL crea molti file e il file “free space map” è scomparso. A differenza di MariaDB/MySQL, i segmenti della tabella sono rimasti della stessa dimensione. Si nota inoltre che la vecchia tabella è “scomparsa” (40965, 40970) e ne è stata creata una nuova (40972 e 40975). Il comando VACUUM FULL in PostgreSQL crea quindi, come in MariaDB/MySQL, una copia dei dati.
postgres=# SELECT pg_relation_size('tracking') AS tab_siz
, pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
, pg_indexes_size('tracking') AS idx_siz
, pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
, pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
, pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
, pg_total_relation_size('tracking') AS tot_rel_siz
, pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
tab_siz | tab_siz_prtty | idx_siz | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
1963417600 | 1872 MB | 376864768 | 359 MB | 2340282368 | 2232 MB | 2340282368 | 2232 MB
Per capire quali altri file/oggetti sono stati modificati, è utile la seguente query:
postgres=# SELECT c.oid, c.relname, ns.nspname
FROM pg_class AS c
JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
WHERE c.oid IN (2704, 2703, 2696, 2674, 2673, 2659, 2658, 2619, 2608, 1249, 1247, 40972, 2662, 1259, 3455, 2663, 40975, 40965, 40970)
;
oid | relname | nspname
-------+-----------------------------------+------------
40965 | tracking | public
40970 | tracking_pkey | public
2619 | pg_statistic | pg_catalog
1247 | pg_type | pg_catalog
2703 | pg_type_oid_index | pg_catalog
2704 | pg_type_typname_nsp_index | pg_catalog
2658 | pg_attribute_relid_attnam_index | pg_catalog
2659 | pg_attribute_relid_attnum_index | pg_catalog
2662 | pg_class_oid_index | pg_catalog
2663 | pg_class_relname_nsp_index | pg_catalog
3455 | pg_class_tblspc_relfilenode_index | pg_catalog
2696 | pg_statistic_relid_att_inh_index | pg_catalog
2673 | pg_depend_depender_index | pg_catalog
2674 | pg_depend_reference_index | pg_catalog
1249 | pg_attribute | pg_catalog
1259 | pg_class | pg_catalog
2608 | pg_depend | pg_catalog
postgres=# SELECT i.indexrelid::regclass as index_name, i.indexrelid as index_oid, ns.nspname
FROM pg_index i
JOIN pg_class c ON i.indrelid = c.oid
JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
WHERE c.oid IN (2704, 2703, 2696, 2674, 2673, 2659, 2658, 2619, 2608, 1249, 1247, 40972, 2662, 1259, 3455, 2663, 40975, 40965, 40970)
;
index_name | index_oid | nspname
-----------------------------------+-----------+------------
pg_type_typname_nsp_index | 2704 | pg_catalog
pg_attribute_relid_attnam_index | 2658 | pg_catalog
tracking_pkey | 40970 | public
pg_class_relname_nsp_index | 2663 | pg_catalog
pg_class_tblspc_relfilenode_index | 3455 | pg_catalog
pg_type_oid_index | 2703 | pg_catalog
pg_attribute_relid_attnum_index | 2659 | pg_catalog
pg_statistic_relid_att_inh_index | 2696 | pg_catalog
pg_depend_depender_index | 2673 | pg_catalog
pg_depend_reference_index | 2674 | pg_catalog
pg_class_oid_index | 2662 | pg_catalog
Tentativo 1: riempire con NULL
Quindi sovrascriviamo anche i valori delle colonne in PostgreSQL con NULL. Da questo punto in poi non ci occuperemo più del file system, poiché PostgreSQL sembra conoscere con precisione le dimensioni dei file, come abbiamo visto sopra:
postgres=# UPDATE tracking
SET d0 = NULL, d1 = NULL, d2 = NULL, d3 = NULL, d4 = NULL
, d5 = NULL, d6 = NULL, d7 = NULL, d8 = NULL, d9 = NULL
;
postgres=# SELECT pg_relation_size('tracking') AS tab_siz
, pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
, pg_indexes_size('tracking') AS idx_siz
, pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
, pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
, pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
, pg_total_relation_size('tracking') AS tot_rel_siz
, pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
tab_siz | tab_siz_prtty | idx_siz | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
2695716864 | 2571 MB | 753696768 | 719 MB | 3449413632 | 3290 MB | 3450101760 | 3290 MB
Qui vediamo che i segmenti della tabella crescono in modo massiccio (+37%), ciò che nella terminologia PostgreSQL viene definito “bloat”. L’implementazione MVCC di PostgreSQL salva sia la versione vecchia che quella nuova della riga “in-place”, ovvero direttamente nella tabella, a differenza di MariaDB/MySQL che salva la versione vecchia nello spazio UNDO e la nuova riga “in-place”. Anche il file dell’indice aumenta in modo significativo (+100%). Dobbiamo ancora approfondire il motivo di questo fenomeno. Inoltre, viene nuovamente creata una “free space map” (differenza tra tot_rel_siz e tab_and_idx_siz).
Un successivo VACUUM FULL riduce la tabella (al 28%) e l’indice (al 50%) rispetto alle dimensioni precedenti:
postgres=# VACUUM FULL tracking;
postgres=# SELECT pg_relation_size('tracking') AS tab_siz
, pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
, pg_indexes_size('tracking') AS idx_siz
, pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
, pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
, pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
, pg_total_relation_size('tracking') AS tot_rel_siz
, pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
tab_siz | tab_siz_prtty | idx_siz | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
742916096 | 709 MB | 376864768 | 359 MB | 1119780864 | 1068 MB | 1119780864 | 1068 MB
e anche rispetto alle dimensioni originali, la tabella (al 38%) e l’indice (al 100%) diventano nuovamente più piccoli. Il motivo per cui l’indice è rimasto della stessa dimensione e solo la tabella si è ridotta deve ancora essere studiato…
Tentativo 2: cancellazione delle colonne
Successivamente, le colonne vengono cancellate con DROP COLUMN.
postgres=# ALTER TABLE tracking
DROP COLUMN d0, DROP COLUMN d1, DROP COLUMN d2, DROP COLUMN d3, DROP COLUMN d4
, DROP COLUMN d5, DROP COLUMN d6, DROP COLUMN d7, DROP COLUMN d8, DROP COLUMN d9
;
Poiché la risposta è arrivata immediatamente, si può presumere che anche questa operazione sia istantanea. Purtroppo non ho trovato nulla al riguardo nella documentazione di PostgreSQL.
Le dimensioni non sono cambiate in modo significativo, cosa che in realtà è prevedibile con un’operazione istantanea. Tuttavia, il fatto che le dimensioni non siano cambiate dopo il comando VACUUM FULL è stato un po’ sorprendente:
tab_siz | tab_siz_prtty | idx_siz | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
742916096 | 709 MB | 376864768 | 359 MB | 1119780864 | 1068 MB | 1120010240 | 1068 MB
postgres=# VACUUM FULL tracking;
tab_siz | tab_siz_prtty | idx_siz | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
742916096 | 709 MB | 376864768 | 359 MB | 1119780864 | 1068 MB | 1119780864 | 1068 MB
Osservazioni
Il locking in PostgreSQL funziona come segue:
VACUUMSono possibili comandi DML concorrenti simili al comandoOPTIMIZE TABLEdi MariaDB/MySQL. Il risultato, tuttavia, non è esattamente lo stesso.VACUUM FULLprovoca un bloccoACCESS EXCLUSIVE. Simile a MariaDB/MySQL 5.5 e versioni precedenti con il comandoOPTIMIZE TABLE. I comandi DML eSELECTNON sono consentiti.
Fonti
- How to Get Sizes of Database Objects in PostgreSQL
- Database File Layout
- System Administration Functions
- CLUSTER
- VACUUM
- Explicit Locking
Prove aggiuntive
- Invece di
0.0, nelle colonned0-d9è stato inseritoNULL. La tabella è rimasta piccola (tot_rel_siz_prtty = 1068 MB). Vale quindi la pena salvareNULLinvece di valori fittizi anche in PostgreSQL. - Le colonne
d0-d9sono state create conDOUBLE PRECISION NOT NULLe riempite con i valori0.0. Nessun effetto: la tabella è rimasta grande (tot_rel_siz_prtty = 2232 MB).
Questa pagina è stata tradotta utilizzando deepl.com.
