Opened 7 weeks ago

Closed 4 weeks ago

#35936 closed Cleanup/optimization (fixed)

Speeding up Postgres bulk_create by using unnest

Reported by: Simon Charette Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 5.1
Severity: Normal Keywords: postgres bulk create unnest
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Per ​this forum discussion there seems to be a consensus and peer validation of performance benefits of using unnest for the bulk insertion of literal values on Postgres.

Change History (6)

comment:1 by Simon Charette, 7 weeks ago

Has patch: set
Owner: set to Simon Charette
Patch needs improvement: set
Status: new → assigned

comment:2 by David Sanders, 6 weeks ago

Triage Stage: Unreviewed → Accepted

comment:3 by Simon Charette, 6 weeks ago

Patch needs improvement: unset

comment:4 by Mariusz Felisiak, 4 weeks ago

Triage Stage: Accepted → Ready for checkin

comment:5 by Sarah Boyce <42296566+sarahboyce@…>, 4 weeks ago

In 2638b755:

Refs #35936 -- Avoided field placeholder lookup for each value inserted.

By building the list of placeholders for each inserted fields once it
doesn't have to be looked up for each inserted rows twice.

The query_values_10000.benchmark.QueryValues10000.time_query_values_10000 ASV
benchmark showed a 5% speed up for 10k items on SQLite for a single field
insertion. Larger performance gains are expected when more fields are involved.

comment:6 by Sarah Boyce <42296566+sarahboyce@…>, 4 weeks ago

Resolution: → fixed
Status: assigned → closed

In a16eedc:

Fixed #35936 -- Used unnest for bulk inserts on Postgres when possible.

This should make bulk_create significantly faster on Postgres when provided
only literal values.

Thanks James Sewell for writing about this technique, Tom Forbes for
validating the performance benefits, David Sanders and Mariusz Felisiak
for the review.

Note: See TracTickets for help on using tickets.
Back to Top