Moving AI reports off the transactional tables: EXPLAIN, materialized views and well-placed indexes in PostgreSQL

Picture your first week deploying a reporting agent for a retail chain. Whenever someone asks for “this week’s revenue by store”, the agent generates SQL and scans the orders table directly: the same table the tills are writing to all day. The demo runs smoothly. In production, the customer’s operations team starts asking why the database is so much busier.

The problem is not the prompt. The agent does not know which tables serve transactions and which are meant for analysis; you have to know for it. This article walks through a process you can try on a laptop with PostgreSQL: set a target, read the execution plan, move the report onto a materialized view, and only then think about indexes.

The schema used here is hypothetical and simplified: a table orders(id, store_id, created_at, amount, is_paid). You need PostgreSQL running locally, permission to create tables and, ideally, a copy of the data large enough for execution plans to resemble production.

Step 1: what number must you hit, and which statements are worth fixing?

Oracle defines SQL tuning as an iterative process aimed at specific, measurable and achievable goals. So before typing any command, write the target down with the customer. A hypothetical example: “the weekly revenue report returns in under two seconds, and the agent running reports does not slow down order writes”.

Next, find the statements worth fixing. Oracle’s documentation advises reviewing execution history to find the statements that account for most of the system’s load and resources, rather than tuning at random.

With an agent, that history usually reveals one thing: a handful of aggregate query patterns repeated over and over with slightly different parameters. Those are the first candidates.

Check: you have a short list of load-heavy statements and a target number the customer has agreed to.

Step 2: read the plan before running anything for real

According to Oracle, the execution plan is the main diagnostic tool for manual tuning. EXPLAIN shows you the plan without running the statement, which makes it the safest step on a customer’s database.

EXPLAIN
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;

For real numbers, you need EXPLAIN ANALYZE. The PostgreSQL documentation is explicit: this command actually executes the query, so any side effects happen as normal. For statements that write data, PostgreSQL recommends wrapping them in a transaction and rolling back.

But do not mistake rollback for safety. Rollback only undoes the data changes; a heavy aggregate query still runs to completion and still weighs on the customer’s transactional database.

For a read-only query like the one below, wrapping it in a transaction saves nothing. Run it on a copy of the data, or during off-peak hours once the operations team has agreed.

EXPLAIN (ANALYZE, BUFFERS)
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;

The BUFFERS option reports I/O at each node of the plan, and PostgreSQL 18 enables it implicitly with ANALYZE. Spelling it out still helps whoever reads your script later.

Step 3: look at row counts, not cost

Newcomers often compare the cost column as if it were milliseconds. It is not. The PostgreSQL documentation says cost units are arbitrary and do not map to real time, and that the most important thing to check is whether the estimated row counts are close to reality.

Suppose the node scanning orders estimates 1,000 rows but actually returns 900,000. The planner chose a strategy for a small set and then had to handle one 900 times larger, and every decision above that node rests on the wrong number. That is the first thing to note, before you even think about indexes.

Check: for each statement on your list, you know which node reads the most buffers and which node’s estimate is furthest off.

Step 4: give the agent a summary to read

Oracle lists missing access structures, such as indexes and materialized views, as a typical cause of slow SQL. For AI reporting, a materialized view is usually the better choice: the agent reads a pre-aggregated table instead of hammering the transactional one.

Microsoft’s description of the Materialized View pattern stresses a valuable property: the view and its data are entirely disposable, because they can always be rebuilt from the source. It is a specialised form of cache, so you can experiment, get it wrong and rebuild without touching the customer’s source data.

CREATE MATERIALIZED VIEW report_daily_store_sales AS
SELECT store_id,
       date_trunc('day', created_at) AS day,
       sum(amount) AS revenue,
       count(*)    AS order_count
FROM orders
GROUP BY 1, 2;

The hardest question at this step is not about SQL. Microsoft advises deciding first how stale the data may acceptably be, and only then choosing between event-driven, scheduled or manual refresh.

