Блог

Главная - Вопросы по программированию - Динамическое разбиение таблицы по идентификатору строки в Postgres

Динамическое разбиение таблицы по идентификатору строки в Postgres

#postgresql #partitioning #database-partitioning

#postgresql #разбиение #база данных -разбиение

Вопрос:

Я хотел бы разделить таблицу в Postgres на ранее неизвестное значение. В моем сценарии этим значением будет device_id, который является строкой.

Это текущая ситуация:

Таблица ‘device_data’ — хранит данные датчиков, которые отправляются с устройств, определенных DDL: 

 CREATE TABLE warehouse.device_data (
  id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('device_data_id_seq'::regclass),
  device_id TEXT NOT NULL,
  device_data BYTEA NOT NULL,
--   contains additional fields which are omitted for brevity
  received_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

В настоящее время таблица содержит миллионы записей, и запросы занимают огромное количество времени. Большинство запросов содержат WHERE device_id='something' предложение.

Решение, которое я имею в виду, — создать разделы таблиц для каждого device_id .

Возможно ли в Postgres создавать разделы таблиц для каждого device_id ?

Я просмотрел документацию Postgres и пару примеров, которые я нашел, но все они используют фиксированные границы для создания разделов. Мое решение потребует:

  1. создание нового раздела таблицы «на лету» при device_id первом появлении нового
  2. сохранить в существующий раздел, если device_id он уже известен, и раздел для него device_id уже существует

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

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

1. почему вы хотите разбить на разделы device_id ? простой индекс для device_id выполнит эту работу. Вопросы производительности должны включать EXPLAIN ANALYZE и некоторую информацию о размере таблицы, индексе, производительности в текущее время, требуемом времени и т. Д. Slow это относительный термин, и нам нужно реальное значение для сравнения.

2. Взгляните на pg_pathman . Это инструмент для упрощения управления разделами в PostgreSQL, и он специально поддерживает HASH стратегию разделения. Недостатком является то, что количество разделов фиксировано и выбирается при инициализации набора разделов.

Ответ №1:

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

Измените тип id to int default 0 и вручную создайте последовательность, чтобы избежать нескольких nextval() вызовов при одной вставке:

 create table device_data (
    id int primary key default 0,
    device_id text not null,
    device_data text not null, -- changed for tests
    received_at timestamp without time zone default now()
);
create sequence device_data_seq owned by device_data.id;

Используйте динамический sql в триггерной функции:

 create or replace function before_insert_on_device_data()
returns trigger language plpgsql as $$
begin
    execute format(
        $f$
            create table if not exists %I (
            check (device_id = %L)
            ) inherits (device_data)
        $f$, 
        concat('device_data_', new.device_id), 
        new.device_id);
    execute format(
        $f$
            insert into %I
            values (nextval('device_data_seq'), %L, %L, default)
        $f$, 
        concat('device_data_', new.device_id), 
        new.device_id, 
        new.device_data);
    return null;
end $$;

create trigger before_insert_on_device_data
    before insert on device_data
    for each row execute procedure before_insert_on_device_data();

Тест:

 insert into device_data (device_id, device_data) values
    ('first', 'data 1'),
    ('second', 'data 1'),
    ('first', 'data 2'),
    ('second', 'data 2');

select * from device_data_first;

 id | device_id | device_data |        received_at         
---- ----------- ------------- ----------------------------
  1 | first     | data 1      | 2016-10-18 19:50:40.179955
  3 | first     | data 2      | 2016-10-18 19:50:40.179955
(2 rows)

select * from device_data_second;

 id | device_id | device_data |        received_at         
---- ----------- ------------- ----------------------------
  2 | second    | data 1      | 2016-10-18 19:50:40.179955
  4 | second    | data 2      | 2016-10-18 19:50:40.179955
(2 rows)