claircore icon indicating copy to clipboard operation
claircore copied to clipboard

libvuln SQL: POC to introduce bulk inserts

Open vishnuchalla opened this issue 2 years ago • 1 comments

Description

POC to demonstrate the usage and benefits of executing bulk queries on postgres. Related JIRA: https://issues.redhat.com/browse/PROJQUAY-5680 Related GitHub Issue: https://github.com/quay/claircore/issues/995

Previously we used to have 1000 separate INSERT statements for one of the insert option on vuln table as below

#### Query 1
INSERT INTO target_table (column1, column2, ...)
VALUES (value1_1, value2_1, ...)
ON CONFLICT DO NOTHING

#### Query 2
INSERT INTO target_table (column1, column2, ...)
VALUES (value1_1, value2_1, ...)
ON CONFLICT DO NOTHING

#### Query 1000
INSERT INTO target_table (column1, column2, ...)
VALUES (value1_1, value2_1, ...)
ON CONFLICT DO NOTHING

Now we have modified the same operation to occur in a single query to do same amount of inserts in bulk as below

INSERT INTO target_table (column1, column2, ...)
VALUES
  Row 1 - (value1_1, value2_1, ...),
  Row 2 - (value1_2, value2_2, ...),
  .
  .
  .
  Row 1000 - (value1_1000, value2_1000, ...)
  ...
ON CONFLICT DO NOTHING

Testing

Tested building a new clair image

vishnuchalla avatar Jul 05 '23 14:07 vishnuchalla

Making a note so I don't forget and it doesn't look like this is totally languishing: I've got a local branch that incorporates these ideas (albeit in a less strcat-y fashion) into a re-org of the internals of the datastore/postgres package.

hdonnay avatar Sep 15 '23 21:09 hdonnay