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:
- Fetch millions of products
- Upsert into the database
- 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:
- Fetch all products that match the criteria of the task (e.g. specific niche or page)
- Get all products from the database matching those criteria (not millions but hundreds, maybe thousands)
- Compare the internal_id field with each other
- If it exists and has to be updated, use an UPDATE statement
- 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.