Почему моя таблица измерений даты бесполезна? (Путаница с хранилищем PostgreSQL...)

Question

Я просмотрел это около 4 раз и все еще озадачен этими результатами.

Взгляните на следующее (которое я изначально разместил здесь)

Таблица измерений даты -

-- Some output omitted

DROP TABLE IF EXISTS dim_calendar CASCADE;

CREATE TABLE dim_calendar (
  id SMALLSERIAL PRIMARY KEY,
  day_id DATE NOT NULL,
  year SMALLINT NOT NULL, -- 2000 to 2024
  month SMALLINT NOT NULL, -- 1 to 12
  day SMALLINT NOT NULL, -- 1 to 31
  quarter SMALLINT NOT NULL, -- 1 to 4
  day_of_week SMALLINT NOT NULL, -- 0 () to 6 ()
  day_of_year SMALLINT NOT NULL, -- 1 to 366
  week_of_year SMALLINT NOT NULL, -- 1 to 53
  CONSTRAINT con_month CHECK (month >= 1 AND month <= 31),
  CONSTRAINT con_day_of_year CHECK (day_of_year >= 1 AND day_of_year <= 366), -- 366 allows for leap years
  CONSTRAINT con_week_of_year CHECK (week_of_year >= 1 AND week_of_year <= 53),
  UNIQUE(day_id)
);

INSERT INTO dim_calendar (day_id, year, month, day, quarter, day_of_week, day_of_year, week_of_year) (
  SELECT ts, 
  EXTRACT(YEAR FROM ts),
  EXTRACT(MONTH FROM ts),
  EXTRACT(DAY FROM ts),
  EXTRACT(QUARTER FROM ts),
  EXTRACT(DOW FROM ts),
  EXTRACT(DOY FROM ts),
  EXTRACT(WEEK FROM ts)
  FROM generate_series('2000-01-01'::timestamp, '2024-01-01', '1day'::interval) AS t(ts)
);

/* ==> [ INSERT 0 8767 ] */

Столы для тестирования -

DROP TABLE IF EXISTS just_dates CASCADE;
DROP TABLE IF EXISTS just_date_ids CASCADE;

CREATE TABLE just_dates AS
  SELECT a_date AS some_date
  FROM some_table;

/* ==> [ SELECT 769411 ] */

CREATE TABLE just_date_ids AS
  SELECT d.id
  FROM just_dates jd
  INNER JOIN dim_calendar d
  ON d.day_id = jd.some_date;

/* ==> [ SELECT 769411 ] */

ALTER TABLE just_date_ids ADD CONSTRAINT jdfk FOREIGN KEY (id) REFERENCES dim_calendar (id);

Путаница -

pocket=# SELECT pg_size_pretty(pg_relation_size('dim_calendar'));

pg_size_pretty 
----------------
448 kB
(1 row)

pocket=# SELECT pg_size_pretty(pg_relation_size('just_dates'));
pg_size_pretty 
----------------
27 MB
(1 row)

pocket=# SELECT pg_size_pretty(pg_relation_size('just_date_ids'));
pg_size_pretty 
----------------
27 MB
(1 row)

Почему таблица, состоящая из группы smallints, имеет такой же размер, как таблица, состоящая из группы дат? И я должен упомянуть об этом раньше, когда dim_calendar.id был нормальным SERIALдала то же самое 27MB результат.

Кроме того, и что более важно - ПОЧЕМУ делает таблицу с 769411 записи с одним полем smallint имеют размер 27MB, который > 32bytes/record???

PS Да, у меня будет миллиарды (или, как минимум, сотни миллионов) записей, и я стараюсь по возможности добавлять оптимизацию производительности и пространства.

РЕДАКТИРОВАТЬ

Это может иметь какое-то отношение к этому, так что выбросить это там -

pocket=# select count(id) from just_date_ids group by id;
 count  
--------
 409752
 359659
 (2 rows)

Answer 1

В таблицах с одним или двумя столбцами наибольшую часть размера всегда составляет заголовок кортежа.

Загляните сюда http://www.postgresql.org/docs/current/interactive/storage-page-layout.html, там объясняется, как хранятся данные. Я цитирую ту часть страницы, которая наиболее актуальна для вашего вопроса.

Все строки таблицы структурированы одинаково. Существует заголовок фиксированного размера (занимающий 23 байта на большинстве машин), за которым следует необязательное нулевое растровое изображение, необязательное поле идентификатора объекта и пользовательские данные.

Это в основном объясняет вопрос

ПОЧЕМУ таблица с 769411 записями с одним полем smallint имеет размер 27 МБ, что> 32 байт / запись???

Другая часть вашего вопроса связана с выравниванием байтов данных postgres. Smallints выровнены в 2-байтовых смещениях, но в целых числах (и датах конечно... date является int4 в конце концов) выровнены в 4 байтовых смещения. Таким образом, порядок, в котором столбцы таблицы разрабатываются, играет важную роль.

Имея таблицу с smallint, date, smallint требует 12 байтов для пользовательских данных (не считая служебных данных), а для объявления smallint, smallint, date потребуется только 8 байтов. Смотрите отличный (и удивительно не принятый) ответ здесь. Расчет и экономия места в PostgreSQL.