mirror of
https://github.com/LemmyNet/lemmy.git
synced 2025-09-02 11:13:51 +00:00
644a448aa9
* [0.19] Fixing active counts slow queries. (#5907) * Fixing active counts slow queries. * Simplify back to str tuple * Batch site and community updates * Using update from temp table * Making aggs temp table use interval name. * Make dev setup use optimized postgres. * Addressing PR comments. * Use ref * Removing system custom info from customPostgresql.conf * Forgot to remove old scheduled tasks. * Making sure migrations aren't missing anything from release/v0.19 Checked using git diff --diff-filter D --stat release/v0.19 migrations * Rename all migrations to come after release/v0.19 migrations * Add liked_at is not null check.
71 lines
2.5 KiB
SQL
71 lines
2.5 KiB
SQL
-- Creates combined tables for
|
|
-- Inbox: (replies, comment mentions, post mentions, and private_messages)
|
|
-- Also add post mentions, since these didn't exist before.
|
|
-- Rename the person_mention table to person_comment_mention
|
|
ALTER TABLE person_mention RENAME TO person_comment_mention;
|
|
|
|
-- Create the new post_mention table
|
|
CREATE TABLE person_post_mention (
|
|
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
recipient_id int REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
|
|
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
|
|
read boolean DEFAULT FALSE NOT NULL,
|
|
published timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (recipient_id, post_id)
|
|
);
|
|
|
|
-- Updating the history
|
|
CREATE TABLE inbox_combined AS
|
|
SELECT
|
|
published,
|
|
id AS comment_reply_id,
|
|
NULL::int AS person_comment_mention_id,
|
|
NULL::int AS person_post_mention_id,
|
|
NULL::int AS private_message_id
|
|
FROM
|
|
comment_reply
|
|
UNION ALL
|
|
SELECT
|
|
published,
|
|
NULL::int,
|
|
id,
|
|
NULL::int,
|
|
NULL::int
|
|
FROM
|
|
person_comment_mention
|
|
UNION ALL
|
|
SELECT
|
|
published,
|
|
NULL::int,
|
|
NULL::int,
|
|
id,
|
|
NULL::int
|
|
FROM
|
|
person_post_mention
|
|
UNION ALL
|
|
SELECT
|
|
published,
|
|
NULL::int,
|
|
NULL::int,
|
|
NULL::int,
|
|
id
|
|
FROM
|
|
private_message;
|
|
|
|
ALTER TABLE inbox_combined
|
|
ADD COLUMN id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
ALTER COLUMN published SET NOT NULL,
|
|
ADD CONSTRAINT inbox_combined_comment_reply_id_fkey FOREIGN KEY (comment_reply_id) REFERENCES comment_reply ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD CONSTRAINT inbox_combined_person_comment_mention_id_fkey FOREIGN KEY (person_comment_mention_id) REFERENCES person_comment_mention ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD CONSTRAINT inbox_combined_person_post_mention_id_fkey FOREIGN KEY (person_post_mention_id) REFERENCES person_post_mention ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD CONSTRAINT inbox_combined_private_message_id_fkey FOREIGN KEY (private_message_id) REFERENCES private_message ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD UNIQUE (comment_reply_id),
|
|
ADD UNIQUE (person_comment_mention_id),
|
|
ADD UNIQUE (person_post_mention_id),
|
|
ADD UNIQUE (private_message_id),
|
|
ADD CONSTRAINT inbox_combined_check CHECK (num_nonnulls (comment_reply_id, person_comment_mention_id, person_post_mention_id, private_message_id) = 1);
|
|
|
|
CREATE INDEX idx_inbox_combined_published ON inbox_combined (published DESC, id DESC);
|
|
|
|
CREATE INDEX idx_inbox_combined_published_asc ON inbox_combined (reverse_timestamp_sort (published) DESC, id DESC);
|
|
|