Embed Notice

HTML Code

<blockquote style="position: relative; padding-left: 55px;"><section><a href="https://eientei.org/objects/9662b7e0-e857-43ab-a92b-823c47111784">Yukkuri (iamtakingiteasy@eientei.org)'s status on Sunday, 02-Mar-2025 06:41:30 JST</a><a href="https://eientei.org/users/iamtakingiteasy" title="iamtakingiteasy@eientei.org"><img src="https://gnusocial.jp/avatar/2644-48-20220729235443.webp" width="48" height="48" alt="Yukkuri" style="position: absolute; left: 0; top: 0;">Yukkuri</a><div><a href="https://eientei.org/objects/fafbe44b-51a9-469e-a9cc-95a4d877693c" rel="in-reply-to">in reply to</a><ul><li></ul></div></section><article><p>(2/2)<br><br>With such schema/query changes it is possible now to partition objects_data/activities_data tables at any granularity.<br><br>For example, for activites, on yearly basis:</p>alter table activities_data rename to activities_data_old;-- drop indexes, foreign keys on activities_data_oldcreate table activities_data (
  id uuid not null primary key,                                                                        
  data jsonb not null,
  inserted_at timestamp(0) without time zone not null,
  updated_at timestamp(0) without time zone not null
) partition by range (id);create table if not exists activities_data_2020 partition of activities_data
for values from ('0000016f-5e66-e800-0000-000000000000') to ('00000176-bb3e-7000-0000-000000000000');create table if not exists activities_data_2021 partition of activities_data
for values from ('00000176-bb3e-7000-0000-000000000000') to ('0000017e-12ef-9c00-0000-000000000000');create table if not exists activities_data_2022 partition of activities_data
for values from ('0000017e-12ef-9c00-0000-000000000000') to ('00000185-6aa0-c800-0000-000000000000');create table if not exists activities_data_2023 partition of activities_data
for values from ('00000185-6aa0-c800-0000-000000000000') to ('0000018c-c251-f400-0000-000000000000');create table if not exists activities_data_2024 partition of activities_data
for values from ('0000018c-c251-f400-0000-000000000000') to ('00000194-1f29-7c00-0000-000000000000');create table if not exists activities_data_2025 partition of activities_data
for values from ('00000194-1f29-7c00-0000-000000000000') to ('0000019b-76da-a800-0000-000000000000');create table if not exists activities_data_2026 partition of activities_data
for values from ('0000019b-76da-a800-0000-000000000000') to ('000001a2-ce8b-d400-0000-000000000000');insert into activities_data select * from activities_data_old;-- recreate indexes, recreate foreign keys on activities_data
<p>And it would work transparently. Partitions can be either added manually for upcoming years, or added on-demand in insert trigger.<br><br>On eientei DB snapshot such partitioning takes 1-3 hours.<br><br>Naturally, due to attributes denormalization there will be some overhead, but a majority of old partitions could now be extracted to separate tablespace (on another disk/raid entirely, e.g. to hdd freeing up ssd space).<br><br>For example using eientei numbers, in (data|indexes)<br><br>before any schema changes:</p><ul><li>objects (65G|22G)</li><li>activities (79G|67G)</li></ul><p>after schema change and partitioning:</p><ul><li>objects_attributes (7G|8G)</li><li>objects_2020 (1M|1M)</li><li>objects_2021 (8G|2G)</li><li>objects_2022 (15G|4G)</li><li>objects_2023 (15G|4G)</li><li>objects_2024 (21G|6G)</li><li>objects_2025 (3G|1G)</li><li>activities_attributes (47G|53G)</li><li>activities_data_2020 (1M|1M)</li><li>activities_data_2021 (6G|500M)</li><li>activities_data_2022 (12G|1200M)</li><li>activities_data_2023 (15G|1500M)</li><li>activities_data_2024 (21G|6G)</li><li>activities_data_2025 (4G|300M)</li></ul><p>which is a ~15% total overhead, but ~60% of total size can be moved to hdd for cold online storage by doing:</p>create tablespace archive location '/mnt/coldstorage/postgres/archive';
alter table activities_data_2020 set tablespace archive;
alter table activities_data_2021 set tablespace archive;
alter table activities_data_2022 set tablespace archive;
alter table activities_data_2023 set tablespace archive;
alter table activities_data_2024 set tablespace archive;
...alter table objects_2020 set tablespace archive;
alter table objects_2021 set tablespace archive;
alter table objects_2022 set tablespace archive;
alter table objects_2023 set tablespace archive;
alter table objects_2024 set tablespace archive;
...
<p>while keeping only current year on fast ssd.</p></article><footer><a rel="bookmark" href="https://gnusocial.jp/conversation/4656843#notice-9115687">In conversation</a><time datetime="2025-03-02T06:41:30+09:00" title="Sunday, 02-Mar-2025 06:41:30 JST">about a month ago</time> <span>from <span><a href="https://eientei.org/objects/9662b7e0-e857-43ab-a92b-823c47111784" rel="external" title="Sent from eientei.org via ActivityPub">eientei.org</a></span></span><a href="https://eientei.org/objects/9662b7e0-e857-43ab-a92b-823c47111784">permalink</a></footer></blockquote>

