Блог

Изначально мой столбец jsonb выглядит так

 {
   addio: { percent : "10.0000"},
   inputId: 'c2a06eb0-ccf6-4845-bf33-a930f3790f37',
   discount: { percent : "10.0000"},
   some: { percent : "10.0000"},
   details: [
       { 
         amount: "10.0000",
         systemItemPriceCode: 'A',
         systemItemCategoryCode: 'B',
         systemItemPriceNumberOfShelfs: 4
       },
       { 
         amount: "5.0000",
         systemItemPriceCode: 'B',
         systemItemCategoryCode: 'C',
         systemItemPriceNumberOfShelfs: 4
       },
    ],
   field: { percent : "10.0000"},
   retention: { percent : "10.0000"},
   customerTesterPropertyItemMode: { percent : "10.0000"}
}
  

Размер моей таблицы с 18594 строками составляет 8472 КБ;

Я решил оптимизировать его дальше, сделав ключ на одну букву короче и полностью удалив некоторые ключи, такие как systemItemCategoryCode и systemItemPriceNumberOfShelfs .

 {
   a: { p : "10.0000"},
   i: 'c2a06eb0-ccf6-4845-bf33-a930f3790f37',
   d: { p : "10.0000"},
   s: { p : "10.0000"},
   l: [
       { 
         a: "10.0000",
         c: 'A'
       },
       { 
         a: "5.0000",
         c: 'B'
       },
    ],
   f: { p : "10.0000"},
   r: { p : "10.0000"},
   m: { p : "10.0000"}
}
  

Однако размер таблицы с 18594 строками (все остальное то же самое, изменен только формат JSONB) составляет 10088 КБ!!

1. Что произошло, почему размер увеличился, я чувствовал, что удалил много материала.
2. Если это касается Toast, как я могу получить точный размер, чтобы знать, что я действительно экономлю место?

Для вычисления размера используется следующий код https://wiki.postgresql.org/wiki/Disk_Usage в разделе Общие сведения о размере таблицы, сгруппированные для секционированных таблиц

Минимальный код для воспроизведения поведения

 -- TABLE CREATION
CREATE TABLE jsonb_big (
    jsonb_col JSONB NOT NULL
);

CREATE TABLE jsonb_optimized (
    jsonb_col JSONB NOT NULL
);

-- CLEAN TABLE
DELETE  FROM jsonb_big WHERE TRUE;
DELETE  FROM jsonb_optimized WHERE TRUE;

-- POPULATE TABLE
INSERT INTO jsonb_big (jsonb_col) SELECT '{"hxxx": {"pxxxxxx": "0.00000"}, "lxxxxxx": [{"axxxxx": "2000.00000", "cxxxxxxxxxxxxxxxxxx": "A", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "1000.00000", "cxxxxxxxxxxxxxxxxxx": "B", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "500.00000", "cxxxxxxxxxxxxxxxxxx": "C", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D01", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D02", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D03", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D04", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D05", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D06", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D07", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D08", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D09", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "250.00000", "cxxxxxxxxxxxxxxxxxx": "D10", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E01", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E02", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E03", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E04", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E05", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E06", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E07", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E08", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E09", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}, {"axxxxx": "75.00000", "cxxxxxxxxxxxxxxxxxx": "E10", "yxxxxxxxxxxxxxxxxxxxxx": "TES", "zxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx": 4}], "dxxxxxxxxx": {"pxxxxxx": "31.00000"}}'
FROM generate_series(1, 20000);
INSERT INTO jsonb_optimized (jsonb_col) SELECT '{"d": {"p": "31.00000"}, "h": {"p": "0.00000"}, "l": [{"a": "2000.00000", "c": "A"}, {"a": "1000.00000", "c": "B"}, {"a": "500.00000", "c": "C"}, {"a": "250.00000", "c": "D01"}, {"a": "250.00000", "c": "D02"}, {"a": "250.00000", "c": "D03"}, {"a": "250.00000", "c": "D04"}, {"a": "250.00000", "c": "D05"}, {"a": "250.00000", "c": "D06"}, {"a": "250.00000", "c": "D07"}, {"a": "250.00000", "c": "D08"}, {"a": "250.00000", "c": "D09"}, {"a": "250.00000", "c": "D10"}, {"a": "75.00000", "c": "E01"}, {"a": "75.00000", "c": "E02"}, {"a": "75.00000", "c": "E03"}, {"a": "75.00000", "c": "E04"}, {"a": "75.00000", "c": "E05"}, {"a": "75.00000", "c": "E06"}, {"a": "75.00000", "c": "E07"}, {"a": "75.00000", "c": "E08"}, {"a": "75.00000", "c": "E09"}, {"a": "75.00000", "c": "E10"}]}'
FROM generate_series(1, 20000);

-- VACUUM TABLE
VACUUM FULL;

-- CALCULATE SIZE
WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
    (select inhrelid, inhparent
    FROM pg_inherits
    UNION
    SELECT child.inhrelid, parent.inhparent
    FROM pg_inherit child, pg_inherits parent
    WHERE child.inhparent = parent.inhrelid),
pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT table_schema
    , TABLE_NAME
    , row_estimate,
       total_bytes as total_bytes
    , pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
    SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
    FROM (
         SELECT c.oid
              , nspname AS table_schema
              , relname AS TABLE_NAME
              , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
              , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
              , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
              , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
              , parent
          FROM (
                SELECT pg_class.oid
                    , reltuples
                    , relname
                    , relnamespace
                    , pg_class.reltoastrelid
                    , COALESCE(inhparent, pg_class.oid) parent
                FROM pg_class
                    LEFT JOIN pg_inherit_short ON inhrelid = oid
                WHERE relkind IN ('r', 'p')
             ) c
             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  ) a
  WHERE oid = parent
) a WHERE table_schema ='public'
ORDER BY total_bytes DESC ;