1

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.

2
  • You’re right that millions of ON CONFLICT updates per day is a problem, if for no other reason than it slows things down. You already know the product_id, so why not just do a COUNT() WHERE product_id = X? If O then insert, else update.
    – RonJohn
    Commented Mar 6, 2023 at 2:10
  • Thanks for the answer, as @Laurenz Albe made clear, we won't be thinking about gaps in sequences anymore. But based on your comment, we'll test out the performance with both an upsert and an update statement (as it's feasible based on the internal_id and updated_at fields).
    – Nuxurious
    Commented Mar 6, 2023 at 8:59

1 Answer 1

0

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.

7
  • Thank you for the answer, Laurenz. It has been made clear that trying to minimize gaps in sequences is a futile effort. The performance (and more specifically speed) of the program is quite important to the cost of the program, so I think for the products fetching it's best to be moving forward with some sort of updater as @RonJohn pointed out (depending on results of some small tests). For smaller operations, we will keep it as is with an upsert statement.
    – Nuxurious
    Commented Mar 6, 2023 at 8:56
  • First query, then update will very likely be slower. Commented Mar 6, 2023 at 8:58
  • It's important to ensure that any changes we make to the program do not negatively impact its performance. I will test out both the query-then-update approach and the upsert statement approach to determine which one has better performance for our specific use case. Based on some tests from other users on the DBA exchange, it does seem like upsert might be the winner.
    – Nuxurious
    Commented Mar 6, 2023 at 9:05
  • @Nuxurious curious, which approach did you guys go with?
    – Sameer
    Commented May 6, 2023 at 20:16
  • Hi @Sameer, we initially planned to use bigint's with upserting, but eventually switched to UUID's since we were making the system more distributed.
    – Nuxurious
    Commented May 7, 2023 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.