Dar sentido a los tamaños de filas de Postgres

Obtuve una tabla Postgres grande (> 100M filas) con estructura {entero, entero, entero, marca de tiempo sin zona horaria}. Esperaba que el tamaño de una fila fuera 3 * entero + 1 * indicación de tiempo = 3 * 4 + 1 * 8 = 20 bytes.

En realidad, el tamaño de la fila es pg_relation_size(tbl) / count(*) = 52 bytes. ¿Por qué?

(No se realizan eliminaciones en la tabla: pg_relation_size(tbl, 'fsm') ~ = 0)

El cálculo del tamaño de fila es mucho más complejo que eso.

El almacenamiento generalmente está dividido en páginas de datos de 8 kb. Hay una pequeña sobrecarga fija por página, posibles residuos no lo suficientemente grandes como para caber en otra tupla, y lo más importante, filas muertas o un porcentaje inicialmente reservado con la configuración de FILLFACTOR .

Más importante aún, hay gastos generales por fila (tupla). HeapTupleHeader de 23 bytes y relleno de alineación . El inicio del encabezado de tupla, así como el inicio de los datos de tupla, están alineados en un múltiplo de MAXALIGN , que es de 8 bytes en una máquina típica de 64 bits. Algunos tipos de datos requieren alineación al siguiente múltiplo de 2, 4 u 8 bytes.

Citando el manual en la tabla del sistema pg_tpye :

typalign es la alineación requerida al almacenar un valor de este tipo. Se aplica al almacenamiento en disco así como a la mayoría de las representaciones del valor dentro de PostgreSQL. Cuando se almacenan varios valores consecutivamente, como en la representación de una fila completa en el disco, el relleno se inserta antes de un dato de este tipo para que comience en el límite especificado. La referencia de alineación es el comienzo del primer dato en la secuencia.

Los valores posibles son:

  • c = alineación de caracteres, es decir, no se necesita alineación.

  • s = alineación short (2 bytes en la mayoría de las máquinas).

  • i = alineación int (4 bytes en la mayoría de las máquinas).

  • d = double alineación (8 bytes en muchas máquinas, pero de ninguna manera todas).

Lea sobre los conceptos básicos en el manual aquí .

Tu ejemplo

Esto da como resultado 4 bytes de relleno después de las 3 columnas de integer , porque la columna de timestamp requiere double alineación y debe comenzar en el siguiente múltiplo de 8 bytes.

Entonces, una fila ocupa:

  23 -- heaptupleheader + 1 -- padding or NULL bitmap + 12 -- 3 * integer (no alignment padding here) + 4 -- padding after 3rd integer + 8 -- timestamp + 0 -- no padding since tuple ends at multiple of MAXALIGN 

Finalmente, hay un puntero ItemData (puntero del elemento) por tupla en el encabezado de la página (como lo señala @AH en el comentario ) que ocupa 4 bytes:

  + 4 -- item pointer in page header ------ = 52 bytes 

Entonces llegamos a los 52 bytes observados.

El cálculo pg_relation_size(tbl) / count(*) es una estimación pesimista. pg_relation_size(tbl) incluye bloat (filas muertas) y el espacio reservado por fillfactor , así como la sobrecarga por página de datos y por tabla. (Y ni siquiera mencionamos la compresión para los datos de Varlena largos en tablas TOAST , ya que no se aplica aquí).

Puede instalar el módulo adicional pgstattuple y llamar a SELECT * FROM pgstattuple('tbl_name'); para obtener más información sobre el tamaño de la mesa y la tupla.

Respuesta relacionada:

  • Tamaño de la tabla con diseño de página

Cada fila tiene metadatos asociados a ella. La fórmula correcta es (asumiendo una alineación ingenua):

 3 * 4 + 1 * 8 == your data 24 bytes == row overhead total size per row: 23 + 20 

O aproximadamente 53 bytes. De hecho, escribí postgresql-varint específicamente para ayudar con este problema con este caso de uso exacto. Es posible que desee consultar una publicación similar para obtener más detalles sobre la sobrecarga de tupla.