VACUUM overhead caused by constraint violations during INSERTs

By John Doe April 1, 2026

Summary: In this article, we will examine the VACUUM overhead caused by constraint violations when inserting data into PostgreSQL tables, as well as the corresponding solutions.

Table of Contents

Preparing Test Data

First, we create a products table and an orders table in a PostgreSQL database:

CREATE TABLE products (
    product_id serial PRIMARY KEY,  -- Product ID
    product_name varchar(100),      -- Product name
    price numeric(10,2),            -- Unit price
    stock int                       -- Stock quantity
);

CREATE TABLE orders (
    order_id serial PRIMARY KEY,    -- Order ID
    product_id int,                 -- Associated product ID
    quantity int,                   -- Purchase quantity
    amount numeric(12,2),           -- Total order amount
    order_time timestamp,           -- Order time
    CONSTRAINT fk_order_product FOREIGN KEY (product_id)
      REFERENCES products(product_id)
);

Generate 100 product records with names ranging from prod_001 to prod_100, followed by 10,000 order records:

INSERT INTO products (product_name, price, stock)
SELECT
    'prod_' || lpad(seq::text, 3, '0'),
    round( (random() * 980 + 19.99)::numeric, 2 ),
    floor(random() * 991 + 10)::int
FROM generate_series(1, 100) AS seq;

INSERT INTO orders (product_id, quantity, amount, order_time)
SELECT
    t.product_id,
    t.quantity,
    round((p.price * t.quantity)::numeric, 2),
    now() - (random() * INTERVAL '365 days')
FROM (
    SELECT
        floor(random() * 100 + 1)::int AS product_id,
        floor(random() * 10 + 1)::int AS quantity
    FROM generate_series(1, 10000)
) t
JOIN products p ON p.product_id = t.product_id;

VACUUM (analyze) products;
VACUUM (analyze) orders;

Unique Constraint Violation on Insert

Let’s execute a simple INSERT statement:

INSERT INTO orders (order_id, product_id,
                    quantity, amount, order_time)
  VALUES (1, 20, 5, 5 * 100, now());
ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (order_id)=(1) already exists.

SELECT schemaname, relname, n_live_tup, n_dead_tup
  FROM pg_stat_all_tables WHERE relname = 'orders';
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | orders  |      10000 |          1
(1 row)

As shown, the error occurs because the insert statement violates the primary key constraint. The tuple that violated the primary key is now a dead tuple.

In mainstream relational databases, developers often use exception code block in stored procedures, to handle errors like primary key constraint violations:

CREATE OR REPLACE FUNCTION sell_product_func
    (ord_id int, prod_id int, qty int)
  RETURNS void AS $$
DECLARE
  p_price numeric(10,2);
BEGIN
  SELECT price INTO p_price FROM products
    WHERE product_id = prod_id;

  BEGIN
    INSERT INTO orders (order_id, product_id,
                        quantity, amount, order_time)
      VALUES (ord_id, prod_id,
              qty, qty * p_price, now());
  EXCEPTION
    WHEN unique_violation THEN
      UPDATE orders SET
          product_id = prod_id,
          quantity = qty,
          amount = qty * p_price,
          order_time = now()
      WHERE order_id = ord_id;
  END;
END; $$ LANGUAGE plpgsql;

In the stored procedure above, if an insert operation encounters a primary key constraint violation, it falls back to an UPDATE to modify the order.

-- Fall back to update after primary key conflict on insert
SELECT * FROM sell_product_func(1, 20, 5);

SELECT schemaname, relname, n_live_tup, n_dead_tup
  FROM pg_stat_all_tables WHERE relname = 'orders';
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | orders  |      10000 |          3
(1 row)

As shown, two new dead tuples are created: one from the failed INSERT and another from the UPDATE. In such cases, the specific ON CONFLICT UPDATE clause can be used to reduce the occurrence of dead tuples in PostgreSQL.

Foreign Key Constraint Violation on Insert

We perform a simple insert that violates the foreign key constraint and check for dead tuples:

INSERT INTO orders (order_id, product_id,
                    quantity, amount, order_time)
  VALUES (10001, 102, 5, 5 * 100, now());
ERROR:  insert or update on table "orders" violates foreign key constraint "fk_order_product"
DETAIL:  Key (product_id)=(102) is not present in table "products".

SELECT schemaname, relname, n_live_tup, n_dead_tup
  FROM pg_stat_all_tables WHERE relname = 'orders';
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | orders  |      10000 |          4
(1 row)

As expected, the foreign key violation during insertion results in an additional dead row.

Note: When INSERTs frequently violate foreign key constraints, using the ON CONFLICT clause can not reduce the occurrence of dead tuples.

Conclusion

From the tests above, frequent and large-scale constraint violations in applications can also result significant VACUUM overhead in PostgreSQL.

In addition, Redrock Postgres introduces an undo log mechanism that immediately rolls back table modifications when an insert violates a constraint, thus avoiding extra storage space consumption.

Search

Categories

Tags