Блог

Главная - Вопросы по программированию - Эффективно хранить большую таблицу в PostgreSQL?

Эффективно хранить большую таблицу в PostgreSQL?

#postgresql

#postgresql

Вопрос:

У меня есть таблица в postgres 11, которая содержит ~ 500 миллионов строк и имеет общий размер около 57 ГБ, как показано в следующем запросе.

 SELECT pg_size_pretty(pg_table_size('my_table')) AS data_size,
pg_size_pretty(pg_relation_size('idx_my_table')) AS index_size,
pg_size_pretty(pg_table_size('my_table')   pg_relation_size('idx_my_table')) AS data_plus_index_size,
pg_size_pretty(pg_total_relation_size('my_table')) AS total_relation_size;

введите описание изображения здесь

Таблица имеет следующую структуру:

 create table my_table
(
    id bigserial not null
        constraint pk_my_table
            primary key,
    id_from integer not null,
    id_to integer not null,
    distance numeric not null
);

create index idx_id_from_to
    on my_table (id_from, id_to, distance);

Таблица считывается очень часто. Данные в таблице меняются не очень часто, а индекс idx_id_from_to делает запросы очень быстрыми.

Что я хотел бы знать, так это то, есть ли более эффективные способы (разные типы данных и т.д.) для хранения этой таблицы, чтобы уменьшить ее размер?

Мне часто приходится копировать эту таблицу в другое место, а размер делает этот процесс длительным, даже с использованием инструментов дампа.

Комментарии:

1. Ответ зависит от данных, хранящихся в таблице. Можете ли вы быть уверены, что у вас никогда не будет id больше 2147483647? каковы диапазоны значений для id_from и id_to ? Вам нужно точное число для distance ? Какова требуемая точность? В общем, не ожидайте существенной экономии места.

2. distance может быть десятичным / плавающим, например, 1.2465. Я почти уверен, что его можно с уверенностью сказать id , что он не превысит 2147483647. id_from и id_to будет находиться в диапазоне от 0 до 200 000

3. pg_relation_size('idx_my_table') У вас, кажется, нет индекса с таким именем?

Ответ №1:

Это потенциальная экономия:

  • используйте integer for id , если вы абсолютно уверены, что значение никогда не превысит 2147483647
  • используйте real distance , если вы можете жить с точностью в несколько цифр

В зависимости от того, как долго numeric это было, это может сэкономить вам около 6 или 10 байт на строку, возможно, около 4 ГБ места. Я не уверен, стоит ли это хлопот и потери точности.

Ответ №2:

Postgresql имеет высокие накладные расходы на строки, как на строку таблицы, так и на запись индекса. Вы мало что можете сделать с этими накладными расходами при использовании обычного метода доступа к куче PostgreSQL. Вы изучали некоторые расширения, такие как timescaledb или cstore_fdw?

Метки: