pg_partman

pg_partman — a Postgres extension for managing partitioned tables: auto-creating new partitions, dropping old ones, maintaining time- or range-based partitions. ide99 provides UI instead of manually calling partman_create_parent / partman_run_maintenance.

Why partition

When a table exceeds ~100M rows, operations on it (vacuum, index rebuild, deleting old data) become painful. Partitioning splits the table into chunks (by date, ID range, hash) — each chunk is smaller and processed independently.

Postgres has native partitioning (PARTITION BY RANGE / LIST / HASH). pg_partman automates:

Creating partitions ahead of time (e.g., 30 days into the future)
Dropping old partitions per retention policy
Background maintenance

Activate

CREATE EXTENSION pg_partman;

In ide99, in Schema Browser, partitioned tables (or partitioning candidates) get a Partman panel.

Creating a partman parent

If you have a regular table and want to partition it:

Create a partitioned parent table:

CREATE TABLE events_p (
  id          BIGSERIAL,
  event_at    TIMESTAMPTZ NOT NULL,
  payload     JSONB
) PARTITION BY RANGE (event_at);

In ide99, right-click events_p → Configure with pg_partman.
Fill the form:
- Interval: daily, weekly, monthly, yearly, custom interval
- Premake: how many partitions ahead (default 4)
- Retention: auto-drop partitions older than N intervals
- Retention keep table: when dropping, keep as detached (for archive)
Apply calls partman.create_parent().

Maintenance

partman.run_maintenance() needs to be called regularly (cron or pg_cron). It creates new partitions ahead of time and drops old ones.

In ide99, the Run maintenance now button in the Partman panel calls it manually. Useful after config changes or for verification.

For production automation, set up cron or pg_cron. ide99 itself doesn't run cron — it's a desktop app.

View partitions

In the tree, the events_p table has child nodes for each partition:

events_p (parent, 12 partitions)
├─ events_p_p2026_05
├─ events_p_p2026_06
├─ events_p_p2026_07
└─ ...

Click a partition — see size, row count, indexes.

Change configuration

Right-click the partman parent → Configure. The form opens with current settings. Change them → ide99 shows the SQL and applies:

UPDATE partman.part_config
SET retention = '90 days',
    premake = 7
WHERE parent_table = 'public.events_p';

The next run_maintenance will apply the new rules.

Remove partman config

Right-click → Disable partman tracking. Calls partman.undo_partition() or partman.partition_data_proc() depending on options. Partitions stay; only automation is removed.

Full table deletion — via Object Editor → Drop table… with CASCADE. Be careful.

Hash and list partitioning

ide99 only supports RANGE via partman through UI (the most common case — by time). For HASH and LIST you'd manage via raw Postgres SQL — no UI help, but Schema Browser still shows the partition tree.

Schema Browser — where the Partman panel appears.
pg_repack — for one-shot reorganization (different task from partitioning).