Why Was My Postgres Trigger 100× Slower in Production? It Re-Planned Its Upsert on Every Call

TL;DR — I shipped a trigger that wrote one settlement row per order, and within fifteen minutes of lunch peak every order status update took about 400 ms instead of 3 ms. The database restarted three times. I spent the first three hours on memory, swap and I/O, which were all real and none of which was the cause. The cause was the query planner: the trigger’s INSERT … ON CONFLICT spent 65.5 ms planning and 5.3 ms executing, and it did that on every call because PL/pgSQL never switched it to a cached generic plan. Postgres estimates a parameter array (= ANY($1)) at 10 elements in a generic plan, compared that against custom plans built for a 1-element array, decided the generic plan was dearer, and kept re-planning forever. ALTER FUNCTION … SET plan_cache_mode = force_generic_plan took the function from 10.0 ms to 0.79 ms per call locally, and production status updates back to 2–3 ms.

Key takeaways

• A trigger turns its cost into a tax on every write to the table, including writes whose authors have never heard of it. Benchmark the writes that fire it, not only the statement it runs.
• In PL/pgSQL, every statement is a prepared statement. Whether it is re-planned on every call is decided by a cost comparison you never see, and for complex statements it can land on “always re-plan”.
• EXPLAIN ANALYZE prints Planning Time separately. When planning is ten times execution, you have found your problem, and no index will fix it.
• pg_stat_statements hides planning time by default (track_planning = off) and hides statements inside functions by default (track = top). The monitoring that should have caught this could not see it.
• Probe the exact statement. I ruled the right answer out once because a simpler probe query behaved differently from the real one.

What shipped, and why it was a trigger

The feature was a settlement ledger: one row per order recording who owes whom for it — the gateway fee, the tax split, the vendor’s share — frozen at the time of the order, so a finance report does not quietly change when a configuration changes later. More than 25 code paths create or modify an order on this platform. A row that has to exist for every order, whichever path created it, is the textbook case for a trigger, and I would still make that call.

The shape, simplified:

-- Illustrative. A statement-per-order upsert, called from triggers on
-- orders (insert and status change), transactions and refunds.
CREATE FUNCTION record_settlements(order_ids uuid[]) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO order_settlements (order_id, gross, gateway_fee, tax, vendor_share /* …50 more */)
  SELECT * FROM compute_settlements(order_ids)   -- 7 CTEs, lateral joins to rules and refunds
  ON CONFLICT (order_id) DO UPDATE
    SET gross = EXCLUDED.gross /* … */
    WHERE (order_settlements.gross /* … */) IS DISTINCT FROM (EXCLUDED.gross /* … */);
END $$;

compute_settlements is a set-returning SQL function with seven CTEs, a lateral join to the rate rules and another to refunds; the target table has 59 columns. Before shipping I measured it: 0.75 ms per order across 200 inserts, against 0.04 ms without the trigger. Acceptable. The migrations went to production at 12:20 IST on a weekday — forty minutes before lunch, which on a food platform is the worst time of day to learn anything.

The timeline

  12:20  triggers deployed (4 tables)
  12:36  "the db is getting under load"          ← first signal
  13:23  RAM 81%, CPU 5%, IOwait climbing          ← looks like memory
  13:32  RESTART 1
  13:51  RESTART 2   pool cut 31 → 20 connections, dashboards closed
  14:15  RESTART 3   ← caused by my own benchmark against production
  14:51  EXPLAIN ANALYZE: planning 65.5 ms, execution 5.3 ms    ← the answer
  15:01  I rule the answer out (wrong probe)
  15:04  triggers disabled
  15:17  plan-cache cause reproduced locally
  15:41  fixed function deployed, triggers re-enabled
  15:43  catch-up sweep: 1,144 orders checked, 364 rows added or corrected, 0 errors

  ▼  a one-line fix, reached after three restarts and one false negative

Users saw between 10 and 50 seconds of failed requests around each restart. At least one write to a payment in progress failed during a restart.

Why memory looked guilty

The database runs on a small instance: 2 GB of RAM, shared_buffers at 512 MB, 90 connections. Every signal I looked at first was a memory signal, and every one of them was real:

The stop-gaps were reasonable for a memory problem. I cut the API’s connection pool from 31 to 20, which dropped total connections to 13 and committed memory to 1.65 GB, and asked people to keep the heaviest dashboards closed. An upgrade to the next instance size (4 GB, about $45 a month more) was on the table and declined, correctly: it needed a restart, at peak, of a database that was already restarting on its own.

What none of those numbers could tell me was why memory pressure had appeared at 12:36 on a day when traffic was ordinary. Resource graphs describe the state of the box. They say nothing about which statement put it there.

