Support partitions

[vensko]

I recently found a great use for Postgres partitions beyond huge action logs. They appear to be a great way to store soft deleted records using the LIST partition method. I mark soft deleted records either with booleans, or enum statuses, and Postgres does scan only the main table, skipping the table with deleted records when it's not needed.

As a result, I'm making raw schemas in Laravel migrations. It would be great to have partition support in your library.

[tpetry]

Can you share an example of your raw schema commands?

[vensko]

Simplified example from my current project:

        DB::unprepared('
            CREATE TABLE files (
                id bigserial NOT NULL,
                user_id int8 NOT NULL,
                file_status int2 NOT NULL DEFAULT \'2\'::smallint,
                file_name varchar(255) NOT NULL
            )
            PARTITION BY LIST (file_status);
        ');

        DB::unprepared('CREATE TABLE files_published PARTITION OF files DEFAULT WITH (
            fillfactor=90,
            autovacuum_analyze_scale_factor=0.02
        )');

        DB::unprepared('CREATE TABLE files_deleted PARTITION OF files FOR VALUES IN (0)');

enum FileStatus: int
{
    case DELETED = 0;
    case QUARANTINED = 1;
    case PUBLISHED = 2;
}

All soft-deleted files get moved to files_deleted automatically, and files_deleted is not scanned in most scenarios at all, Postgres skips it just fine when I query records with file_status > 0.

PS Most people would use partition by range, since it's a better documented use case.

[vensko]

Another use case - model versioning. Old versions can be automatically stored in a separate partition.