Calcular y ahorrar espacio en PostgreSQL
Tengo una tabla en la página así:
CREATE TABLE t (
a BIGSERIAL NOT NULL, -- 8 b
b SMALLINT, -- 2 b
c SMALLINT, -- 2 b
d REAL, -- 4 b
e REAL, -- 4 b
f REAL, -- 4 b
g INTEGER, -- 4 b
h REAL, -- 4 b
i REAL, -- 4 b
j SMALLINT, -- 2 b
k INTEGER, -- 4 b
l INTEGER, -- 4 b
m REAL, -- 4 b
CONSTRAINT a_pkey PRIMARY KEY (a)
);
Lo anterior suma 50 bytes por fila. Mi experiencia es que necesito otro 40% a 50% para la sobrecarga del sistema, sin siquiera ningún índice creado por el usuario para lo anterior. Entonces, alrededor de 75 bytes por fila. Tendré muchas, muchas filas en la tabla, potencialmente más de 145 mil millones de filas, por lo que la tabla ocupará entre 13 y 14 terabytes. ¿Qué trucos, si los hay, podría utilizar para compactar esta mesa? Mis posibles ideas a continuación...
Convierta los realvalores a integer. Si se pueden almacenar como smallint, eso supone un ahorro de 2 bytes por campo.
Convierta las columnas b .. m en una matriz. No necesito buscar en esas columnas, pero sí necesito poder devolver el valor de una columna a la vez. Entonces, si necesito la columna g, podría hacer algo como
SELECT a, arr[5] FROM t;
¿Ahorraría espacio con la opción de matriz? ¿Habría una penalización por velocidad?
¿Alguna otra idea?
"Columna Tetris"
En realidad, puedes hacer algo , pero esto requiere una comprensión más profunda. La palabra clave es relleno de alineación . Cada tipo de datos tiene requisitos de alineación específicos .
Puede minimizar el espacio perdido por el relleno entre columnas ordenándolas favorablemente. El siguiente ejemplo (extremo) desperdiciaría mucho espacio en disco físico:
CREATE TABLE t (
e int2 -- 6 bytes of padding after int2
, a int8
, f int2 -- 6 bytes of padding after int2
, b int8
, g int2 -- 6 bytes of padding after int2
, c int8
, h int2 -- 6 bytes of padding after int2
, d int8)
Para guardar 24 bytes por fila, utilice en su lugar:
CREATE TABLE t (
a int8
, b int8
, c int8
, d int8
, e int2
, f int2
, g int2
, h int2) -- 4 int2 occupy 8 byte (MAXALIGN), no padding at the end
db<>violín aquí
Antiguo sqlfiddle
Como regla general, si coloca primero las columnas de 8 bytes, luego las de 4 bytes, 2 bytes y 1 byte al final, no puede equivocarse.
boolean, uuid(!) y algunos otros tipos no necesitan relleno de alineación. texty varcharotros tipos "varlena" (longitud variable) nominalmente requieren alineación "int" (4 bytes en la mayoría de las máquinas). Pero no observé ningún relleno de alineación en el formato de disco (a diferencia de la RAM). Finalmente, encontré la explicación en una nota en el código fuente:
Tenga en cuenta también que permitimos que se viole la alineación nominal al almacenar varlenas "empaquetadas"; el mecanismo TOAST se encarga de ocultarlo de la mayoría del código.
Por lo tanto, la alineación "int" solo se aplica cuando el dato (posiblemente comprimido) que incluye un único byte de longitud inicial excede los 127 bytes. Luego, el almacenamiento de varlena cambia a cuatro bytes iniciales y requiere alineación "int".
Normalmente, puede guardar un par de bytes por fila, en el mejor de los casos, jugando al "tetris de columnas" . Nada de esto es necesario en la mayoría de los casos. Pero con miles de millones de filas, puede significar fácilmente un par de gigabytes.
Puede probar el tamaño real de columna/fila con la función pg_column_size().
Algunos tipos ocupan más espacio en la RAM que en el disco (formato comprimido o "empaquetado"). Puede obtener mejores resultados para las constantes (formato RAM) que para las columnas de la tabla cuando prueba el mismo valor (o fila de valores frente a fila de la tabla) con pg_column_size().
Finalmente, algunos tipos pueden comprimirse o "tostarse" (almacenarse fuera de línea) o ambas cosas.
Siempre que sea posible, mueva NOT NULLlas columnas al frente y las columnas con muchos NULLvalores al final. NULLlos valores se sirven directamente desde el mapa de bits nulo, por lo que su posición en la fila no influye en el costo de acceso de los NULLvalores, pero agregan un pequeño costo para calcular el desplazamiento de las columnas ubicadas a la derecha (más atrás en la fila).
Gastos generales por tupla (fila)
4 bytes por fila para el identificador del artículo; no sujeto a las consideraciones anteriores.
Y al menos 24 bytes (23 + relleno) para el encabezado de la tupla. El manual sobre diseño de página de base de datos:
Hay un encabezado de tamaño fijo (que ocupa 23 bytes en la mayoría de las máquinas), seguido de un mapa de bits nulo opcional, un campo de ID de objeto opcional y los datos del usuario.
Para el relleno entre el encabezado y los datos del usuario, necesita saber MAXALIGNen su servidor: generalmente 8 bytes en un sistema operativo de 64 bits (o 4 bytes en un sistema operativo de 32 bits). Si no estás seguro, consulta pg_controldata.
Ejecute lo siguiente en su directorio binario de Postgres para obtener una respuesta definitiva:
./pg_controldata /path/to/my/dbcluster
El manual:
Los datos reales del usuario (columnas de la fila) comienzan en el desplazamiento indicado por
t_hoff, que siempre debe ser múltiplo de laMAXALIGNdistancia de la plataforma.
Por lo tanto, normalmente se obtiene el almacenamiento óptimo empaquetando los datos en múltiplos de 8 bytes.
No hay nada que ganar con el ejemplo que publicaste . Ya está bien embalado. 2 bytes de relleno después del último int2, 4 bytes al final. Podrías consolidar el relleno en 6 bytes al final, lo que no cambiaría nada.
Gastos generales por página de datos
El tamaño de la página de datos suele ser de 8 KB. También hay algo de sobrecarga/inflación en este nivel: los restos no son lo suficientemente grandes como para caber en otra tupla y, lo que es más importante, filas muertas o un porcentaje reservado con la FILLFACTORconfiguración .
Hay un par de factores más a tener en cuenta para el tamaño del disco:
- ¿Cuántos registros puedo almacenar en 5 MB de PostgreSQL en Heroku?
- ¿No usar NULL en PostgreSQL todavía usa un mapa de bits NULL en el encabezado?
- Configuración de PostgreSQL para rendimiento de lectura
¿Tipos de matriz?
Con un tipo de matriz como el que estaba evaluando, agregaría 24 bytes de sobrecarga para el tipo. Además, los elementos de la matriz ocupan espacio como de costumbre. Nada que ganar allí.
De esta excelente documentación: https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
Para una tabla que ya tiene, o quizás una que esté creando en desarrollo, llamada my_table, esta consulta le dará el orden óptimo de izquierda a derecha.
SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'my_table'
AND a.attnum >= 0
ORDER BY t.typlen DESC
No veo nada que ganar (y algo que perder) al almacenar varios campos numéricos en una matriz.
El tamaño de cada tipo numérico está claramente documentado; simplemente debe utilizar el tipo más pequeño compatible con el rango de resolución deseado; y eso es todo lo que puedes hacer.
No creo (pero no estoy seguro) que exista algún requisito de alineación de bytes para las columnas a lo largo de una fila, en ese caso un reordenamiento de las columnas podría alterar el espacio utilizado, pero no lo creo.
Por cierto, hay una sobrecarga fija por fila, aproximadamente 23 bytes .
Aquí hay una herramienta interesante sobre la sugerencia de reordenamiento de las columnas de Erwin: https://github.com/NikolayS/postgres_dba
Tiene el comando exacto para eso: p1:
Luego, automáticamente le muestra el potencial real de reordenación de las columnas en todas sus tablas:
Después de leer las respuestas de Erwin Brandstetter y jboxxx y el documento vinculado en este último, mejoré ligeramente la consulta para hacerla más versátil:
-- https://www.postgresql.org/docs/current/catalog-pg-type.html
CREATE OR REPLACE VIEW tabletetris
AS SELECT n.nspname, c.relname,
a.attname, t.typname, t.typstorage, t.typalign, t.typlen
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE a.attnum >= 0
ORDER BY n.nspname ASC, c.relname ASC,
t.typlen DESC, t.typalign DESC, a.attnum ASC;
Úselo así:
SELECT * FROM tabletetris WHERE relname='mytablename';
Pero puede agregar un filtro nspname(el esquema en el que se encuentra la tabla).
También agregué el tipo de almacenamiento, que es información útil para determinar cuáles -1insertar y/u ordenar dónde, y mantener el orden relativo de las columnas existentes con la misma clave de clasificación.