Where the time actually went

pg_stat_statements, read after the third restart, gave the first real lead. The statements that fired the trigger were in a different class from the ones that did not:

Those updates were 49% of all database time — 110 of 224 seconds in the 36 minutes after the last restart. A single-row UPDATE by primary key does not cost 400 ms. Something it caused did.

The next step was to run the trigger’s function directly under EXPLAIN (ANALYZE) on production, for one real order:

Planning Time: 65.542 ms
Execution Time: 5.264 ms

That is the whole incident in two lines. The work was cheap. Deciding how to do the work cost twelve times more than doing it, and it was being decided again on every call.

Why Postgres never cached the plan

Every SQL statement inside a PL/pgSQL function is a prepared statement. The PL/pgSQL plan-caching docs say the interpreter prepares each statement on first use and then decides whether to cache a generic plan — one that does not depend on the parameter values — or keep building a custom plan for each call’s actual values. The rule for that decision is in the PREPARE notes:

“the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.”

Most write-ups about this rule complain about the opposite failure: Postgres switches to a generic plan that turns out to be slow. This was the other direction, and it is quieter.

The trigger always passes a one-element array — the order that just changed. In a custom plan, the planner sees order_id = ANY('{…one id…}') and estimates one row through every CTE. In the generic plan, $1 is an unknown array, and the planner falls back to its default guess of 10 elements. Ten orders through seven CTEs and two lateral joins costs more, on paper, than one. So the generic plan was judged dearer than the average custom plan, and every call from the sixth onward kept building a fresh custom plan. Forever. The cost comparison looks only at estimated execution cost with a small planning allowance, and it had no idea this statement took 65 ms to plan.

The local reproduction made it unambiguous, calling the function in a loop on a warm connection:

auto and force_custom_plan are the same number. That is what “it never switched” looks like.

The fix is one line

plan_cache_mode can be set per function, so the override applies to this statement and nothing else:

-- Illustrative. Scope the override to the one function that needs it.
ALTER FUNCTION record_settlements(uuid[]) SET plan_cache_mode = force_generic_plan;

A generic plan is safe here because the parameter does not change what the best plan is: it is always a handful of orders looked up by primary key. After the fix went out, order status updates in production measured about 21 ms over the first six calls off-peak, and 2–3 ms an hour later across nineteen. Transaction updates came down to 0.4–0.7 ms.

There is a small irony in the original migration. It had a comment explaining that compute_settlements deliberately carried no SET clause, so Postgres could inline it “into the caller’s cached plan”. The caller’s plan was never cached. The fix is a SET clause, one level up.

When forcing a generic plan is right, and when it is not

Why the monitoring could not see it

pg_stat_statements was installed and it is where the investigation turned. It still could not show the cause, for two reasons that are defaults in the extension’s configuration:

• pg_stat_statements.track_planning is off by default, so planning time is not recorded at all. The plan-time columns read zero.
• pg_stat_statements.track is top by default, so statements executed inside a function or trigger are folded into the statement that called them. The view showed a slow UPDATE, not the upsert the UPDATE caused.

Both defaults are reasonable for overhead reasons. Together they meant the only tool pointed at query performance reported that a primary-key update took 400 ms, and could not say why. EXPLAIN (ANALYZE) on the function itself was the only thing that printed the planning cost.

What actually broke in production

The trigger was the bug. The three hours were mine, and they are the more useful part of this post.

My pre-ship benchmark measured the wrong path. 0.75 ms per order was measured on inserts, in one warm session, on my laptop, running a newer Postgres major version than production. The expensive path was status updates. When I later measured those locally, under the default auto mode, they took 10.5 ms each — fourteen times the number I had signed off on. I did not benchmark the writes that fire the trigger most often.

I ran a benchmark against production during the incident. About 145 calls, several of them taking seconds each, to measure which statement was slow. The third restart, at 14:15, followed it directly. The information was worth having. Getting it that way, on a 2 GB box already in trouble, at peak, was not. A read-only replica, or the same measurement against a restored copy, would have given the same numbers without the restart.

I found the answer and then ruled it out. At 14:51 the planning-time evidence was in front of me. Ten minutes later I dismissed it, because a probe I wrote — a SELECT count(*) over the same compute function — did switch to a generic plan after six calls, exactly as the documentation describes. The probe was a different statement with a different cost shape, and the switch it made told me nothing about the INSERT … ON CONFLICT the trigger actually ran. I also proposed moving the calculation into a PL/pgSQL function as a fix, which would have changed nothing: it already was one. The triggers were disabled at 15:04 on the strength of the load, not the diagnosis, and the local repro at 15:17 is what settled it.

