PostgreSQL version: 13.10
My app has a notification feature, where users have a list of events that happened in relation to them (much like the Facebook notifications). Each notification is a row in my notifications table, with many columns (15) such as:
idrecipient_idnotification_type:'post_notification' | 'comment notification' | 'dm_notification' | ...post_id: foreign key to a post, can beNULLcomment_id: foreign key to comment on a post, can beNULLuser_id: foreign key to another user, can beNULL- ...
So for example, if a notification is of type post_notification the comment_id, user_id and other columns will be set to NULL.
Here is a (slightly redacted) \d+ output:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------------+--------------------------------+-----------+----------+-------------------------------------------+----------+--------------+------------- id | bigint | | not null | nextval('notifications_id_seq'::regclass) | plain | | notification_type | character varying(255) | | | | extended | | read | boolean | | not null | false | plain | | recipient_id | bigint | | | | plain | | comment_id | bigint | | | | plain | | topic_id | bigint | | | | plain | | user_id | bigint | | | | plain | | badge_id | bigint | | | | plain | | inserted_at | timestamp(0) without time zone | | not null | | plain | | updated_at | timestamp(0) without time zone | | not null | | plain | | post_id | bigint | | | | plain | | contest_id | bigint | | | | plain | | article_id | bigint | | | | plain | | entity_nbr | integer | | | 1 | plain | | article_comment_id | bigint | | | | plain | | Indexes: "notifications_pkey" PRIMARY KEY, btree (id) "notifications_article_comment_id_index" btree (article_comment_id) "notifications_comment_id_index" btree (comment_id) "notifications_post_id_index" btree (post_id) "notifications_recipient_id_index" btree (recipient_id) Foreign-key constraints: "notifications_article_comment_id_fkey" FOREIGN KEY (article_comment_id) REFERENCES article_comments(id) ON DELETE CASCADE "notifications_article_id_fkey" FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE "notifications_badge_id_fkey" FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE "notifications_comment_id_fkey" FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE "notifications_contest_id_fkey" FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE "notifications_follower_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE "notifications_post_id_fkey" FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE "notifications_recipient_id_fkey" FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE "notifications_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE Access method: heap The feature has been working fairly well for a while, but is now hitting some sort of bottleneck. The table is reaching 13,000,000 records, and I am facing an indexing conundrum:
- Many foreign keys need to be indexed, because when deleting a related entity (a post, for example), the database must delete every notification tied to it. Without an index on the corresponding foreign key, the entity removal fails with a timeout, since it takes too long to retrieve the notifications that needed to be deleted
- But now, many writes to the
notificationstable fail with a timeout, as I believe that maintaining all these indexes has become too costly for writing into the table.
I have been going back and forth with removing and adding indexes trying to fix these two issues, but they seem to be utterly in conflict... Has anyone faced such a dilemma, and has any idea what is the best practice in this situation?
EXPLAIN (ANALYZE, BUFFERS)output for a slow execution.