Partitioned tables

[ptman]

Have you got any ideas how to handle partitioned tables? I'm thinking of our usecase, but I believe it to be quite common. Range-based partitioning by timestamp column. In our case, monthly partitions for some tables.

Dump at least dumps the partitions, which isn't that useful. We also use sqlc which then ends up creating structs for the partitions.

[peterldowns]

Sorry, I don't understand the question. The most helpful explanation would be an example scenario like this:

• Given some schema...
• Pgmigrate currently does X when you run Y commands ...
• But you'd rather it did Z

I haven't worked much with partitioned tables so I'm sure that there is a way pgmigrate could be improved for your use case.

[ptman]

Suppose we have a schema like this:

create table events (
    created timestamp with time zone not null default now(),
    event text
) partition by range (created);

create table events_p20250101 partition of events for values from ('2025-01-01 00:00:00Z') to ('2025-02-01 00:00:00Z');

Fixing the dates in the schema migrations does not make sense. But they will exist in pg when the schema gets dumped. Partitions don't really describe the schema, the shape of the data. They are an implementation detail and should be managed separately from migrations.

So I propose a way to either not dump tables that are partitions of other tables or some other way to ignore specific tables (with a regex?) to get the same end result.

[ptman]

Oh, also, dump loses the partition by range so information doesn't roundtrip 100%.