We have Kong API which calls an insert statement on oauth2_tokens table in our postgres database, there is also an trigger function which actioned after insert will call batch_delete_expired_rows to remove expired rows from this table.
The problem is that the API response is slower sometimes, when we checked the query performance, the query sometimes finishes in 3 sec and sometimes it takes more than 15s and API requests are timing out at end user due to this.
Our question is,
- Can we disable the trigger and move the clean up job seperately and run it during night hours to cleanup expired rows
- We see lot of unused indexes on this table created on Text column on this table, can you suggest to drop the unused indexes from this table?
schemaname | table_name | index_name | idx_scan | index_size
------------±--------------±-----------------------------------------±---------±-----------
public | oauth2_tokens | oauth2_tokens_ws_id_access_token_unique | 472333 | 54 MB
public | oauth2_tokens | oauth2_tokens_id_ws_id_unique | 0 | 35 MB
public | oauth2_tokens | oauth2_tokens_pkey | 0 | 22 MB
public | oauth2_tokens | oauth2_tokens_ws_id_refresh_token_unique | 0 | 14 MB
public | oauth2_tokens | oauth2_tokens_ttl_idx | 655555 | 11 MB
public | oauth2_tokens | oauth2_tokens_credential_id_idx | 0 | 5800 kB
public | oauth2_tokens | oauth2_tokens_service_id_idx | 12 | 5544 kB
public | oauth2_tokens | oauth2_tokens_authenticated_userid_idx | 0 | 5544 kB
if you provide us any other suggestions to improve this insert statements that would be great.