Slow Inserts in Postgres: It's Almost Always One of Three Things

The insert endpoint was simple. One row, a few columns, primary key generated by the database. It had been fast for a year. Then a customer import made it slow enough that the API timed out.

The insert had not changed. Everything around it had. More indexes. More foreign keys. A trigger added by another team. Larger WAL bursts. A replica that could not keep up. The application was still calling it "an insert," but Postgres was doing a lot more than writing one tuple.

The framework: identify what the row has to pay for

Every insert pays for some combination of:

• Writing the heap tuple.
• Maintaining every index on the table.
• Checking constraints and foreign keys.
• Running triggers.
• Generating and flushing WAL.
• Waiting behind locks or saturated IO.

Slow insert diagnosis is figuring out which bill got large.

Start with query evidence

SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(rows::numeric / nullif(calls, 0), 2) AS rows_per_call,
  left(query, 180) AS query
FROM pg_stat_statements
WHERE query ILIKE 'insert%'
ORDER BY total_exec_time DESC
LIMIT 20;

If rows per call is close to one during bulk imports, the first fix may be batching. Many small commits pay overhead repeatedly.

Indexes are the usual suspect

Every index must be maintained on insert. That is fine when the index earns its keep. It is expensive when a table has accumulated indexes for old query paths.

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY pg_relation_size(indexrelid) DESC;

A write-heavy table with ten indexes is ten index writes per row. Before tuning random settings, ask which indexes are still used and which are only taxing inserts.

Foreign keys need indexes on the referencing side

Foreign keys check parent existence on insert. Deletes and updates on the parent also need to find referencing rows. Missing supporting indexes can make relational integrity look like insert slowness or lock pain.

SELECT
  conname,
  conrelid::regclass AS child_table,
  confrelid::regclass AS parent_table
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid = 'events'::regclass;

For high-write tables, review each foreign key and its supporting indexes as part of the insert path.

Triggers can hide real work

SELECT
  trigger_name,
  event_manipulation,
  action_timing
FROM information_schema.triggers
WHERE event_object_table = 'events';

A trigger that updates a summary table, sends work to another table, or performs validation can dominate insert latency. That may be the right design, but it should be visible in the performance model.

WAL and checkpoints explain spikes

If insert latency is spiky rather than consistently slow, look at WAL and checkpoints. Bulk inserts generate WAL. Index maintenance generates WAL. Large transactions can create bursts replicas and storage have to absorb.

SELECT
  wal_records,
  pg_size_pretty(wal_bytes) AS wal_written,
  stats_reset
FROM pg_stat_wal;

Use deltas. If WAL generation jumps exactly when inserts slow down, the bottleneck may be storage, replication, archiving, or checkpoint pressure rather than SQL planning.

Batch when the product allows it

For import paths, row-at-a-time inserts are usually the easiest performance win to avoid.

INSERT INTO events (account_id, occurred_at, kind, payload)
VALUES
  ($1, $2, $3, $4),
  ($5, $6, $7, $8),
  ($9, $10, $11, $12);

For larger loads, COPY is usually better. The tradeoff is error handling: one bad row in a batch needs a policy.

Do not reach for unsafe shortcuts first

UNLOGGED tables, relaxed durability, and disabled constraints all have valid uses. They also change failure semantics. I use them for staging, rebuildable data, or controlled pipelines - not as the first fix for a production write path.

What I check before changing anything

• Rows per insert call and commit frequency.
• Number and usage of indexes on the target table.
• Foreign keys and missing supporting indexes.
• Triggers and hidden writes.
• WAL generation rate and replica lag.
• Lock waits during insert bursts.
• Autovacuum lag on the table after heavy writes.

The pragmatic default

Slow inserts are usually not solved by one setting. Remove unused index tax, batch where the product allows it, make constraint and trigger cost visible, and monitor WAL as part of the write path. The insert statement is small. The work behind it may not be.