Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Обслуживание секций  [new]
DSKalugin
Member

Откуда: Мать городов русских
Сообщений: 379
Есть секционированная таблица ru.propose_sod и её секция private.propose_sod_ru_2019 с данными за 2019г размером 1,8ТБ
Планирую заменить период секционирования с "годового" на "помесячный" (слишком медленно выполняются запросы + сложности в обслуживании секций)
Можно ли в наполненной секции заменить верхнюю границу диапазона с '2020-01-01' на '2019-11-01'?
Не повлечет ли это действие риск потери данных или выделение дополнительного пространства на диске (который и так переполнен) или блокировку таблицы на длительное время... ?
-- секционированная таблица
CREATE TABLE ru.propose_sod (
  id BIGSERIAL,
  receive_id INTEGER DEFAULT 0 NOT NULL,
  date_begin DATE NOT NULL,
  balance_begin REAL DEFAULT 0 NOT NULL,
  quantity_credit REAL DEFAULT 0 NOT NULL,
  price_credit REAL DEFAULT 0 NOT NULL,
  quantity_debit REAL DEFAULT 0 NOT NULL,
  price_debit REAL DEFAULT 0 NOT NULL,
  drugs_sha1 VARCHAR(40),
  flag_set SMALLINT DEFAULT 0 NOT NULL
) 
PARTITION BY RANGE (date_begin)
WITH (oids = false);
-- секция
CREATE TABLE private.propose_sod_ru_2019 PARTITION OF ru.propose_sod(
  CONSTRAINT pk_propose_sod_ru_2019 PRIMARY KEY(id),
  CONSTRAINT fk_propose_sod_ru_2019_r FOREIGN KEY (receive_id)
    REFERENCES ru.receive_day(id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) 
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01')
WITH (oids = false)
TABLESPACE arch_global;

CREATE INDEX propose_sod_ru_2019_date_begin_idx ON private.propose_sod_ru_2019
  USING btree (date_begin)
  TABLESPACE arch_global;

CREATE INDEX propose_sod_ru_2019_drugs_sha1 ON private.propose_sod_ru_2019
  USING btree (drugs_sha1 COLLATE pg_catalog."default")
  TABLESPACE arch_global;

CREATE INDEX propose_sod_ru_2019_rcv_id ON private.propose_sod_ru_2019
  USING btree (receive_id)
  TABLESPACE arch_global;
-- мастер
CREATE TABLE ru.receive_day (
  id SERIAL,
  organization_id INTEGER DEFAULT 0 NOT NULL,
  date_receive TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  date_begin DATE,
  date_end DATE,
  price_sum DOUBLE PRECISION DEFAULT 0 NOT NULL,
  total_positions INTEGER DEFAULT 0 NOT NULL,
  unknown_positions INTEGER DEFAULT 0 NOT NULL,
  hash_data VARCHAR(40) DEFAULT ''::character varying NOT NULL,
  flag_set INTEGER DEFAULT 0 NOT NULL,
  meta JSONB,
  CONSTRAINT pk_ps_receive_so_ru PRIMARY KEY(id)
) 
WITH (oids = false);
CREATE INDEX ps_receive_so_ru_idx_db ON ru.receive_day
  USING btree (date_begin);

CREATE INDEX ps_receive_so_ru_idx_o ON ru.receive_day
  USING btree (organization_id);


PostgreSQL 11 / Ubuntu / Размер БД 7,2ТБ / Заполненность диска 96%(!)
18 окт 19, 14:46    [21997477]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
lr2
Member

Откуда:
Сообщений: 136
+
SET friday_mode = ON;


Сначала хорошо бы сделать VACUUM FULL.
18 окт 19, 14:51    [21997483]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1246
alter table private.propose_sod_ru_2019 add constraint tmp_attach check (date_begin >= '2019-01-01' and date_begin < '2019-11-01') not valid;
alter table private.propose_sod_ru_2019 validate constraint tmp_attach;
alter table ru.propose_sod DETACH PARTITION  private.propose_sod_ru_2019;
alter table ru.propose_sod ATTACH PARTITION private.propose_sod_ru_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-11-01');
alter table private.propose_sod_ru_2019 drop constraint tmp_attach;


без check attach partition будет проверять данные на соответствие ограничению. (seqscan в один поток без индексов)
18 окт 19, 14:55    [21997491]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
DSKalugin
Member

Откуда: Мать городов русских
Сообщений: 379
lr2
Сначала хорошо бы сделать VACUUM FULL.

Это повлечет за собой блокировку и выделение места ~ 1,8ТБ для пересоздания таблицы. А места уже нет :-(
18 окт 19, 15:14    [21997512]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
DSKalugin
Member

Откуда: Мать городов русских
Сообщений: 379
Melkij, спасибо!
18 окт 19, 15:46    [21997536]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Добрый вечер!

Подскажите пожалуйста, а можно где-нибудь в Postgres посмотреть границы уже созданной секции RANGE?
13 май 20, 18:13    [22132318]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
DSKalugin
Member

Откуда: Мать городов русских
Сообщений: 379
MacArrow,

в DDL секции
22 май 20, 16:58    [22137819]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
DSKalugin,

Спасибо! Это понятно, особенно теоретически...

А есть какие-то таблицы, представления, "хранимки", из которых по запросу можно получить необходимые данные о секции, в том числе и границы диапазона?
15 июн 20, 15:07    [22151054]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1246
MacArrow
А есть какие-то таблицы, представления, "хранимки", из которых по запросу можно получить необходимые данные о секции, в том числе и границы диапазона?

Технически это pg_class.relpartbound. Но это pg_node_tree. pg_dump и psql с ним работают просто как с данностью (psql вызывает именно так):
SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '159622' ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
В результате получается всё выражение текстом, например "FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00')"
15 июн 20, 16:20    [22151114]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Melkij,

Большое спасибо!

А вот ещё вопрос: можно ли по значению ключа секции определить саму секцию (имя)?

Сообщение было отредактировано: 18 июн 20, 22:29
18 июн 20, 22:24    [22153278]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1246
MacArrow,

емнип tuple routing из backend'а не экспортирован никак.
19 июн 20, 13:36    [22153629]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Melkij,

Понятно! Спасибо!
28 июн 20, 15:59    [22158602]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Написано:
"Создайте в секционируемой таблице индекс, будет автоматически создан отдельный индекс в каждой секции, и все секции, которые вы будете создавать или присоединять позднее, тоже будут содержать такой индекс".

Вопрос:
Если индекс создан в "голову" уже после создания секций (очень многих секций, причём в разных таблицах разными методами), есть ли "штатная" возможность создать индексы по всем секциям?
Или же поможет только самописная процедура, которая всё это сделает?
Буду весьма благодарен за конструктивный ответ.
11 авг 20, 21:06    [22181098]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1246
MacArrow
Если индекс создан в "голову" уже после создания секций (очень многих секций, причём в разных таблицах разными методами), есть ли "штатная" возможность создать индексы по всем секциям?

create index на головной раздел по всем секциям индексы сам и создаст.

А вот если нужен create index concurrently - то только ручками. Оживление сейчас по этому вопросу есть, может в pg14 и войдёт даже.
12 авг 20, 12:37    [22181421]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Melkij,

И снова огромнейшее спасибо!

Я имел в виду, что при создании секции, в этой же секции создаются индексы по "головному". Если это concurrently, то да, он.
Просто столкнулись с такой проблемой: потребовался новый индекс на секционированную таблицу, создали в "голову", но на быстродействие запроса это никак не повлияло... Ради эксперимента отдетачили и приаттачили секции по одной из таблиц, индексы в секциях создались и запрос выполнился много быстрее.

Сообщение было отредактировано: 13 авг 20, 11:15
13 авг 20, 11:15    [22181824]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1246
MacArrow,

уточните как именно индекс создавался. Конкретный запрос.
create index on table_name - создаст индексы по всему дереву
create index concurrently on table_name - откажется работать, т.к. поддержка пока не реализована
create index on only table_name - создаст invalid индекс только на головном разделе и так и задумано.
13 авг 20, 13:07    [22181895]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Melkij,

Я извиняюсь за нубство в этом вопросе, т.к. не вникал особо, как Постгрес сам делает индексы на секции...
Но если конкретно, то алгоритм такой:
- при создании таблицы, скриптом же создавался индекс по ключевому столбцу, например:
+ скриптики
CREATE TABLE balance (id serial, acc_id bigint, in_amt numeric, dt_amt numeric, ct_amt numeric, out_amt numeric, op_dt date) PARTITION BY RANGE (op_dt);
CREATE INDEX balance_op_dt_idx ON balance (op_dt);

- дальше, перед загрузкой вызывается АПИ, где создаётся секция (примерно как тут) и автоматом ПГ создаёт индекс на секцию.
Поискав по DDL, нашёл для этих секций связанные скрипты создания индексов, например, вот один из них (получается, что никакого concurrently):
+ скриптики
CREATE INDEX balance_20200701_op_dt_idx ON balance_20200701 USING btree (op_dt);

- уже после загрузки данных потребовалось ввести новый индекс на acc_id, который создали в "голову":
+ скриптики
CREATE INDEX balance_acc_id_idx ON balance (acc_id);

Но он не возымел никакого действия на быстродействие
Как я понял опытным путём, это из-за того, что не были созданы индексы на секциях (выявлено отсутствием DDL-скриптов для индексов, привязанных к этим самым секциям).
Дальше Вы мне подсказали, что индексы на секции "штатными" средствами ПГ никак быстро не создать, а только ручками... Как выяснилось теперь, Вы имели в виду что-то другое
Тем не менее, самописной процедурой, где отдетачили и снова приаттачили все секции, были всё-таки (полу-)автоматически созданы так необходимые индексы на каждую секцию.
В результате получили необходимое быстродействие выполнения запросов.
20 авг 20, 20:50    [22185125]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание секций  [new]
MacArrow
Member

Откуда:
Сообщений: 49
Да, забыл...
Соответственно, в результате выполнения этой "самописной процедуры detach&attach", появились DDL-скрипты для индексов, привязанных к этим самым секциям, например такой:
+ скриптики
CREATE INDEX balance_20200701_acc_id_idx ON balance_20200701 USING btree (acc_id);


P.S. Если что, то эти самые скрипты (да и сами индексы и другие объекты БД) я вижу при помощи DBeaver.

Сообщение было отредактировано: 20 авг 20, 21:12
20 авг 20, 21:12    [22185132]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить