I have performed comparison of different ways of implementing UPSERT in Postgres.
May be it will be interesting not only for me, so I share my results:
So first of all initialization step:
create table jsonb_schemas(id serial, schema bytea primary key);
create unique index on jsonb_schemas(id);
insert into jsonb_schemas (schema) values ('some') on conflict(schema) do nothing returning id;
Then I test performance of getting ID of exitsed schema:
1. Use plpgsql script to avoid unneeded database modifications:
create function upsert(obj_schema bytea) returns integer as $$
declare
obj_id integer;
begin
select id from jsonb_schemas where schema=obj_schema into obj_id;
if obj_id is null then
insert into jsonb_schemas (schema) values (obj_schema) on conflict(schema) do nothing returning id into obj_id;
if obj_id is null then
select id from jsonb_schemas where schema=obj_schema into obj_id;
end if;
end if;
return obj_id;
end;
$$ language plpgsql;
------------------------
upsert-plpgsql.sql:
select upsert('some');
------------------------
pgbench -n -T 100 -M prepared -f upsert-plpgsql.sql postgres
tps = 45092.241350
2. Use ON CONFLICT DO UPDATE:
upsert-update.sql:
insert into jsonb_schemas (schema) values ('some') on conflict(schema) do update set schema='some' returning id;
------------------------
pgbench -n -T 100 -M prepared -f upsert-update.sql postgres
tps = 9222.344890
3. Use ON CONFLICT DO NOTHING + COALESCE:
upsert-coalecsce.sql:
with ins as (insert into jsonb_schemas (schema) values ('some') on conflict(schema) do nothing returning id) select coalesce((select id from ins),(select id from jsonb_schemas where schema='some'));
------------------------
pgbench -n -T 100 -M prepared -f upsert-coalesce.sql postgres
tps = 28929.353732
4. Use ON CONFLICT DO SELECT
upsert-select.sql:
insert into jsonb_schemas (schema) values ('some') on conflict(schema) do select returning id;
------------------------
pgbench -n -T 100 -M prepared -f upsert-select.sql postgres
ps = 35788.362302
So, as you can see PLpgSQL version, which doesn't modify database if key is found is signficantly faster than others.
And version which always do update is almost five times slower!
Proposed version of upsert with ON CONFLICT DO SELECT is slower than PLpgSQL version (because it has to insert speculative tuple),
but faster than "user-unfriendly" version with COALESCE:
Upsert implementation
| TPS
|
PLpgSQL
| 45092 |
| ON CONFLICT DO UPDATE | 9222 |
| ON CONFLICT DO NOTHING | 28929 |
| ON CONFLICT DO SELECT | 35788 |
Slightly modified version of my ON CONFLICT DO SELECT patch is attached to this mail.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
