Skip to main content

Partitioned tables

A partitioned table is many tables behind one name. Queries that filter on the partition column skip the rest, and dropping a whole partition frees its space at once, where deleting the same rows would take hours and leave the table bloated. It's a good fit for data that's mostly appended and kept for a fixed time.

Declaring one​

type Event struct {
	ID         int64
	OccurredAt time.Time
}

func (Event) Configure(b *ant.ModelBuilder[Event]) {
	b.CompositeKey(ant.F(&E.ID), ant.F(&E.OccurredAt))
	b.PartitionByRange(ant.F(&E.OccurredAt))
}

var E = ant.Fields[Event]()

Migrations create the parent table. A partitioned table's primary key and unique constraints must include the partition column, because PostgreSQL only enforces uniqueness per partition, so the key is composite.

Keeping the months​

Partitions come and go on a schedule, so they aren't migrations, and drift ignores them. postgres.MonthlyPartitions looks after them.

months, err := postgres.MonthlyPartitions[Event](db)
if err != nil {
	return err
}

created, err := months.Ensure(ctx, time.Now(), 3) // this month and the next three
existing, err := months.List(ctx)                 // what exists
dropped, err := months.DropBefore(ctx, cutoff)    // whole months that ended by cutoff

Ensure is safe to repeat, and to run from several instances. Run it at startup, since a row for a month with no partition is an error, and again on a schedule.

A foreign key to a partitioned table needs the whole key, which a relation can't express. Keep the relation for loading, and declare the constraint apart.

type Person struct {
	ID         int64
	EventID    int64
	OccurredAt time.Time
	Event      *Event
}

func (Person) Configure(b *ant.ModelBuilder[Person]) {
	b.PartitionByRange(ant.F(&Pn.OccurredAt))
	b.BelongsTo(&Pn.Event).ForeignKey(&Pn.EventID).References(&E.ID).NoForeignKey().NoIndex()
	b.ForeignKey(ant.F(&Pn.EventID), ant.F(&Pn.OccurredAt)).
		References(ant.F(&E.ID), ant.F(&E.OccurredAt)).
		OnDelete(ant.Cascade)
}

var Pn = ant.Fields[Person]()

Children copy the parent's time, which puts both rows in matching months. Name both columns in joins, so PostgreSQL pairs each month with its own.

query := db.People.Query().
	JoinOn[Event](ant.EqCol(&Pn.EventID, &E.ID), ant.EqCol(&Pn.OccurredAt, &E.OccurredAt))

Drop children first: DropBefore on Person, then on Event.

Limits​

  • .Concurrently() doesn't work on a partitioned table.
  • A row outside every partition is an error. A default partition would hide that, then block creating the month later.
  • Filter on the partition column wherever you can, or PostgreSQL searches every month.

The audit example is built on all of this, with retention on a cron schedule.