Economize muito dinheiro em grandes volumes no PostgreSQL

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

O PostgreSQL usa um tamanho de página fixo (geralmente 8 KB) e não permite que tuplas se espalhem por várias páginas. Portanto, é impossível armazenar diretamente valores de campo muito grandes. Para superar essa limitação, valores de campo grandes são compactados e / ou divididos em várias linhas físicas. Isso passa despercebido pelo usuário e afeta a maior parte do código do servidor. Este método é conhecido como TOAST ...

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

Reduza o efeito TOAST

• EXTENDED permite compactação e armazenamento separado. Esta é a opção padrão para a maioria dos tipos de dados compatíveis com o TOAST. Primeiro, é feita uma tentativa de executar a compactação e, em seguida, ela é salva fora da tabela se a linha ainda for muito grande.
• PRINCIPAL permite compactação, mas não armazenamento separado. (De fato, o armazenamento separado, no entanto, será executado para essas colunas, mas apenas como último recurso , quando não houver outra maneira de reduzir a linha para que ela caiba na página.)

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Como avaliar o efeito

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

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

O código de processamento TOAST é acionado somente quando o valor da linha a ser armazenada na tabela for maior que TOAST_TUPLE_THRESHOLD bytes (geralmente 2 KB). O código TOAST comprime e / ou move os valores dos campos para fora da tabela até que o valor da linha seja menor que TOAST_TUPLE_TARGET bytes (variável, geralmente também 2 KB) ou se torna impossível reduzir o tamanho.

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Muitas arquiteturas fornecem alinhamento de dados através dos limites das palavras de máquina. Por exemplo, em um sistema x86 de 32 bits, números inteiros (tipo inteiro, ocupa 4 bytes) serão alinhados no limite de palavras de 4 bytes, além de números de ponto flutuante de precisão dupla (tipo de precisão dupla, 8 bytes). E em um sistema de 64 bits, os valores duplos serão alinhados na borda das palavras de 8 bytes. Esse é outro motivo de incompatibilidade.

Devido ao alinhamento, o tamanho da linha da tabela depende da ordem dos campos. Normalmente, esse efeito não é muito perceptível, mas, em alguns casos, pode levar a um aumento significativo no tamanho. Por exemplo, se você colocar campos dos tipos char (1) e número inteiro misto, entre eles, como regra, 3 bytes serão desperdiçados 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)