I'm looking for a little help working through some database scaling issues with #WriteFreely.
On our Write.as instance, we're up to 4.25 million rows in our `posts` table, and it's causing problems for some individual blogs with 9,000+ posts.
I have an idea of what some bottleneck queries are. But wondering if there are easy db optimizations we might make (indexes, etc.), and if anyone can help identify what they might be.
Boosts appreciated!
Matt Baer /
npub1eh…6ysfx
2023-08-11 16:28:06
Author Public Key
npub1eh33dlkkp8kgj6w8jffsn5wqpte8wwfavxl90xq0huu8h7qvdh5sk6ysfxPublished at
2023-08-11 16:28:06Event JSON
{
"id": "d012b466733644d7521bd402f0c96b3874450a449d51ef25cc61f029b305daf0",
"pubkey": "cde316fed609ec8969c7925309d1c00af277393d61be57980fbf387bf80c6de9",
"created_at": 1691764086,
"kind": 1,
"tags": [
[
"t",
"WriteFreely"
],
[
"content-warning",
"WriteFreely scaling help"
],
[
"proxy",
"https://writing.exchange/users/matt/statuses/110871451172679911",
"activitypub"
]
],
"content": "I'm looking for a little help working through some database scaling issues with #WriteFreely.\n\nOn our Write.as instance, we're up to 4.25 million rows in our `posts` table, and it's causing problems for some individual blogs with 9,000+ posts.\n\nI have an idea of what some bottleneck queries are. But wondering if there are easy db optimizations we might make (indexes, etc.), and if anyone can help identify what they might be.\n\nBoosts appreciated!",
"sig": "e48da60b0b96ee826f6ee7ccb39eabb8ef0ec20893b618553f346a785f3a62fd2e313748db24e2c669e9aa6100ba62ec12fa27097069b1c85788ae1b2363afee"
}