If regional managers only look at the report each morning, data a few hours old is fine; if they use it to move stock during the day, it is not. Ask that question during customer discovery rather than guessing.

Step 5: refresh without locking out readers

A plain REFRESH MATERIALIZED VIEW blocks reads of the view while it runs, which means the agent waits. The CONCURRENTLY variant refreshes the view without locking out concurrent SELECTs, but it requires a UNIQUE index and allows only one refresh to run at a time.

CREATE UNIQUE INDEX report_daily_store_sales_key
  ON report_daily_store_sales (store_id, day);

REFRESH MATERIALIZED VIEW CONCURRENTLY report_daily_store_sales;

An easy trap: the PostgreSQL documentation requires that index to use only plain columns, not an expression index and with no WHERE clause. In the example above, date_trunc is already in the view definition, so day is a plain column of the view and the index is valid. If you put date_trunc inside the index, a concurrent refresh will not run.

Do not forget that this view is built from orders: each full refresh reruns the aggregate in the view definition against the transactional table, and Microsoft notes that refreshing costs compute. So refresh as rarely as the staleness threshold from step 4 allows, and schedule it for off-peak hours rather than the middle of a trading shift.

Indexes help the agent read quickly, but they are not free. Microsoft notes that maintaining indexes on a view adds write cost to every refresh cycle. Keep only the indexes the reporting queries actually use.

Check: rerun EXPLAIN for the agent’s query, this time pointed at the view, and compare the buffer counts with step 3.

Step 6: a silently stale view is the most dangerous failure

When a trigger or refresh job is missed, Microsoft warns, the view will silently return stale results. With an agent this is worse than an obvious error: it answers fluently with yesterday’s numbers. The recommendation is to track the time of the last refresh and alert when the view’s age exceeds the agreed threshold.

A simple approach: the refresh job writes its completion time to a small log table, and a periodic check compares that time with the threshold from step 4. You can also let the agent read this timestamp and state “figures as of…” in its answer, so users are not misled.

When should you think about an index table?

Some queries are not aggregates but lookups by a key other than the primary key, such as finding orders by customer code. Microsoft’s Index Table pattern addresses this at the architecture level, with a separately built index table.

Carrying it over to PostgreSQL indexes is an analogy, not a PostgreSQL rule, but two pieces of the pattern’s advice are worth an FDE remembering.

First: do not create speculative index tables for queries the application never runs, or runs only occasionally. Every index table adds maintenance cost, so it needs to be reviewed and removed once nobody uses it. Applied to an agent, add one only when the history from step 1 shows the query pattern genuinely recurs.

Second: low-selectivity keys, such as the boolean column is_paid, are poor candidates. The index table still incurs the full storage and throughput cost but barely narrows the query. A flag with two values splits the table in half; it does not help you find a few rows quickly.

Common mistakes

The most common mistake is running EXPLAIN ANALYZE in production and feeling safe because of ROLLBACK, while the load still lands on the customer’s database. The second is comparing cost between two plans and drawing conclusions in milliseconds.

The third is building a materialized view without anyone agreeing on acceptable staleness, so refreshes run on gut feeling and nothing alerts when they break.

In the other direction, do not rush to index every column the agent has ever filtered on. Each index is a write cost the customer’s transactional system pays, forever.

How to put this skill on your CV

If an FDE job description mentions working directly with customer databases or production performance, this is a skill to lead with in interviews.

On your CV, do not write “proficient in SQL”. Write one structured line: the measurable target, the load-heavy statement, the change you made (materialized view, UNIQUE index, concurrent refresh) and how you monitored staleness.

A small repo with scripts, before-and-after execution plans and a README explaining why you did not index the boolean column will be more convincing than any certificate.

Agents will keep getting better at writing SQL. But understanding whom the customer’s database is serving at what time, and why the agent should not touch it, remains the job of the FDE sitting next to the customer’s operations team.