Skip to content

fix(coderd/database): optimize provisioner daemon with status query using index #19703

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.

Sign up for GitHub

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
Merged
merged 2 commits into from
Sep 8, 2025
Merged

fix(coderd/database): optimize provisioner daemon with status query using index #19703

merged 2 commits into from
Sep 8, 2025
+8 −0

Conversation

mafredri
Copy link
Member

@mafredri mafredri commented Sep 4, 2025 •
edited
Loading

Fixes coder/internal#724

This change adds an index to optimize the GetProvisionerDaemonsWithStatusByOrganization query.

On dogfood, this index does not add a lot of weight to the database and reduces execution time.

coder=> \di+ provisioner_jobs_worker_id_organization_id_completed_at_idx
List of relations
-[ RECORD 1 ]-+------------------------------------------------------------
Schema        | public
Name          | provisioner_jobs_worker_id_organization_id_completed_at_idx
Type          | index
Owner         | coder
Table         | provisioner_jobs
Persistence   | permanent
Access method | btree
Size          | 4720 kB
Description   |

@mafredri mafredri changed the title fix(coderd/database): optimize GetProvisionerDaemonsWithStatusByOrganization query via index fix(coderd/database): optimize provisioner daemon with status query via index Sep 4, 2025
@mafredri mafredri changed the title fix(coderd/database): optimize provisioner daemon with status query via index fix(coderd/database): optimize provisioner daemon with status query using index Sep 4, 2025
@mafredri mafredri marked this pull request as ready for review September 4, 2025 13:25
johnstcn
johnstcn approved these changes Sep 4, 2025
View reviewed changes
Copy link
Member

@johnstcn johnstcn left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Missing a make gen but otherwise nice add!

...tabase/migrations/000363_optimize_getprovisionerdaemonswithstatusbyorganization_query.up.sql
@@ -0,0 +1,3 @@
CREATE INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx ON provisioner_jobs (worker_id, organization_id, completed_at DESC NULLS FIRST);
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

What impact did NULLS FIRST show versus without in your testing?

Copy link
Member Author

@mafredri mafredri Sep 4, 2025 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It's negligible because there are 0 uncompleted jobs in the clone I have. It does get rid of one sort, and should provide a benefit for larger datasets and lots of in flight jobs.

@mafredri mafredri merged commit 9db265d into main Sep 8, 2025
50 of 53 checks passed
@mafredri mafredri deleted the mafredri/fix-optimize-provisioner-daemon-with-status-query branch September 8, 2025 08:10
@github-actions github-actions bot locked and limited conversation to collaborators Sep 8, 2025
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Reviewers

@johnstcn johnstcn johnstcn approved these changes

@deansheather deansheather deansheather approved these changes

@spikecurtis spikecurtis Awaiting requested review from spikecurtis

Assignees

@mafredri mafredri

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

bug: GetProvisionerDaemonsWithStatusByOrganization is slow and creates lots of DB load
3 participants
@mafredri @johnstcn @deansheather