Embed Notice

(1/2)

Migrating pleroma database to partitioned objects/activities tables seems to be possible with some schema and query alterations.

There are two main problems that could prevent meaningful partitioning

• P1. object ids are not time-ordered
• P2. postgresql does not support global cross-partition indexes and scanning every index of every partition amplifies cross-lookups to a crawl on joins

Countering P1 is easy - by simply changing the type from bigint to uuid updating all relations:

Pleroma uses time-ordered 128-bit flake ids, which are by default structured as

• [64 bits of timestamp in millisecond resolution]
• [48 bits of device identifier]
• [16 bits of sequential counter within the same millisecond]

So inserted_at can be used as a timestamp and original bigint id can be smeared across remaining 64 bits, allowing backward migration.

After dropping all foreign keys on objects-related tables and default sequence generator it's just alter type using, like

Then every objects-related table needs a temporary field to set new uuid alongside their old foreign key bigint and similarly migrated with alter type using that temporary field. Then temporary fields can be dropped and foreign key constraints re-added.

Countering P2 is more complicated, since existing activities/objects tables need to be split further in two: partitioned data table and unpartitioned denormalization/index table.

unpartitioned activities_attributes table must have:

• activity_id foreign key to partitioned activities_data
• object_id (uuid) and object_ap_id (text) in order to correlate with objects without jsonb data
• visibility with pre-computed activity_visibility()
• type with data->>'type'
• context with data->>'context'
• ap_id with data->>'id'
• actor, recipients and local -- currently denormalized fields

unpartitioned objects_attributes table must have:

• object_id foreign key to partitioned objects
• type with data->>'type'
• ap_id with data->>'id'
• actor with data->>'actor'

fts_content can actually be partitioned without much impact to search queries.

To match current interface, activities_data and activities_attributes can be joined with a table without much impact, since data from both is needed in virtually every query.

Objects can remain as plain table since a lot of times it's just lookup by object id without any need for objects_attributes fields, with manual joins in queries where needed.

Both activities view and objects table would need triggers after insert and before delete (or instead in case of a view) to populate corresponding attributes. With it, separate status_visibility_counter_cache_trigger is also no longer needed and can be incorporated into activity triggers, since both can benefit from shared logic.

A proof of concept based on upstream develop supporting schema above, including up/down migrations and necessary query/test changes:

https://gitlab.eientei.org/eientei/pleroma/-/tree/upstream-flake

migration: https://gitlab.eientei.org/eientei/pleroma/-/blob/upstream-flake/priv/repo/migrations/20250109104615_migrate_objects_to_flake_id.exs

On eientei DB snapshot (~230G combined activities and objects) it takes 3-6 hours.