We're Hiring!

Mattermost, Inc.

Disk space utilization

Hello,

I am using Gitlab Mattermost Team Edition and have around 20 users.

Mattermost Version: 5.13.2
Database Schema Version: 5.13.0
Database: postgres

Since few months i experience Mattermost is taking lots of disk space, in last 20 days it consumed around 1.3 GB space. Below directory is taking lots of space which has many files inside.
/var/opt/gitlab/postgresql/data/base/16401

I am not sure what to do to make it consume less space or how to delete old chat from channels. Kindly suggest to resolve this issue.

Thank You,
Ravi K.

Hello @ravi.kukadia! Thanks for opening a topic here, I hope we can help you!

This appears to be the data stored in your Postgres database if I am interpreting the file path you have provided correctly. If it is not going to significantly impact your organization, you could delete old messages from previous dates from your database, which, as you would be deleting data, would inherently free up disk space. Can I ask how many total messages your instance has seen? These numbers can be obtained from the system console when logged in as a system admin or the master admin account.

Thanks for reply.

From statistics i can say Total Posts are 10227315. But it includes system alerts/notifications as well which we configured in channel. Is there any way i can delete all messages from channel? i cant see way through console.

Thank You,
Ravi

Yes, 10 Million messages is quite a large amount :laughing:

You can delete messages in bulk using the Postgres DELETE and TRUNCATE commands, of which you can read the official documentation on here.

Let me know if it works! I would strongly suggest making a backup of your database before you do attempt this, just in case it goes wrong somehow, so that you have a way to restore and recover!

yes, its a huge number. Is there a way i can delete all messages of a channel on which we have configured notification? like using command i can select that specific channel and delete all posts from it.

Yes, there indeed is! Again, I strongly suggest making a backup of your database before deleting, in the event that you select the wrong channel ID etc, and need to restore data.

This is a bit of an explanation of how we can do this.

First, we are going to need the channel ID of the channel you are wanting to delete all messages from.

If your database schema is anything like mine, your Channels table in your database should have a schema like this (this is my personal Mattermost instances’ schema):

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| Id               | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt         | bigint       | YES  | MUL | NULL    |       |
| UpdateAt         | bigint       | YES  | MUL | NULL    |       |
| DeleteAt         | bigint       | YES  | MUL | NULL    |       |
| TeamId           | varchar(26)  | YES  | MUL | NULL    |       |
| Type             | varchar(1)   | YES  |     | NULL    |       |
| DisplayName      | varchar(64)  | YES  |     | NULL    |       |
| Name             | varchar(64)  | YES  | MUL | NULL    |       |
| Header           | text         | YES  |     | NULL    |       |
| Purpose          | varchar(250) | YES  |     | NULL    |       |
| LastPostAt       | bigint       | YES  |     | NULL    |       |
| TotalMsgCount    | bigint       | YES  |     | NULL    |       |
| ExtraUpdateAt    | bigint       | YES  |     | NULL    |       |
| CreatorId        | varchar(26)  | YES  |     | NULL    |       |
| SchemeId         | varchar(26)  | YES  | MUL | NULL    |       |
| GroupConstrained | tinyint(1)   | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

So, as we can see the columns in the table, we can find the channel ID by querying the Channels table in your mattermost database, and matching the Id column ID string, with the name of the channel that is also outputted, if you use a command such as
select Id,DisplayName,TotalMsgCount from Channels; (the command I used), and hopefully will get a output such as this:

+----------------------------+------------------------------------------------------------------+---------------+
| Id                         | DisplayName                                                      | TotalMsgCount |
+----------------------------+------------------------------------------------------------------+---------------+
| 4wndj85s4pr5ik96wjx63fadzy | XXXXXXXXXXXX REDACTED XXXXXXXX                                   |             0 |
| 58c7aubeatfhtksd5wzdceqwgr |                                                                  |             6 |
| 67w4guom5bdjpytc8y9jg5gj9y |                                                                  |             0 |
| 6f8785t3cpg95jch13m5dx86go | Town Square                                                      |             5 |
| 7qtrc5gt4jdwxegoaq7r15nwny |                                                                  |             1 |
| 8wikjkizwfrmip5digszpj8rha |                                                                  |             0 |
| 968qdow69fr33nxj7fh91jnwjc | General                                                          |           149 |
| 9yhw11oktpyt9j6equrd1odi4a | Off-Topic                                                        |             3 |
| a6179a31ijd6igw6pkx4stc3pa |                                                                  |             7 |
| as1zdah4nj81ucmdnby5cyuxpy | XXXXXX                                                           |             0 |
| aty66iae13b1x8fczu5i53uqsr |                                                                  |            24 |
| bfx7bb9xbtgt9gjuc7dgqocp7w | Off Topic                                                        |             5 |
| bqmguzisibdoxfa3uy89x5i6ra |                                                                  |             0 |
| e5gy6iyoyb81ig9mwfetw8uzbr | Off-Topic                                                        |             5 |
| e5hjzmyuj7bhijmquy1g8jggoc | Memes                                                            |             2 |
| enhsd4ed4pbjbm17cnkstaorqw |                                                                  |             0 |
| eryjb65fcpf38d3d1icqya16xc |                                                                  |             0 |
| ewhdtqnqgfbedmcsz7trrhku6a | Hydra Admins                                                     |             1 |
| fezfwtjhzjfj5n8ghs53rjr91a | Town Square                                                      |            97 |
| fqwbb5m3btfrzxn9mw4hrofasy |                                                                  |             0 |
| gx1cj5kkebg19p66kmu6n5ypoe | Off-Topic                                                        |             0 |
| hotbao1rzfy17pzucy7c9439uc |                                                                  |             1 |
| kcdopbm3678ytcdcxprmq3ofza |                                                                  |            47 |
| m18wmjypwfgp9fe8fcjomquiic | XXXXXXXXX channel                                                |             1 |
| mqg8xeu43i8a5yk4wtjqu4ffbh |                                                                  |             0 |
| mx5oy6wmafbwxcjhjo6hog6s5c |                                                                  |             0 |
| ntn7idqitjypzd4gm5n7uhx5ho | General                                                          |           205 |
| onyb7mtbkjrjfmbb665fhnsguo |                                                                  |             0 |
| p5f1e9e5ztyd8gd8d3gpmtzdqa | Off-Topic                                                        |             0 |
| pb7mjkwkrprtjxngs3btqsdj9a | XXXXXXXX                                                         |             9 |
| pes4zewzyb8hdy6tmfnxan6e5y | XXXXXXXXXXXXXXXXX                                                |             1 |
| s9zzn9q9iffd5pnztp9pin1gph | Vouches                                                          |             1 |
| se6phdmcdbg6dduwrkt4abf5re |                                                                  |             0 |
| t76b6u1ysjgcifp4iomp4rssmh |                                                                  |             0 |
| tiyogrs1jby3mdqftht38zk67h |                                                                  |             3 |
| u47ozuoxgbfs9yucbxj33zbwor | Town Square                                                      |             3 |
| uaa1edyu7j8cm8n4u7rfowt8ac | Town Square                                                      |            72 |
| uu39ab93jt8q8ktzc3ypn69zpo |                                                                  |             2 |
| wicy79wzjfnu5mdry7exh5a94a | Town Square                                                      |             0 |
| y7knrhegmb849dojiqpryhzsuo | General                                                          |           361 |
| zbo58hxwdpnobew1ogm8u5kone |                                                                  |           164 |
+----------------------------+------------------------------------------------------------------+---------------+

