Ahorre mucho dinero en grandes volúmenes en PostgreSQL

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt --  
    date
, data --  
    text
, PRIMARY KEY(pack, recno)
);

PostgreSQL utiliza un tamaño de página fijo (generalmente 8 KB) y no permite que las tuplas abarquen varias páginas. Por lo tanto, es imposible almacenar directamente valores de campo muy grandes. Para superar esta limitación, los valores de campo grandes se comprimen y / o dividen en varias líneas físicas. Esto pasa desapercibido para el usuario y afecta ligeramente a la mayoría del código del servidor. Este método se conoce como TOAST ...

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Reduce el efecto TOSTADA

• EXTENDIDO permite tanto la compresión como el almacenamiento por separado. Esta es la opción estándar para la mayoría de los tipos de datos compatibles con TOAST. Primero, se intenta realizar la compresión, luego se guarda fuera de la tabla si la fila sigue siendo demasiado grande.
• PRINCIPAL permite la compresión, pero no el almacenamiento por separado. (De hecho, se realizará un almacenamiento por separado para tales columnas, pero solo como último recurso , cuando no haya otra forma de reducir la fila para que se ajuste a la página).

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

¿Cómo evaluar el efecto?

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

El código de procesamiento de TOAST se activa solo cuando el valor de fila que se almacenará en la tabla es mayor que TOAST_TUPLE_THRESHOLD bytes (generalmente 2 Kb). El código de TOAST comprimirá y / o moverá los valores de campo fuera de la tabla hasta que el valor de la fila sea menor que TOAST_TUPLE_TARGET bytes (variable, generalmente también 2 KB) o sea imposible reducir el tamaño.

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Muchas arquitecturas proporcionan la alineación de datos a través de los límites de palabras de máquina. Por ejemplo, en un sistema x86 de 32 bits, los enteros (tipo entero, ocupa 4 bytes) se alinearán en el borde de las palabras de 4 bytes, así como los números de coma flotante de precisión doble (tipo de precisión doble, 8 bytes). Y en un sistema de 64 bits, los valores dobles se alinearán en el borde de las palabras de 8 bytes. Este es otro motivo de incompatibilidad.

Debido a la alineación, el tamaño de la fila de la tabla depende del orden de los campos. Por lo general, este efecto no es muy notable, pero en algunos casos puede conducir a un aumento significativo de tamaño. Por ejemplo, si coloca campos de tipos char (1) y enteros mezclados, entre ellos, por regla general, se perderán 3 bytes por nada.

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 

CREATE TABLE public.plan_20190220
(
--  from table plan:  pack uuid NOT NULL,
--  from table plan:  recno smallint NOT NULL,
--  from table plan:  host uuid,
--  from table plan:  ts timestamp with time zone,
--  from table plan:  exectime numeric(32,3),
--  from table plan:  duration numeric(32,3),
--  from table plan:  bufint bigint,
--  from table plan:  bufmem bigint,
--  from table plan:  bufdsk bigint,
--  from table plan:  apn uuid,
--  from table plan:  ptr uuid,
--  from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

CREATE TABLE public.plan_20190221
(
--  from table plan:  dt date NOT NULL,
--  from table plan:  ts timestamp with time zone,
--  from table plan:  pack uuid NOT NULL,
--  from table plan:  recno smallint NOT NULL,
--  from table plan:  host uuid,
--  from table plan:  apn uuid,
--  from table plan:  ptr uuid,
--  from table plan:  bufint bigint,
--  from table plan:  bufmem bigint,
--  from table plan:  bufdsk bigint,
--  from table plan:  exectime numeric(32,3),
--  from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)