Postgresql locks with high concurrency API key usage

Product Support Self-hosting

AI-generated summary

Joan_Tomàs_i_Buliart reports PostgreSQL lock issues in a high-concurrency Discourse API setup using PostgreSQL+Patroni+HAProxy. With ~100 API requests per second, multiple processes are frequently blocked while updating the same api_keys record, causing Patroni to restart the master node. The blocked transactions show concurrent UPDATE statements on the same API key’s last_used_at field. Although Discourse has logic to avoid frequent updates (e.g., only updating if changed within the last minute), this protection fails under high concurrency across multiple pods. The user asks whether this is a bug, if there are configuration parameters to mitigate the issue (e.g., reducing update frequency or adjusting PostgreSQL settings), and what the recommended approach is for PostgreSQL HA with auto recovery. They also question whether Discourse is designed for such high request volumes. Joan_Tomàs_i_Buliart follows up seeking input from the community on similar experiences or guidance.

1

Hello Community,

We are using the Discourse API on a self-hosted installation. Our use case is quite intensive in terms of concurrency, with around 100 API requests per second on average. We are also using PostgreSQL+Patroni+HAProxy to provide a high-availability database setup. Occasionally, our PostgreSQL locks and Patroni restarts the master node.

We have implemented a cron job to check for blocked transactions, and every time this issue occurs, we find the same kind of operations:

 blocked_pid | blocked_user | blocking_pid | blocking_user |                                       blocked_statement                                        |                             current_statement_in_blocking_process                              
-------------+--------------+--------------+---------------+------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------
      297904 | discourse    |       293083 | discourse     | UPDATE "api_keys" SET "last_used_at" = '2024-07-16 16:38:16.822352' WHERE "api_keys"."id" = 21 | UPDATE "api_keys" SET "last_used_at" = '2024-07-16 16:34:48.163449' WHERE "api_keys"."id" = 21
      296718 | discourse    |       293083 | discourse     | UPDATE "api_keys" SET "last_used_at" = '2024-07-16 16:34:50.900480' WHERE "api_keys"."id" = 21 | UPDATE "api_keys" SET "last_used_at" = '2024-07-16 16:34:48.163449' WHERE "api_keys"."id" = 21
      293101 | discourse    |       293083 | discourse     | UPDATE "api_keys" SET "last_used_at" = '2024-07-16 16:34:49.485074' WHERE "api_keys"."id" = 21 | UPDATE "api_keys" SET "last_used_at" = '2024-07-16 16:34:48.163449' WHERE "api_keys"."id" = 21

As you can see, many requests are trying to update the last usage time of the same API key.

For now, we have increased the number of API keys that our application is using to reduce the probability of collision. I noticed that there is code in place that checks if the API key was modified in the last minute to avoid an update. However, I assume that since we are using more than one pod to process the requests, this protection is not effective.

I am unsure if we should report this as a bug or if there are any parameters to avoid this kind of collision (either in Discourse or PostgreSQL). Note that for us, the information related to the last usage is relevant, but a resolution of 1 day would be sufficient.

I would also like to know what the preferred approach is to manage PostgreSQL HA with auto recovery.

Thanks

2

Has anyone encountered a similar issue or can provide some guidance on what to investigate? Could it be possible that Discourse is not designed to handle this volume of requests per second?

Thank you in advance.