This will allow you to obtain the Channel ID, which will be used to perform the delete query. Once you find the correct ID, make note of it, be that in a text file on your desktop, or just copying and pasting it in your shell.

Going forward, I am going to use a channel ID from my database, which is u47ozuoxgbfs9yucbxj33zbwor.

Now that we have the channel ID, we can create our query to search for all messages in the Posts table that have been posted in the channel we just got the ID for.

To do this, we need to search based on a condition. Here is a brief overview of how this works, referenced from here, before I go into the Mattermost applied usage:

The DELETE statement can be used with a single condition. The condition is set using the WHERE clause. Consider the Price table with the following data:


Let us delete the record with an id of 4:

DELETE FROM Price
WHERE id = 4;

The above command will delete the records in which the id is 4. Let us confirm whether the deletion was successful:


The row with an id of 4 has been deleted.

So, a few things to note. In the example above, Price is the name of the table we are deleting in, the variable id given is the column name of the location to search for the string in, and the 4 is the string we want to search for.

Therefore, to apply this to our goal, we first obtain the schema of the Posts table of our mattermost database with show columns from Posts; :

| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| Id           | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt     | bigint       | YES  | MUL | NULL    |       |
| UpdateAt     | bigint       | YES  | MUL | NULL    |       |
| EditAt       | bigint       | YES  |     | NULL    |       |
| DeleteAt     | bigint       | YES  | MUL | NULL    |       |
| IsPinned     | tinyint(1)   | YES  | MUL | NULL    |       |
| UserId       | varchar(26)  | YES  | MUL | NULL    |       |
| ChannelId    | varchar(26)  | YES  | MUL | NULL    |       |
| RootId       | varchar(26)  | YES  | MUL | NULL    |       |
| ParentId     | varchar(26)  | YES  |     | NULL    |       |
| OriginalId   | varchar(26)  | YES  |     | NULL    |       |
| Message      | text         | YES  | MUL | NULL    |       |
| Type         | varchar(26)  | YES  |     | NULL    |       |
| Props        | text         | YES  |     | NULL    |       |
| Hashtags     | text         | YES  | MUL | NULL    |       |
| Filenames    | text         | YES  |     | NULL    |       |
| FileIds      | varchar(150) | YES  |     | NULL    |       |
| HasReactions | tinyint(1)   | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)

As you can see above, the channel ID for each message is located in the column titled ChannelId, so this is the column we are going to use to search for all messages in our targeted channel.

At this point, we should have verified several things:

  • Channel ID of the channel we desire to delete all messages from
    (u47ozuoxgbfs9yucbxj33zbwor)
  • Table and column names for the location of the posts, and location of channel ID logging for each message
    (Posts, ChannelId)
  • Query to search and delete
    DELETE FROM Price WHERE id = 4;

Taking all this information, and knowing the variable’s functions in our query, we can plug our values in, resulting in an operational query such as this:
DELETE FROM Posts WHERE ChannelId = u47ozuoxgbfs9yucbxj33zbwor;
Running this command should do the trick – remember, you have upwards of 10 million posts, this could take quite a while to run, and depending on your server’s speed, could impact performance as well. Be patient, it will work!

After the command finishes running, you can check to see that the messages were actually deleted using the same command we used to get the channel ID, and compare the number of posts in the TotalMsgCount column to that of the original value.

For further information, and an insight into the more advanced operations you can complete with the DELETE command, reference the Postgres Docs on the command.
I hope this helps, and I hope it works!
(Disclaimer: I haven’t actually tried this as I need my data :laughing:. but it should work without a doubt, but being extra cautious and taking a backup of your database prior to deleting the messages is a very smart thing to do)

If there are any additional difficulties, please let me know, I’ll be more than happy to continue providing assistance as I can!

1 Like

Thanks a lot for detailed explanation. It is really a great help, i appreciate.

I will try to follow these steps in weekend to avoid any issues. Will update you.

My pleasure Ravi, I’m glad to be able to help, good luck, and I’ll be here if you encounter any issues!