I’m still pondering the least-intrusive way to deal with PostgreSQL fragmenting the comment table so badly the way Lemmy does things.
Rust code does INSERT, waits for the primary key ID
Rust code does UPDATE, puts the ID on path
Rust code does UPDATE, puts the ID on ap_id
At some point her TRIGGERS are running to comment_aggregates on the comment row, plus counting the new comment in site/community/person totals.
Then there is counting, which there is a request in !lemmydev@lemm.ee this past weekend to have a new count of direct-children, on top of the tree counting of children.
Right now when a reply comment is made, each parent in in the branch is updated by count. This UPDATE is one of the slower parts of Lemmy’s PostfreSQL transactions. But it also is generating post_aggregate dead rows due to the children count update.
I’m still pondering the least-intrusive way to deal with PostgreSQL fragmenting the comment table so badly the way Lemmy does things.
Then there is counting, which there is a request in !lemmydev@lemm.ee this past weekend to have a new count of direct-children, on top of the tree counting of children.
Right now when a reply comment is made, each parent in in the branch is updated by count. This UPDATE is one of the slower parts of Lemmy’s PostfreSQL transactions. But it also is generating post_aggregate dead rows due to the children count update.