node.js and postgres bulk upsert or another pattern?

Question

I am using Postgres, NodeJS and Knex.

I have the following situation:

1. A database table with a unique field.

2. In NodeJS I have an array of objects and I need to:

   a. Insert a new row, if the table does not contain the unique id, or

   b. Update the remaining fields, if the table does contain the unique id.

From my knowledge I have three options:

1. Do a query to check for each if exists in database and based on the response, do a update or insert. This costs resources because there's a call for each array item and also a insert or update.
2. Delete all rows that have id in array and then perform a insert. This would mean only 2 operations but the autoincrement field will keep on growing.
3. Perform an upsert since Postgres 9.5 supports it. Bulk upsert seems to work and there's only a call to database.

Looking through the options I am aware of, upsert seems the most reasonable one but does it have any drawbacks?

Answer 1

Upsert is a common way.

Another way is use separate insert/update operations and most likely it will be faster:

1. Define existing rows select id from t where id in (object-ids) (*)

2. Update existing row by (*) result

3. Filter array by (*) and bulk insert new rows.

See more details for same question here