Proactively moving to hourly partitions for Upbound Hub v1.1.2+

Last updated: October 8, 2026

Goal

In an attempt to reduce the total size of what is stored in the database for the hub, v1.1.2 moves the partitions in the hub’s inbox back to hourly (pre-v1.1.2 it was set as weekly). In general, the hub should be creating up to 4 weeks of partitions in advance, which means rolling out the new hourly management would take ~3 weeks to roll over automatically. This guide is written up to help you move to hourly partitions more quickly.

Environment

Upbound Hub v1.1.1 · BYO Postgres · EKS · Kubernetes 1.36

Observed with 2 or more connected control planes syncing a large number of resources.

Prerequisites

Before Upgrading

As an optional step before upgrading, you can run the following query to capture the names of the current partitions:

SELECT c.relname AS partition, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'public.inbox_messages'::regclass
ORDER BY c.relname;

Steps

After upgrading: Drop the empty future weekly partitions

Wait until every hub-core pod runs 1.1.2. A pod still on 1.1.1 recreates weekly partitions four weeks ahead.

  1. List the weekly partitions that start in the future. The current week's partition is not listed: leave it, since it holds live messages. Cleanup drops it 24 hours after the week ends.

    SELECT c.relname AS partition, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'public.inbox_messages'::regclass
  AND c.relname ~ '_w[0-9]{2}$'
  AND substring(pg_get_expr(c.relpartbound, c.oid) FROM 'FROM \(''([^'']+)''\)')::timestamp
      > (now() AT TIME ZONE 'UTC')
ORDER BY c.relname;

  2. For each partition listed, confirm it is empty. Expect false. NOTE: The next query is an example that requires input from the above.

    SELECT EXISTS (SELECT 1 FROM public.inbox_messages_2026_w42);

  3. Detach it, then drop it. Run each statement on its own: a concurrent detach fails inside a transaction, so don't send them as one batch. If the detach fails because another partition is being detached, cleanup is mid-run. Wait a minute and retry.

    ALTER TABLE public.inbox_messages DETACH PARTITION public.inbox_messages_2026_w42 CONCURRENTLY;
DROP TABLE public.inbox_messages_2026_w42;

  4. Nothing more is needed unless you finish within 2 hours of the week's end (Monday 00:00 UTC). Hourly maintenance creates the hourly partitions for the following week once they are within 23 hours. If you are that close, restart hub-core so it creates them now: kubectl -n <namespace> rollout restart deployment/<hub-core deployment>.

Verify

  • Partitions: rerun the partition list from before upgrading. Weekly partitions that ended more than 24 hours ago are gone after the first cleanup, about a minute after start; very large ones take longer. No future _wNN partitions remain. Hourly partitions for the next week appear the day before the current week ends.

  • Logs: hub-core logs built partition index for each partition after its first cleanup. failed to build partition indexes means a build ran out of time; see the next section.

  • The message count: this plans the query without running it. Expect Index Only Scan on every partition holding messages.

    EXPLAIN SELECT tenant, status, COUNT(*) FROM public.inbox_messages
WHERE status IN ('pending', 'processing', 'failed')
  AND created_at >= (now() AT TIME ZONE 'UTC') - interval '24 hours'
GROUP BY tenant, status;

Related