Le colonne nullable occupano uno spazio aggiuntivo in PostgreSQL?

Ho una tabella con 7 colonne e 5 di esse saranno nulle. Avrò colonne nulle su tipi di dati int , text , date , boolean e money . Questa tabella conterrà milioni di righe con molti molti null. Temo che i valori nulli occuperanno spazio.

Inoltre, sai se Postgres indicizza valori nulli? Mi piacerebbe impedirgli di indicizzare i null.

Fondamentalmente, i valori NULL occupano 1 bit nella bitmap NULL. Ma non è così semplice.

La bitmap null (per riga) è presente solo se almeno una colonna in quella riga contiene un valore NULL . Ciò può portare a un effetto paradosso nelle tabelle con 9 o più colonne: l’assegnazione del primo valore NULL a una colonna può occupare più spazio sul disco rispetto alla scrittura di un valore. Viceversa, con l’ultima colonna che diventa non nulla, la bitmap null viene rilasciata per la riga.

Fisicamente, la bitmap null iniziale occupa 1 byte tra HeapTupleHeader (23 byte) e i dati effettivi delle colonne o l’ OID della riga (se si dovrebbe continuare a utilizzarlo), che inizia sempre con un multiplo di MAXALIGN (in genere 8 byte ). Ciò lascia 1 byte di riempimento che viene utilizzato dalla bitmap null iniziale.

In effetti l’ archiviazione NULL è assolutamente gratuita per tabelle con 8 colonne o meno .
Successivamente, un altro MAXALIGN byte (tipicamente 8) viene assegnato per le successive colonne MAXALIGN * 8 (in genere 64). Eccetera.

Maggiori dettagli nel manuale e sotto queste domande correlate:

  • Quanto spazio su disco è necessario per memorizzare un valore NULL utilizzando il DB postgresql?
  • Non usa NULL in PostgreSQL ancora usa un bitmap NULL nell’intestazione?
  • Quanti dischi posso archiviare in 5 MB di PostgreSQL su Heroku?

Una volta compreso il riempimento dell’allineamento dei tipi di dati, puoi ottimizzare ulteriormente lo spazio di archiviazione:

  • Calcolo e risparmio di spazio in PostgreSQL

Ma i casi sono rari in cui è ansible salvare notevoli quantità di spazio. Normalmente non ne vale la pena.

@Daniel copre già gli effetti sulla dimensione dell’indice.

Se i valori NULL arrivano all’indice o non dipendono almeno dal tipo dell’indice. Fondamentalmente, questo sarebbe per i tipi di indice btree e gist , NO per hash , e sembra SÌ o NO per i tipi di indice gin seconda della versione di PostgreSQL.

C’era una colonna booleana amindexnulls nella tabella pg_catalog.pg_am che trasportava quell’informazione, ma è pg_catalog.pg_am in 9.1. Probabilmente perché gli indici sono diventati ancora più sofisticati tra i miglioramenti del PG.

Nel caso specifico dei tuoi dati, il modo migliore per conoscere sarebbe misurare la differenza di dimensione degli indici, usando la funzione pg_relation_size('index_name') , tra i contenuti interamente NULL e interamente NOT NULL, con la tua esatta versione PG, esatto tipo di dati , tipo di indice esatto e definizione. E sappi che, probabilmente, un cambiamento futuro in uno di questi parametri potrebbe cambiare il risultato.

Ma in ogni caso, se “solo” vuoi evitare di indicizzare NULL, è sempre ansible creare un indice parziale:

 CREATE INDEX partial_idx(col) ON table WHERE (col is not null) 

Ci vorrà meno spazio, ma se questo dipenderà o meno dalle prestazioni delle query dipende da queste query.

Credo che ognuno utilizzerebbe un singolo bit nella bitmap per la riga. Vedi qui: http://www.postgresql.org/docs/9.0/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE