Post #3750816

2026-07-12 00:43 UTC

For anyone who might find it useful, here's some stats on the note (posts) table from two real-world Sharkey instances: Instance A (3 years old, 12 users (2 active), 472 pub and 491 sub connections): +---------------+------------+--------+------------------------------+ |Metric |Value |Percent |Metric | +---------------+------------+--------+------------------------------+ |total | 25,326,479| 100.00%|All notes | |isSilencedUser | 604,310| 2.39%|Notes by a silenced user | |isSilencedHost | 2,940,100| 11.61%|Notes from a silenced instance| |isSuspendedUser| 282,627| 1.12%|Notes by a suspended user | |isSuspendedHost| 85,733| 0.34%|Notes from a blocked instance | |isPublic | 11,910,305| 47.03%|Public notes | |isHome | 10,244,218| 40.45%|Home-only / unlisted notes | |isFollowers | 3,155,757| 12.46%|Followers-only notes | |isSpecified | 16,199| 0.06%|Specified / DM notes | |isPoll | 53,258| 0.21%|Polls | |isMedia | 2,382,441| 9.41%|Notes w/ media | |isReply | 9,541,477| 37.67%|Replies | |isReplyToSelf | 2,058,518| 8.13%|Self-replies | |isBoost | 6,192,622| 24.45%|Boosts | |isBoostOfSelf | 190,660| 0.75%|Self-boosts | |isQuote | 225,201| 0.89%|Quotes | |isQuoteOfSelf | 70,205| 0.28%|Self-quotes | |isChannel | 2| 0.00%|Notes in a channel | |isRemote | 25,259,313| 99.73%|Remote notes | |isLocal | 67,166| 0.27%|Local notes | |isLocalOnly | 311| 0.00%|Local-only (defederated) notes| +---------------+------------+--------+------------------------------+ Instance B (3 years old, 2,731 users (130 active), 1,872 pub and 2,149 sub connections): +---------------+------------+--------+------------------------------+ |Metric |Value |Percent |Metric | +---------------+------------+--------+------------------------------+ |total | 87,276,701| 100.00%|All notes | |isSilencedUser | 1,018,657| 1.17%|Notes by a silenced user | |isSilencedHost | 13,408,299| 15.36%|Notes from a silenced instance| |isSuspendedUser| 414,901| 0.48%|Notes by a suspended user | |isSuspendedHost| 819,842| 0.94%|Notes from a blocked instance | |isPublic | 40,977,139| 46.95%|Public notes | |isHome | 28,991,756| 33.22%|Home-only / unlisted notes | |isFollowers | 17,205,922| 19.71%|Followers-only notes | |isSpecified | 101,884| 0.12%|Specified / DM notes | |isPoll | 298,023| 0.34%|Polls | |isMedia | 9,727,324| 11.15%|Notes w/ media | |isReply | 26,004,182| 29.80%|Replies | |isReplyToSelf | 5,551,978| 6.36%|Self-replies | |isBoost | 32,734,600| 37.51%|Boosts | |isBoostOfSelf | 577,841| 0.66%|Self-boosts | |isQuote | 398,429| 0.46%|Quotes | |isQuoteOfSelf | 133,203| 0.15%|Self-quotes | |isChannel | 34| 0.00%|Notes in a channel | |isRemote | 86,199,472| 98.77%|Remote notes | |isLocal | 1,077,229| 1.23%|Local notes | |isLocalOnly | 2,009| 0.00%|Local-only (defederated) notes| +---------------+------------+--------+------------------------------+ #FediDev #Fedi #ActivityPub #AP #Sharkey

Replies (4)

@hazelnoot@enby.life 2026-07-12 00:45

