Mukesh Asthana
Database architecture

Cutting month-end and Power BI refresh time with PostgreSQL partitioning and indexing

By Mukesh Asthana, Database Architect · · 8 min read

Month-end is when a reporting database shows its real shape. Batch jobs that run comfortably for 29 days suddenly scan years of history, and the Power BI datasets sitting on top re-import everything at the same time. On a recent telecom engagement, my team cut month-end processing and dataset refresh time by about 70%. None of it was exotic. It came from three disciplines applied in order: measure, reshape the data, then stop moving data you've already moved.

1. Measure before you touch anything

Tuning without evidence usually means adding indexes that never get used. Start with what the database is actually spending time on.

-- Top statements by total time (needs the pg_stat_statements extension)
SELECT queryid,
       calls,
       round(total_exec_time::numeric / 1000, 1) AS total_s,
       round(mean_exec_time::numeric, 1)         AS mean_ms,
       rows,
       left(query, 120)                          AS sample
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

Then take the worst offenders and run them with EXPLAIN (ANALYZE, BUFFERS) on a production-sized copy. The things to look for are sequential scans over large tables when the query only needs one period, row estimates that are off by orders of magnitude (stale statistics), and sorts or hashes spilling to disk.

2. Partition by the way the business reads the data

Month-end reporting almost always filters on a period. If the fact tables are partitioned on that same column, PostgreSQL can skip every partition that can't match, which is called partition pruning.

CREATE TABLE usage_fact (
    usage_id      bigint       NOT NULL,
    account_id    bigint       NOT NULL,
    billing_month date         NOT NULL,
    amount        numeric(14,2),
    loaded_at     timestamptz  NOT NULL DEFAULT now()
) PARTITION BY RANGE (billing_month);

CREATE TABLE usage_fact_2026_10 PARTITION OF usage_fact
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Three rules keep partitioning from backfiring:

  • Filter on the partition key directly. WHERE billing_month >= '2026-10-01' prunes; WHERE to_char(billing_month,'YYYYMM') = '202610' does not.
  • Keep partition counts sensible. Monthly partitions for multi-year data are fine; daily partitions over a decade make planning slower than scanning.
  • Automate partition creation ahead of time, in a scheduled job, so a load never fails at midnight on the first of the month.

Partitioning also makes retention cheap. Archiving a closed year becomes ALTER TABLE ... DETACH PARTITION instead of a multi-hour DELETE that bloats the table.

3. Index for real predicates, not for columns

An index is worth having when it matches how a query filters and joins. Composite indexes should lead with equality columns and end with range columns. A few patterns did most of the work for us:

  • Composite B-tree on (account_id, billing_month) for per-account lookups within a period.
  • Partial indexes for hot subsets, for example WHERE status = 'OPEN', so the index stays small.
  • BRIN indexes on append-only timestamp columns such as loaded_at: tiny on disk and very effective when rows arrive in time order.

Just as important is removing indexes nobody uses. Check pg_stat_user_indexes for idx_scan = 0 over a full business cycle; every unused index slows every insert during the load window.

4. Refactor the queries that remain slow

After partitioning and indexing, the remaining slow statements are usually shaped badly rather than missing an index. The usual fixes:

  • Replace correlated subqueries in the SELECT list with a join or a single aggregate pass.
  • Aggregate before joining when one side is much larger than the other.
  • Replace row-by-row PL/pgSQL loops with set-based INSERT ... SELECT statements.
  • Run ANALYZE on freshly loaded partitions before the reports run, so the planner isn't working from last month's statistics.

5. Stop re-importing history into Power BI

A dataset that re-imports five years every night is doing 59 months of unnecessary work. Power BI's incremental refresh uses two parameters, RangeStart and RangeEnd, to load only recent periods. For that to be fast, the filter has to fold: Power Query must push it down to PostgreSQL as a WHERE clause on the partition key. Check folding with "View native query" in Power Query, and expose a clean view rather than letting the report build complex SQL itself.

The combination matters. Incremental refresh asks for one month; the partition key lets PostgreSQL read one partition; the index finds the rows inside it. Each step on its own helps a little. Together they remove most of the work.

What to check afterwards

  • Re-run the pg_stat_statements query after a full month-end and compare total time per statement.
  • Watch autovacuum on the current partition; heavy update loads may need table-level autovacuum settings.
  • Document the partition and refresh policy so the next team doesn't undo it.

If you're planning a similar exercise on Oracle, the same principles apply with range partitioning, local indexes and materialised views. The order doesn't change: measure, reshape, then move less data.

More articles