Self-ddos

Product Support

AI-generated summary

The discussion revolves around a self-DDoS issue on a Discourse forum, where a daily index creation process (CREATE INDEX CONCURRENTLY) causes high database load and site downtime. Eviepayne reports the issue, suspecting it’s related to an underpowered or misconfigured database setup. Falco suggests the problem may stem from insufficient database resources. Eviepayne clarifies the setup: a DigitalOcean-managed instance with 8GB RAM, 4vCPU, and an external database and cache, indicating it’s a non-standard installation. tobiaseigen asks if the issue was resolved. Eviepayne notes the problem seems to have stopped after updating to a newer container revision, though the root cause remains unclear. The installation is no longer fully supported, with external database and cache components in use, and the user suspects the issue may have been resolved by updates or changes in the underlying infrastructure.

1

I’m unsure if this is a bug or not but I seem to be experiencing my forum self-ddossing.
Once a day the site will go down because it seems to be creating an index:

CREATE INDEX CONCURRENTLY temp_idx_recent_regular_post_search_data
ON post_search_data USING GIN(search_data)
WHERE NOT private_message AND post_id >= 0

This in turn creates a lot of parallellised queries:

SELECT “posts”.“id”, “posts”.“user_id”, “posts”.“topic_id”, “posts”.“post_number”, “posts”.“raw”, “posts”.“cooked”, “posts”.“created_at”, “posts”.“updated_at”, “posts”.“reply_to_post_number”, “posts”.“reply_count”, “posts”.“quote_count”, “posts”.“deleted_at”, “posts”.“off_topic_count”, “posts”.“like_count”, “posts”.“incoming_link_count”, “posts”.“bookmark_count”, “posts”.“score”, “posts”.“reads”, “posts”.“post_type”, “posts”.“sort_order”, “posts”.“last_editor_id”, “posts”.“hidden”, “posts”.“hidden_reason_id”, “posts”.“notify_moderators_count”, “posts”.“spam_count”, “posts”.“illegal_count”, “posts”.“inappropriate_count”, “posts”.“last_version_at”, “posts”.“user_deleted”, “posts”.“reply_to_user_id”, “posts”.“percent_rank”, “posts”.“notify_user_count”, “posts”.“like_score”, “posts”.“deleted_by_id”, “posts”.“edit_reason”, “posts”.“word_count”, “posts”.“version”, “posts”.“cook_method”, “posts”.“wiki”, “posts”.“baked_at”, “posts”.“baked_version”, “posts”.“hidden_at”, “posts”.“self_edits”, “posts”.“reply_quoted” …

The issue here is im using a managed database that has a connection pool of 197 which is quickly filled up.

Versions:

3.6.0.beta2-latest
c3efcc99e3e1f3a0855527d669d8b8f27cdd901b

2

This smells like you are running in an under-powered or misconfigured database.

How large is your database and what are the database server specs?

3

Digitalocean managed
8 GB RAM / 4vCPU / 160 GiB Disk / Primary only / TOR1 - PostgreSQL 15
Transaction mode pool (197 connections)
100GB uncompressed is used

4

@Eviepayne were you able to get this resolved? If not, can you tell us what more you tried and have found out about your problem? Is this a supported install, created using the official install instructions?

5

As far as I can tell I think it’s resolved. idk how I fixed it but it seemed to have stopped after some updates to newer container revisions.
I’m on: 4d20baa8754fd782f01fbf8bf5c687cae846915a

My installation is fairly removed from the supported install at this point.
The standard install applies but what has changed is:

External Database (managed with digitalocean)
External Cache (managed valkey with digitalocean)
Pinned container revision
ACME Client reconfigured for DNS challenges

There was an issue in the past where I couldn’t update due to pgvector versions changing and not being available at DO for pg15, that’s since been resolved so I updated and the self-ddosing behavior appears to not be occurring anymore.

I think the version I was on before was: db67a0841e8e14ae70de0890441f8158c50ae3c9