Économisez beaucoup d'argent sur de gros volumes dans PostgreSQL

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

PostgreSQL utilise une taille de page fixe (généralement 8 Ko) et ne permet pas aux tuples de s'étendre sur plusieurs pages. Par conséquent, il est impossible de stocker directement de très grandes valeurs de champ. Pour surmonter cette limitation, les grandes valeurs de champ sont compressées et / ou divisées en plusieurs lignes physiques. Cela se passe inaperçu par l'utilisateur et affecte légèrement la plupart du code du serveur. Cette méthode est connue sous le nom de TOAST ...

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

Réduit l'effet TOAST

• EXTENDED permet à la fois la compression et le stockage séparé. Il s'agit de l' option standard pour la plupart des types de données compatibles TOAST. Tout d'abord, une tentative est effectuée pour effectuer la compression, puis elle est enregistrée en dehors du tableau si la ligne est toujours trop grande.
• MAIN permet la compression, mais pas un stockage séparé. (En fait, un stockage séparé sera cependant effectué pour ces colonnes, mais uniquement en dernier recours , lorsqu'il n'y a pas d'autre moyen de réduire la ligne pour qu'elle tienne sur la page.)

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Comment évaluer l'effet

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

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

Le code de traitement TOAST n'est déclenché que lorsque la valeur de la ligne à stocker dans la table est supérieure à TOAST_TUPLE_THRESHOLD octets (généralement 2 Ko). Le code TOAST compressera et / ou déplacera les valeurs de champ hors de la table jusqu'à ce que la valeur de la ligne soit inférieure à TOAST_TUPLE_TARGET octets (variable, généralement 2 Ko également) ou qu'il devienne impossible de réduire la taille.

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

De nombreuses architectures assurent l'alignement des données au-delà des limites des mots machine. Par exemple, sur un système x86 32 bits, les entiers (type entier, occupent 4 octets) seront alignés sur la bordure des mots de 4 octets, ainsi que les nombres à virgule flottante double précision (type double précision, 8 octets). Et sur un système 64 bits, les valeurs doubles seront alignées sur la bordure des mots de 8 octets. C'est une autre raison de l'incompatibilité.

En raison de l'alignement, la taille de la ligne du tableau dépend de l'ordre des champs. Habituellement, cet effet n'est pas très visible, mais dans certains cas, il peut entraîner une augmentation significative de la taille. Par exemple, si vous placez des champs de types char (1) et entier mélangés, entre eux, en règle générale, 3 octets seront gaspillés pour rien.

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)