How do I make Retool use one query to bulk upsert instead of thousands?

  • Goal: When I run a query that uses the GUI mode to Bulk Upsert a few hundred to 1000s of items, I expect ONE single query going to Postgres.

  • How it works today: When I execute a Bulk Upsert of an array of JS objects using GUI mode, I get at least one query hitting Postgres per object. The Retool query times out after 120s. I am refreshing a query in Postgres during the operation:

SELECT now() - query_start, query, *
FROM pg_stat_activity 
where datname = 'retool' and state = 'active'
ORDER BY query_start desc;

And I can see the query from retool toggling between INSERT and UPDATE, and each one lasts a fraction of a second. I would expect one long query (Using INSERT ON CONFLICT or MERGE) instead.

  • Steps:

  • Get a big array of JS objects (I'm using a CSV file upload and parsedValue[0]).

  • Run a bulk upsert query with primary key

  • Run the query

  • Witness a Timeout

  • During the query run pg_stat_activity and see multiple separate queries instead of one

  • Details:
    Array of data looks like:
[
  {
    "email": "something@example.com"
},
 {
    "email": "another@example.com"
 }
]

Getting Timed Out After 120,000ms

2 Likes

Hi @Jake_Moffatt,

Welcome to Retool. I believe the issue is with the upsert, vs insert. Upsert has a WHERE clause that is unique to each object, so it has to iterate through them. Insert doesn't have this limitation (no need for a WHERE clause).

Postgres can take a insert like this:

INSERT INTO director (id, name)
VALUES
    (2, 'robert'),
    (5, 'sheila'),
    (6, 'flora')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name; 

Which allows for upserts on a unique key (in the example above, ID) without sending multiple queries to the database.

It's pretty clear the Retool algorithm is:

for object in objects
  success = doInsert(object.id, object.values)
  if ! success
    doUpdate(object.id, object.values)
  end 
end

Using up to N*2 separate queries, when this really should just be a single efficient call with only 1 query.