Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

x/pkgsite: explore using pgx for fast bulk inserts #42707

Closed
jba opened this issue Nov 18, 2020 · 3 comments
Closed

x/pkgsite: explore using pgx for fast bulk inserts #42707

jba opened this issue Nov 18, 2020 · 3 comments
Labels
FeatureRequest FrozenDueToAge NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one. pkgsite
Milestone
pkgsite/unplanned

Comments

@jba
Copy link
Contributor

jba commented Nov 18, 2020

Doing bulk inserts with pgx may be significantly faster than our current method of constructing large INSERT statements. Benchmark and switch to pgx if it's significantly better.
@jba jba added this to the pkgsite/unplanned milestone Nov 18, 2020
@jba jba self-assigned this Nov 18, 2020
@julieqiu julieqiu added the NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one. label Nov 18, 2020
gopherbot pushed a commit to golang/pkgsite that referenced this issue Nov 24, 2020
@jba
internal/database: benchmark pgx copy against bulk insert
8dd5fd7 
Add a benchmark which demonstrates that pgx's CopyFrom method
is significantly faster than doing a bulk insert with pq. In this
simple example, it is 55 times faster (!).

For golang/go#42707

Change-Id: I8bb37ba95b6207ead320b9720fbcc5a2fb8c8514
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/272506
Trust: Jonathan Amsterdam <jba@google.com>
Run-TryBot: Jonathan Amsterdam <jba@google.com>
Reviewed-by: Julie Qiu <julie@golang.org>
@gopherbot
Copy link

Change https://golang.org/cl/272506 mentions this issue: internal/database: benchmark pgx copy against bulk insert

@jba
Copy link
Contributor Author

jba commented Nov 25, 2020

pgx is faster, and it makes sense to switch to it for other reasons too (like, it's actively maintained). We now can use either the lib/pq or pgx drivers by setting an environment variable. We'll roll out pgx to the workers first, then the frontends. Once we're sure there are no issues with it, we can start using the fast copy code.

@jba
Copy link
Contributor Author

jba commented Dec 2, 2020

Unfortunately we can't use pgx copy, but we'll still switch to the pgx driver. See https://golang.org/cl/274215.

@jba jba closed this as completed Aug 6, 2021
@rsc rsc unassigned jba Jun 23, 2022
@golang golang locked and limited conversation to collaborators Jun 23, 2023
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
FeatureRequest FrozenDueToAge NeedsInvestigation Someone must examine and confirm this is a valid issue and not a duplicate of an existing one. pkgsite
Projects
None yet
Milestone
pkgsite/unplanned
Development

No branches or pull requests
3 participants
@julieqiu @gopherbot @jba