Re: Performance impact of updating target columns with unchanged values ON CONFLICT

In other words, is Postgres smart enough to not actually write to disk any
columns that haven’t changed value or update indexes based on those columns?

On Thu, Nov 22, 2018 at 11:32 AM Abi Noda <a(at)abinoda(dot)com> wrote:

> Given a table, `github_repos`, with a multi-column unique index on
> `org_id` and `github_id` columns, is there any performance difference (or
> other issues to be aware of) between the two bulk upsert operations below?
> The difference is that in the first query, the `org_id` and `github_id`
> columns are included in the UPDATE, whereas in the second query they are
> not. Since the UPDATE runs ON CONFLICT, the updated values of `org_id` and
> `github_id` will be the same as the old values, but those columns are
> included in the UPDATE because the underlying library I am using is
> designed that way. I'm wondering if its safe to use as-is or whether I
> should be explicitly excluding those columns in the UPDATE.
>
> Query #1:
>
> INSERT INTO "github_repos" ("org_id","github_id","name")
> VALUES (1,1,'foo')
> ON CONFLICT (org_id, github_id)
> DO UPDATE SET
> "org_id"=EXCLUDED."org_id","github_id"=EXCLUDED."github_id","name"=EXCLUDED."name"
> RETURNING "id"
>
> Query #2:
>
> INSERT INTO "github_repos" ("org_id","github_id","name")
> VALUES (1,1,'foo')
> ON CONFLICT (org_id, github_id)
> DO UPDATE SET "name"=EXCLUDED."name"
> RETURNING "id"
>
> `github_repos` table:
>
> Column | Type | Collation | Nullable
> -------------------+-------------------+-----------+----------+
> id | bigint | | not null |
> org_id | bigint | | not null |
> github_id | bigint | | not null |
> name | character varying | | not null |
>
> Indexes:
> "github_repos_pkey" PRIMARY KEY, btree (id)
> "unique_repos" UNIQUE, btree (org_id, github_id)
>