The restarts themselves are still not explained. Memory exhaustion is the obvious suspect and I believe it, but I never confirmed it from the Postgres logs, and I am not going to write it down as the cause. What I can say is that the restarts stopped when the trigger’s cost stopped.

Once the triggers were back, the catch-up sweep recomputed every order from that day: 1,144 checked, 364 settlement rows added or corrected, zero errors. Because the upsert only writes when a value IS DISTINCT FROM the stored one, running it again over 3,000 orders changed zero rows. That property is why re-enabling was safe without a maintenance window.

The side findings from the same afternoon

Two things turned up while the box was under a microscope, and both are worth a line.

The main transactions table was only 46% all-visible in the visibility map, so index-only scans were not index-only: an order-history count did about 13,000 disk reads to answer a question the index could have answered in 118 lookups. A manual VACUUM (ANALYZE) took that count from 7.0 s to 0.34 s, and dropping that table’s autovacuum scale factor from the default 0.2 to 0.02 keeps it there. Several order-history functions were also missing STABLE, which blocks inlining; adding it took the worst from 9,664 ms to 13 ms. The small-tables post covers why that marker matters, so I will not repeat it.

FAQ

Why is my PL/pgSQL function slow even though the query inside is fast? Check planning time. Run the statement under EXPLAIN (ANALYZE) and compare Planning Time with Execution Time. PL/pgSQL prepares every statement, and if Postgres decides the generic plan is more expensive than the custom ones, it re-plans on every call. For a statement with many CTEs and joins, planning can cost more than execution — 65 ms against 5 ms in this incident.

What does plan_cache_mode = force_generic_plan do? It tells Postgres to use a cached generic plan for prepared statements instead of building a custom plan for each set of parameter values. It removes per-call planning cost. It is safe when the best plan does not depend on the parameter values, such as lookups by primary key, and risky when values select very different row counts. Set it per function with ALTER FUNCTION … SET, not globally.

When does Postgres switch from custom plans to a generic plan? After five executions with custom plans, it builds a generic plan and compares its estimated cost with the average custom-plan cost. If the generic plan is not much more expensive, it is used from then on. If it is judged more expensive — for example because an array parameter is estimated at 10 elements while the real calls pass one — Postgres keeps re-planning every time.

Why doesn’t pg_stat_statements show planning time? Because pg_stat_statements.track_planning is off by default. Turn it on to populate the planning columns, at some overhead. Also note that track = top, the default, attributes statements run inside functions and triggers to the calling statement, so a slow trigger appears as a slow UPDATE rather than as the statement that is actually slow.

Are database triggers bad for performance? No, but their cost is paid by every write that fires them, including writes from code that does not know the trigger exists. Measure the firing statements under realistic load, with the planner mode production will use, before shipping. A trigger that adds 0.75 ms in a benchmark and 400 ms in production is almost always measuring different paths.

Should I have upgraded the instance? Not during the incident. More memory would have delayed the restarts without touching the cause, and the upgrade itself required a restart at peak. It is a fair question for later, on its own merits; it was the wrong lever for a planning-cost problem.

What I’d still improve

• Turn on track_planning. The one number that would have short-circuited three hours was not being collected. I would rather pay its overhead than repeat this.
• Benchmark triggers by firing statement, on the production major version, and include a status update, not just an insert.
• Never measure on a sick primary. A restored copy is slower to set up and faster than a fourth restart.
• Confirm the restart cause from the logs and write it down, rather than leaving “probably memory” as the record.
• Re-measure at lunch peak. The post-fix numbers are off-peak. They are good, and they are not a peak measurement, and I have not yet posted one.
• A retry sweep for settlement errors. Today a failed settlement write is recorded and caught by the next catch-up run. A scheduled sweep would close that gap without a human, the same way the timeouts post argues every quiet failure needs a loud counterpart.

The one idea to take away

When Planning Time is bigger than Execution Time, stop looking at the box and look at the planner.

Every resource graph during this incident was true and pointed away from the cause. Memory was high because backends were busy; backends were busy because they were planning; they were planning because a cost estimate said re-planning was cheaper than caching. None of that is visible from the outside of the database. Two lines of EXPLAIN ANALYZE output were, and the fix was one line after that.

---

I write about backend reliability, Postgres performance and concurrency correctness from work on food-tech and healthcare platforms. More on what I build and how I work, the overload where client timeouts made things worse, and how a missing constraint grew 2,008 duplicate bookings. If you have a trigger in production, run its function under EXPLAIN ANALYZE today and read the first line.