PostgreSQL use upsert with ON CONFLICT, or with separate INSERT and UPDATE statements?

Question

We have a PostgreSQL 14 database where we store products fetched from online platforms. We have scrapers that are run on schedule which fetch products, and this data is then either inserted if the product doesn't exist yet, or updated if the fetched version differs from the one we have stored. We are dealing with a few million products a day at the moment.

The products table within our Postgres database has the following ID field:

product_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY

Currently, our insert query (using the Python library psycopg2 execute_values method) is as follows:

INSERT INTO products (column1, column2, internal_id) VALUES %S ON CONFLICT (internal_id) DO UPDATE SET column1 = EXCLUDED.column1, column2 = EXCLUDED.column2

The basic process is therefore:

1. Fetch millions of products
2. Upsert into the database
3. On conflict (quite likely, say 90%), update existing ones

The 'issue' with this is the gap which occurs in sequences, especially when dealing with millions of products daily (distributed among tasks, but consider thousands per task). To combat this, there's another approach we can take:

1. Fetch all products that match the criteria of the task (e.g. specific niche or page)
2. Get all products from the database matching those criteria (not millions but hundreds, maybe thousands)
3. Compare the internal_id field with each other
4. If it exists and has to be updated, use an UPDATE statement
5. If it doesn't exist, use the INSERT statement

There are a few more instances, with less data, but the same 'issue' of upserting with large amount of conflicts, e.g. uploading a CSV file with 5M records of which several million are already in the database in an effort to insert the ones that are in there yet.

My question is, what is the best approach? Is it bad practice to have so many gaps in the ID sequence, or does that not matter given the possible size of the BIGINT ID field? We switched from MongoDB to Postgres recently, and I haven't been able to find an explicit answer for this. Maybe it's not an issue at all and is best practice, maybe it's a very bad practice (it doesn't seem the best approach to me right now). I'd like to ask for some feedback before we fully commit to an approach.

Answer 1

Stick with INSERT ... ON CONFLICT. It will most likely be the most efficient solution and is free from race conditions. If you lose “a few million” generated primary key values per day, why is that a problem?

There are 9223372036854775807 positive bigint values. If you consume 10 million per day, you will run out of bigint values in 922337203685 days. That is over 2.5 billion years. If I were you, I would worry about something else.