0

I have a cron on my server that runs every minute, it looks for all registered people who did not have their last connection updated in the last 10 minutes and changes the value in a column in the same table.

However, to update this record informing that there was a connection, every 5 minutes the app triggers a request that makes an update.

My index would only consist of his status and the date of his last connection, without a primary key.

CREATE TABLE userwork (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(250),
    owner_id INTEGER REFERENCES owner(owner_id),
    lastconnection timestamptz,
    status CHAR(1)
);
CREATE INDEX idx_user_lastconnection ON userwork (status, lastconnection);

My question is: Can an index for a line that will be constantly updated cause high stress for the server or is it best to create it to avoid an UPDATE that will not search in indexes, just using FILTERS?

1 Answer 1

0

Any table update which touch a field included in the index is causing this index to be updated as well.

So no, it is not really a "high-stress". Just a regular amount of work. If you update a table once a minute - all indexes on this table are touched every minute also.

If you want to reduce workload on the server, you can remove the indexes on such table and recreate them before running a heavy query which would use those indexes, and kill them after that. So the workload would be heavy, but localized in time.

Or you can increase gin_pending_list_limit which would tell the server how many updates to the index are stored in a "pending list" and not in the index itself - it would slow down queries which use the index, but improve speed of updating this indexes. But usually, this technique start to become useful on tables with multiple updates per second, if you have once a minute - do not bother.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.