here's the query used. fair warning - it's ugly and takes minutes to complete! SELECT unnest(array[ 'total', 'isSilencedUser', 'isSilencedHost', 'isSuspendedUser', 'isSuspendedHost', 'isPublic', 'isHome', 'isFollowers', 'isSpecified', 'isPoll', 'isMedia', 'isReply', 'isReplyToSelf', 'isBoost', 'isBoostOfSelf', 'isQuote', 'isQuoteOfSelf', 'isChannel', 'isRemote', 'isLocal', 'isLocalOnly' ]) AS "Metric", unnest(array[ to_char("total", '999,999,990'), to_char("isSilencedUser", '999,999,990'), to_char("isSilencedHost", '999,999,990'), to_char("isSuspendedUser", '999,999,990'), to_char("isSuspendedHost", '999,999,990'), to_char("isPublic", '999,999,990'), to_char("isHome", '999,999,990'), to_char("isFollowers", '999,999,990'), to_char("isSpecified", '999,999,990'), to_char("isPoll", '999,999,990'), to_char("isMedia", '999,999,990'), to_char("isReply", '999,999,990'), to_char("isReplyToSelf", '999,999,990'), to_char("isBoost", '999,999,990'), to_char("isBoostOfSelf", '999,999,990'), to_char("isQuote", '999,999,990'), to_char("isQuoteOfSelf", '999,999,990'), to_char("isChannel", '999,999,990'), to_char("isRemote", '999,999,990'), to_char("isLocal", '999,999,990'), to_char("isLocalOnly", '999,999,990') ]) AS "Value", unnest(array[ to_char((("total"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSilencedUser"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSilencedHost"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSuspendedUser"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSuspendedHost"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isPublic"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isHome"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isFollowers"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isSpecified"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isPoll"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isMedia"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isReply"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isReplyToSelf"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isBoost"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isBoostOfSelf"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isQuote"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isQuoteOfSelf"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isChannel"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isRemote"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isLocal"::float8 / "total"::float8) * 100::float8), '990.00%'), to_char((("isLocalOnly"::float8 / "total"::float8) * 100::float8), '990.00%') ]) AS "Percent", unnest(array[ 'All notes', 'Notes by a silenced user', 'Notes from a silenced instance', 'Notes by a suspended user', 'Notes from a blocked instance', 'Public notes', 'Home-only / unlisted notes', 'Followers-only notes', 'Specified / DM notes', 'Polls', 'Notes w/ media', 'Replies', 'Self-replies', 'Boosts', 'Self-boosts', 'Quotes', 'Self-quotes', 'Notes in a channel', 'Remote notes', 'Local notes', 'Local-only (defederated) notes' ]) AS "Metric" FROM ( SELECT COUNT("id") as total, COUNT("id") FILTER ( WHERE "isSilencedUser" = true ) as "isSilencedUser", COUNT("id") FILTER ( WHERE "isSilencedHost" = true ) as "isSilencedHost", COUNT("id") FILTER ( WHERE "isSuspendedUser" = true ) as "isSuspendedUser", COUNT("id") FILTER ( WHERE "isSuspendedHost" = true ) as "isSuspendedHost", COUNT("id") FILTER ( WHERE "isPublic" = true ) as "isPublic", COUNT("id") FILTER ( WHERE "isHome" = true ) as "isHome", COUNT("id") FILTER ( WHERE "isFollowers" = true ) as "isFollowers", COUNT("id") FILTER ( WHERE "isSpecified" = true ) as "isSpecified", COUNT("id") FILTER ( WHERE "isPoll" = true ) as "isPoll", COUNT("id") FILTER ( WHERE "isMedia" = true ) as "isMedia", COUNT("id") FILTER ( WHERE "isReply" = true ) as "isReply", COUNT("id") FILTER ( WHERE "isReplyToSelf" = true ) as "isReplyToSelf", COUNT("id") FILTER ( WHERE "isBoost" = true ) as "isBoost", COUNT("id") FILTER ( WHERE "isBoostOfSelf" = true ) as "isBoostOfSelf", COUNT("id") FILTER ( WHERE "isQuote" = true ) as "isQuote", COUNT("id") FILTER ( WHERE "isQuoteOfSelf" = true ) as "isQuoteOfSelf", COUNT("id") FILTER ( WHERE "isChannel" = true ) as "isChannel", COUNT("id") FILTER ( WHERE "isLocal" = false ) as "isRemote", COUNT("id") FILTER ( WHERE "isLocal" = true ) as "isLocal", COUNT("id") FILTER ( WHERE "isLocalOnly" = true ) as "isLocalOnly" FROM ( SELECT "note"."id", "user"."isSilenced" as "isSilencedUser", COALESCE("instance"."isSilenced", false) as "isSilencedHost", "user"."isSuspended" as "isSuspendedUser", COALESCE("instance"."isBlocked", false) as "isSuspendedHost", "visibility" = 'public' as "isPublic", "visibility" = 'home' as "isHome", "visibility" = 'followers' as "isFollowers", "visibility" = 'specified' as "isSpecified", "hasPoll" as "isPoll", "fileIds" != '{}' as "isMedia", "replyId" IS NOT NULL as "isReply", "replyUserId" = "userId" as "isReplyToSelf", "renoteId" IS NOT NULL AND ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isBoost", "renoteUserId" = "userId" AND ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isBoostOfSelf", "renoteId" IS NOT NULL AND NOT ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isQuote", "renoteUserId" = "userId" AND NOT ( "text" IS NULL AND "cw" IS NULL AND "replyId" IS NULL AND "hasPoll" = false AND "fileIds" = '{}' ) as "isQuoteOfSelf", "channelId" IS NOT NULL as "isChannel", "userHost" IS NULL as "isLocal", "localOnly" as "isLocalOnly" FROM "note" INNER JOIN "user" on "note"."userId" = "user"."id" LEFT JOIN "instance" on "note"."userHost" = "instance"."host" ) note_types ) type_counts

Open ##3750832
@rimu@piefed.social 2026-07-12 00:54

Does 87,276,701 mean 87 GB?

Open ##3752494
@rimu@piefed.social 2026-07-12 00:54

How much in the media storage?

Open ##3752524
@nathan@ublog.hurel.me 2026-07-12 09:09

@hazelnoot@enby.life do you use meilisearch for indexation ? I'm more concerned about the index size than the SQL db size

Open ##3758082