I previously blogged about ensuring that the “ON CONFLICT” directive is used in order to avoid vacuum from having to do additional work. You can read the original blog here: Reduce Vacuum by Using “ON CONFLICT” Directive
Now that Postgres has incorporated the “MERGE” functionality into Postgres 15 and above, I wanted to ensure that there was no “strange” behavior as it relates to vacuum when using merge. As you can see here, the “MERGE” functionality does perform exactly as expected. For example, when you attempt to have a merge where the directive is to try an insert first followed by an update, exactly one row is marked dead when the insert fails and the update succeeds.
/* Create the table: */
CREATE TABLE public.pk_violation_test (
id int PRIMARY KEY,
value numeric,
product_id int,
effective_date timestamp(3)
);
/* Insert some mocked up data */
INSERT INTO public.pk_violation_test VALUES (
generate_series(0,10000),
random()*1000,
random()*100,
current_timestamp(3));
/* Verify that there are no dead tuples: */
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 100001 | 0
Then, create a simple merge and check the results:
WITH insert_query AS (
SELECT
0 AS id,
44.33893489873 AS value,
46 AS product_id,
now() AS effective_date) MERGE INTO pk_violation_test pkt
USING insert_query i ON pkt.id = i.id
WHEN MATCHED THEN
UPDATE SET
value = i.value, product_id = i.product_id, effective_date = i.effective_date
WHEN NOT MATCHED THEN
INSERT (id, value, product_id, effective_date)
VALUES (i.id, i.value, i.product_id, i.effective_date);
MERGE 1
And then check the dead tuple count:
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup
FROM
pg_stat_all_tables
WHERE
relname = 'pk_violation_test';
schemaname | relname | n_live_tup | n_dead_tup
------------+-------------------+------------+------------
public | pk_violation_test | 10001 | 1
(1 row)
As expected only one row is marked dead. Merge is such great functionality and I am glad to see it in Postgres. As you get time, all of your “ON CONFLICT” statements should be converted to use this functionality. Enjoy!
🛩️ Shane Borden's Technology Blog 
Pingback: Vacuum, MERGE, and ON CONFLICT Directives – Curated SQL