Jira (PDB-4504) add-resource-events-pk migration does not use batched inserts

[Charlie Sharpsteen (JIRA)]

Title: Message Title

Charlie Sharpsteen created an issue   PuppetDB / PDB-4504 add-resource-events-pk migration does not use batched inserts Issue Type: Bug Affects Versions: PDB 6.5.0, PDB 6.3.4 Assignee: Unassigned Created: 2019/09/16 11:46 AM Priority: Normal Reporter: Charlie Sharpsteen PuppetDB 6.3 added a primary key to the `resource_events` table in order to facilitate functionality such as repacking and partitioning. In order to create a unique key, the hashing algorithm for resource events was changed. This resulted in a migration that re-writes the entire table in order to update the hash and remove duplicates. The re-write portion does not use batched inserts when sending transformed data back to the database, which results in a seperate `INSERT` statement being executed for each row in the `resource_events` table. Reproduction Case Install PuppetDB 5.2 Populate Postgres with some test data and [enable the pg_stat_statements extension|https://www.postgresql.org/docs/9.6/pgstatstatements.html] on the PuppetDB database Upgrade to PuppetDB 6.3 and wait for migration 69 to finish Compare the number of calls to INSERT INTO resource_evetns_transform to the numer of rows modified by that query and the number of rows in the resource_events table: SELECT sum(calls) AS calls, sum(rows) AS rows FROM pg_stat_statements WHERE query ILIKE 'insert into resource_events_transform%'; SELECT count(*) FROM resource_events; Outcome pg_stat_statements shows that the INSERT is executed once per row in the resource_events table: pe-puppetdb=# SELECT sum(calls) AS calls, sum(rows) AS rows FROM pg_stat_statements WHERE query ILIKE 'insert into resource_events_transform%'; -[ RECORD 1 ]-- calls | 5698404 rows | 5698404   pe-puppetdb=# SELECT count(*) FROM resource_events; -[ RECORD 1 ]-- count | 5779611 Expected Outcome The insertion of transformed rows is batched such that the number of calls to INSERT is a few orders of magnitude smaller than the number of rows in the resource_events table. Add Comment   This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.324827.1568659575000.99985.1568659620443%40Atlassian.JIRA.