MbinOrg
Review the `favourite` table and it's indexs to reduce used storage
I have around 10 million rows in my favourite table and it is by far the largest table (storage wise) in my db. It is taking up a total of 31GB. Only 9.4GB are from the table itself while 22GB are from the indexes. I suggest a review be made on both the table itself and its indexes to see what can be done to reduce its size.
For context, here is my query of the top tables taking up the most storage in my db:
table_name | row_estimate | total | table | index | toast
-------------------------------+---------------+------------+----------+------------+------------
favourite | 1.3553085e+08 | 31 GB | 9472 MB | 22 GB | 8192 bytes
messenger_messages | 1.0479002e+07 | 29 GB | 14 GB | 601 MB | 14 GB
entry_comment | 7.711465e+06 | 9512 MB | 5155 MB | 3733 MB | 624 MB
entry | 1.020152e+06 | 2259 MB | 810 MB | 939 MB | 510 MB
post | 544430 | 1284 MB | 629 MB | 591 MB | 63 MB
entry_comment_vote | 7.531793e+06 | 1067 MB | 439 MB | 628 MB |
post_comment | 825041 | 1007 MB | 546 MB | 439 MB | 23 MB
notification | 4.122492e+06 | 759 MB | 345 MB | 414 MB | 8192 bytes
image | 1.009419e+06 | 590 MB | 382 MB | 208 MB | 64 kB
embed | 1.764939e+06 | 503 MB | 215 MB | 288 MB |
user | 301763 | 468 MB | 227 MB | 228 MB | 13 MB
entry_vote | 2.805029e+06 | 391 MB | 163 MB | 228 MB |
oauth2_client_access | 1.847488e+06 | 255 MB | 199 MB | 57 MB |
hashtag_link | 1.42693e+06 | 197 MB | 74 MB | 123 MB |
activity | 416538 | 182 MB | 43 MB | 62 MB | 76 MB
magazine | 16269 | 52 MB | 21 MB | 23 MB | 7648 kB
post_vote | 170408 | 26 MB | 10200 kB | 16 MB |
oauth2_refresh_token | 35730 | 18 MB | 7744 kB | 11 MB |
magazine_log | 91670 | 17 MB | 7216 kB | 9792 kB | 8192 bytes
oauth2_access_token | 44999 | 17 MB | 10 MB | 6480 kB | 8192 bytes
hashtag | 153607 | 16 MB | 7464 kB | 8912 kB | 8192 bytes
sessions | 1044 | 6592 kB | 968 kB | 5032 kB | 592 kB
post_comment_vote | 34617 | 6152 kB | 2264 kB | 3888 kB |
domain | 34732 | 4672 kB | 2192 kB | 2480 kB |
magazine_subscription | 23285 | 3216 kB | 1272 kB | 1944 kB |
moderator | 20325 | 3024 kB | 1112 kB | 1912 kB |
pg_statistic | 920 | 2040 kB | 896 kB | 56 kB | 1088 kB
instance | 8389 | 1920 kB | 1232 kB | 680 kB | 8192 bytes
pg_proc | 3378 | 1240 kB | 824 kB | 360 kB | 56 kB
pg_attribute | 5031 | 1136 kB | 800 kB | 336 kB |
Small investigation says these are the current indexes we are using in the favourite table:
Some indexes have no name, but just idx_<number>. But are most likely linked directly to the columns like magazine_id, user_id, entry_id and for example entry_comment_id.. The primary key (id) is under the name favourite_pkey.
The biggest indexes are the index that are the so called "unique" keys, that are combining 2 or more columns together.
For example the favourite_user_entry_comment_unique_idx index is checking for uniqueness between the following two columns, and ensures there will not be a second insert with the same combination of these two column values:
Those 4 unique indexes ensures no duplicated likes with either a thread, thread comment, post or a comment post coming from the same user (hence user_id)..
So long story short, I believe the indexes are correct and also the unique indexes are here for a reason.. The problem is just the incredible amount of rows in this table.
Like you can see in my DB (in red):
Instead of storing each like, we could of course just increase some favourite number. Meaning we will not track anymore which user is behind it.