Avoid using view_queue to retrieve next command in PostgreSQL#227
Avoid using view_queue to retrieve next command in PostgreSQL#227roperzh wants to merge 1 commit intomicromdm:mainfrom
Conversation
This change addresses high CPU usage in PostgreSQL when retrieving commands from large queues. This follows commit c771e92 which implemented a similar optimization for MySQL by: - Removing `view_queue` in favor of a direct table query + optimized indexes - Using parameterized query instead of string concatenation which enables prepared statement plan reuse.
0cf42f2 to
4fa9c5a
Compare
| @@ -0,0 +1,7 @@ | |||
| CREATE INDEX idx_enrollment_queue_active_retrieval | |||
| ON enrollment_queue (id, priority DESC, created_at) | |||
| WHERE active = TRUE; | |||
There was a problem hiding this comment.
note: I saw that the MySQL PR also updated the schema.sql file, let me know if that's necessary and I'll do it!
There was a problem hiding this comment.
Yes we the primary schema.sql should represent the "current" complete schema. A new user wouldn't run through the migrations.
|
|
||
| CREATE INDEX idx_command_results_lookup | ||
| ON command_results (id, command_uuid) | ||
| INCLUDE (status, updated_at); |
There was a problem hiding this comment.
we already have a primary key on (id, command_uuid), but the INCLUDE here allows us to store this info in the index.
having that said, I have no problems removing if you think it's redundant
There was a problem hiding this comment.
Interesting. What's rationale/reason here?
jessepeterson
left a comment
There was a problem hiding this comment.
Hey there, sorry for the delay, and thanks!! Yeah we'll want to include the changes in the main schema. Also just an FYI for #213 which is probably destined for the psql backend, too.
| @@ -0,0 +1,7 @@ | |||
| CREATE INDEX idx_enrollment_queue_active_retrieval | |||
| ON enrollment_queue (id, priority DESC, created_at) | |||
| WHERE active = TRUE; | |||
There was a problem hiding this comment.
Yes we the primary schema.sql should represent the "current" complete schema. A new user wouldn't run through the migrations.
|
|
||
| CREATE INDEX idx_command_results_lookup | ||
| ON command_results (id, command_uuid) | ||
| INCLUDE (status, updated_at); |
There was a problem hiding this comment.
Interesting. What's rationale/reason here?
This change addresses high CPU usage in PostgreSQL when retrieving commands from large queues.
This follows commit c771e92 which implemented a similar optimization for MySQL by:
view_queuein favor of a direct table query + optimized indexes