Of course, that would be an insane amount of work, especially if it would get ignored, but something to consider!
I already did an insane amount of work to populate a Lemmy database with over 10 million posts. It is so incredibly slow out of the box that the normal API would take days to accomplish this. i had to rewrite the SQL TRIGGER logic to allow bulk inserts.
Here is my work on that:
DROP TRIGGER site_aggregates_post_insert ON public.post;
/*
TRIGGER will be replaced with per-statement INSERT only
*/
CREATE TRIGGER site_aggregates_post_insert
AFTER INSERT ON public.post
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION site_aggregates_post_insert();
DROP TRIGGER community_aggregates_post_count ON public.post;
/*
TRIGGER will be replaced with per-statement INSERT only
*/
CREATE TRIGGER community_aggregates_post_count
AFTER INSERT ON public.post
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION community_aggregates_post_count();
DROP TRIGGER person_aggregates_post_count ON public.post;
/*
TRIGGER will be replaced with per-statement INSERT only
*/
CREATE TRIGGER person_aggregates_post_count
AFTER INSERT ON public.post
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION person_aggregates_post_count();
/*
TRIGGER will be replaced with per-statement INSERT only
no Lemmy-delete or SQL DELETE to be performed during this period.
*/
CREATE OR REPLACE FUNCTION public.site_aggregates_post_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE site_aggregates SET posts = posts +
(SELECT count(*) FROM new_rows WHERE local = true)
WHERE site_id = 1
;
RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.community_aggregates_post_count() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
community_aggregates ca
SET
posts = posts + p.new_post_count
FROM (
SELECT count(*) AS new_post_count, community_id
FROM new_rows
GROUP BY community_id
) AS p
WHERE
ca.community_id = p.community_id;
RETURN NULL;
END
$$;
/*
TRIGGER will be replaced with per-statement INSERT only
no Lemmy-delete or SQL DELETE to be performed during this period.
*/
CREATE OR REPLACE FUNCTION public.person_aggregates_post_count() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
person_aggregates personagg
SET
post_count = post_count + p.new_post_count
FROM (
SELECT count(*) AS new_post_count, creator_id
FROM new_rows
GROUP BY creator_id
) AS p
WHERE
personagg.person_id = p.creator_id;
RETURN NULL;
END
$$;
/*
***********************************************************************************************
** comment table
*/
DROP TRIGGER post_aggregates_comment_count ON public.comment;
/*
TRIGGER will be replaced with per-statement INSERT only
*/
CREATE TRIGGER post_aggregates_comment_count
AFTER INSERT ON public.comment
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION post_aggregates_comment_count();
-- IMPORTANT NOTE: this logic for INSERT TRIGGER always assumes that the published datestamp is now(), which was a logical assumption with general use of Lemmy prior to federation being added.
CREATE OR REPLACE FUNCTION public.post_aggregates_comment_count() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
-- per statement update 1
post_aggregates postagg
SET
comments = comments + c.new_comment_count
FROM (
SELECT count(*) AS new_comment_count, post_id
FROM new_rows
GROUP BY post_id
) AS c
WHERE
postagg.post_id = c.post_id;
UPDATE
-- per statement update 2
post_aggregates postagg
SET
newest_comment_time = max_published
FROM (
SELECT MAX(published) AS max_published, post_id
FROM new_rows
GROUP BY post_id
) AS c
WHERE
postagg.post_id = c.post_id;
UPDATE
-- per statement update 3
post_aggregates postagg
SET
newest_comment_time_necro = max_published
FROM (
SELECT MAX(published) AS max_published, post_id, creator_id
FROM new_rows
WHERE published > ('now'::timestamp - '2 days'::interval)
GROUP BY post_id, creator_id
) AS c
WHERE
postagg.post_id = c.post_id
AND c.creator_id != postagg.creator_id
;
RETURN NULL;
END
$$;
DROP TRIGGER community_aggregates_comment_count ON public.comment;
CREATE TRIGGER community_aggregates_comment_count
AFTER INSERT ON public.comment
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION public.community_aggregates_comment_count();
CREATE OR REPLACE FUNCTION public.community_aggregates_comment_count() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
community_aggregates ca
SET
comments = comments + p.new_comment_count
FROM (
SELECT count(*) AS new_comment_count, community_id
FROM new_rows AS nr
JOIN post AS pp ON nr.post_id = pp.id
GROUP BY pp.community_id
) AS p
WHERE
ca.community_id = p.community_id
;
RETURN NULL;
END
$$;
DROP TRIGGER person_aggregates_comment_count ON public.comment;
CREATE TRIGGER person_aggregates_comment_count
AFTER INSERT ON public.comment
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION public.person_aggregates_comment_count();
CREATE OR REPLACE FUNCTION public.person_aggregates_comment_count() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
person_aggregates personagg
SET
comment_count = comment_count + p.new_comment_count
FROM (
SELECT count(*) AS new_comment_count, creator_id
FROM new_rows
GROUP BY creator_id
) AS p
WHERE
personagg.person_id = p.creator_id;
RETURN NULL;
END
$$;
DROP TRIGGER site_aggregates_comment_insert ON public.comment;
CREATE TRIGGER site_aggregates_comment_insert
AFTER INSERT ON public.comment
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION public.site_aggregates_comment_insert();
CREATE OR REPLACE FUNCTION public.site_aggregates_comment_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE site_aggregates
SET comments = comments +
(
SELECT count(*) FROM new_rows WHERE local = true
)
WHERE site_id = 1
;
RETURN NULL;
END
$$;
With this in place, 300,000 posts a minute can be generated and reaching levels of 5 million or 10 million don't take too long.