Dynamic creation of list partitions in highly concurrent write environment

Поиск
Список
Период
Сортировка
От Jim Vanns
Тема Dynamic creation of list partitions in highly concurrent write environment
Дата
Msg-id CAH7vdhOeJ59QsogfWi-zY-AKRomVQjzRHzXH699AHxo_kvD09w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Dynamic creation of list partitions in highly concurrent write environment
Список pgsql-general
Hello everyone. 

We are attempting to evaluate list partitioning over hash partitioning (which we currently use) to better facilitate dropping tables that distinctly model devices we wish to purge. We don't want to use a DML statement for cleanup since these tables can contain billions of rows otherwise. Using PG15, we've hit a snag;

We're attempting to use declarative partitioning syntax in a function run as an after trigger; we hit deadlocks. We try the older inheritance alternative, we still hit deadlocks. I appreciate this is generally because creating tables isn't a concurrently supported operation. Here's the general approach we have, without code and with hypothetical model, since I'm writing this hastily;

table devices; <-- After trigger on here
table routes; <-- To create physical partition for this logical table
table route_for_device_N; <-- The dynamically created partition

We don't know the device names and thus partitions up front, hence the need for dynamic creation. How do you go about doing this or is it folly!?

Cheers

Jim

--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

В списке pgsql-general по дате отправления:

Предыдущее
От: Lian Jiang
Дата:
Сообщение: Re: speed up full table scan using psql
Следующее
От: Marc Millas
Дата:
Сообщение: Re: Hash Index on Partitioned Table