Corresponding Notice

Embed this notice
Yukkuri (iamtakingiteasy@eientei.org)'s status on Sunday, 02-Mar-2025 06:41:30 JSTYukkuri
in reply to
- Yukkuri
(2/2)

With such schema/query changes it is possible now to partition objects_data/activities_data tables at any granularity.

For example, for activites, on yearly basis:
alter table activities_data rename to activities_data_old; -- drop indexes, foreign keys on activities_data_old create table activities_data ( id uuid not null primary key, data jsonb not null, inserted_at timestamp(0) without time zone not null, updated_at timestamp(0) without time zone not null ) partition by range (id); create table if not exists activities_data_2020 partition of activities_data for values from ('0000016f-5e66-e800-0000-000000000000') to ('00000176-bb3e-7000-0000-000000000000'); create table if not exists activities_data_2021 partition of activities_data for values from ('00000176-bb3e-7000-0000-000000000000') to ('0000017e-12ef-9c00-0000-000000000000'); create table if not exists activities_data_2022 partition of activities_data for values from ('0000017e-12ef-9c00-0000-000000000000') to ('00000185-6aa0-c800-0000-000000000000'); create table if not exists activities_data_2023 partition of activities_data for values from ('00000185-6aa0-c800-0000-000000000000') to ('0000018c-c251-f400-0000-000000000000'); create table if not exists activities_data_2024 partition of activities_data for values from ('0000018c-c251-f400-0000-000000000000') to ('00000194-1f29-7c00-0000-000000000000'); create table if not exists activities_data_2025 partition of activities_data for values from ('00000194-1f29-7c00-0000-000000000000') to ('0000019b-76da-a800-0000-000000000000'); create table if not exists activities_data_2026 partition of activities_data for values from ('0000019b-76da-a800-0000-000000000000') to ('000001a2-ce8b-d400-0000-000000000000'); insert into activities_data select * from activities_data_old; -- recreate indexes, recreate foreign keys on activities_data
And it would work transparently. Partitions can be either added manually for upcoming years, or added on-demand in insert trigger.

On eientei DB snapshot such partitioning takes 1-3 hours.

Naturally, due to attributes denormalization there will be some overhead, but a majority of old partitions could now be extracted to separate tablespace (on another disk/raid entirely, e.g. to hdd freeing up ssd space).

For example using eientei numbers, in (data|indexes)

before any schema changes:
- objects (65G|22G)
- activities (79G|67G)
after schema change and partitioning:
- objects_attributes (7G|8G)
- objects_2020 (1M|1M)
- objects_2021 (8G|2G)
- objects_2022 (15G|4G)
- objects_2023 (15G|4G)
- objects_2024 (21G|6G)
- objects_2025 (3G|1G)
- activities_attributes (47G|53G)
- activities_data_2020 (1M|1M)
- activities_data_2021 (6G|500M)
- activities_data_2022 (12G|1200M)
- activities_data_2023 (15G|1500M)
- activities_data_2024 (21G|6G)
- activities_data_2025 (4G|300M)
which is a ~15% total overhead, but ~60% of total size can be moved to hdd for cold online storage by doing:
create tablespace archive location '/mnt/coldstorage/postgres/archive'; alter table activities_data_2020 set tablespace archive; alter table activities_data_2021 set tablespace archive; alter table activities_data_2022 set tablespace archive; alter table activities_data_2023 set tablespace archive; alter table activities_data_2024 set tablespace archive; ... alter table objects_2020 set tablespace archive; alter table objects_2021 set tablespace archive; alter table objects_2022 set tablespace archive; alter table objects_2023 set tablespace archive; alter table objects_2024 set tablespace archive; ...
while keeping only current year on fast ssd.
In conversationabout a month ago from eientei.orgpermalink

Public

Embed Notice

HTML Code

Corresponding Notice