# Why my discourse postgres\_data is too big
**URL:** https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657
**Category:** Self-hosting
**Created:** [July 5, 2018, 8:54pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657 "2018-07-05T20:54:31Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [July 5, 2018, 8:54pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/1 "2018-07-05T20:54:31Z")
Today I was evaluating my discourse server and found two weird things.
1- If I understand correctly, the discourse postgres\_data folder which is in /var/discourse/shared/standalone folder, is where discourse stores the database. Now this folder for my forum is about 8GB. Which I believe is too big for a humble forum. Can someone explain why its too big?
2- I have another folder named postgres\_data\_old that is also about 7GB. What is this for?
Also, my server memory was about 4GB. I found its mostly consumed. So I upgraded it to 8GB. Again I think a humble forum shouldn’t need that much of memory.
---
### Author:  [@Steven](https://meta.discourse.org/u/Steven)
#### Post date: [July 5, 2018, 9:28pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/2 "2018-07-05T21:28:38Z")
There was a postgres update recently, there is a old folder in case something went wrong.
If your forum works fine, you can execute this command
```
cd /var/discourse
./launcher cleanup app
```
It should clean the old postgres data folder.
For the memory, DIscourse works this way, it uses the most memory it can, you don’t have to worry about that.
If you want it to use less memory, you can change the `db_shared_buffers` in the app.yml (command : `nano containers/app.yml`)
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [July 5, 2018, 10:50pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/3 "2018-07-05T22:50:09Z")
what about the big database? Is it something wrong with my forum? I think its way too larger than it should be.
---
### Author:  [@Steven](https://meta.discourse.org/u/Steven)
#### Post date: [July 5, 2018, 11:17pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/4 "2018-07-05T23:17:31Z")
How many posts do you have ?
I’ll let a specialist give you an answer about that. I know that Discourse stores a lot of information to provide relevent statistics and a good search engine I guess. It may not be alarming.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [July 6, 2018, 12:09am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/5 "2018-07-06T00:09:11Z")
You can run the following commands to see which tables are taking up the most disk space
```plaintext
./launcher enter app
su - postgres
psql discourse
```
```plaintext
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
```
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [July 6, 2018, 7:48am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/6 "2018-07-06T07:48:02Z")
This is the output of my forum for these commands. I think the first 5 rows are consuming too much space. I cant imagine why user\_actions should be around 2GB. Or post timing about 1GB. Can you give me an idea what could be wrong?
On the other hand, is there any way to clean unnecessary data? For example maybe I can get rid of most of email\_logs. I don’t send too many emails. I don’t know why this is so big.
```
public.user_actions | 1792 MB
public.email_logs | 1293 MB
public.post_timings | 731 MB
public.directory_items | 456 MB
public.topic_views | 446 MB
public.posts | 380 MB
public.post_search_data | 298 MB
public.notifications | 170 MB
backup.topic_views | 156 MB
public.post_actions | 155 MB
public.user_histories | 134 MB
backup.directory_items | 123 MB
public.user_auth_tokens | 110 MB
public.users | 91 MB
backup.user_auth_tokens | 87 MB
public.user_visits | 77 MB
backup.posts | 68 MB
backup.post_timings | 65 MB
backup.post_search_data | 64 MB
public.optimized_images | 63 MB
```
---
### Author:  [@Steven](https://meta.discourse.org/u/Steven)
#### Post date: [July 6, 2018, 11:42am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/7 "2018-07-06T11:42:12Z")
Did you do an upgrade recently?
I’ve seen a commit about cleaning the email logs table. After the rebuild, it may be lighter
Also, you might check the setting `delete email logs after days`, it should be safer than deleting it manually
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [July 6, 2018, 11:53am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/8 "2018-07-06T11:53:39Z")
Thank you very much. I found this option and changed it from 90 days to 10 days.
What about user\_actions ? what is stored in this table that it has got so big? I have the same question about post\_timings and topic\_views too. The names indicate that these should be just a bunch of numbers. And shouldn’t take really this much space.
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [July 6, 2018, 2:09pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/9 "2018-07-06T14:09:38Z")
> [@hosna](#):
>
> What about user\_actions ? what is stored in this table that it has got so big? I have the same question about post\_timings and topic\_views too. The names indicate that these should be just a bunch of numbers. And shouldn’t take really this much space.
You can run a backup, download it, and analyze it locally. A pg\_dump is just a text file that is human readable and will let you check what exactly is in each table.
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [July 6, 2018, 5:08pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/10 "2018-07-06T17:08:05Z")
I followed your suggestion and downloaded and extracted the backup. it was about 2GB. Is it normal that its 1/4 of what discourse reports?
Btw I realized that a huge amount of data is for excessive number of inactive users. Its more than 100k. Is there an automatic way for deleting all these users? they don’t have any post or other things that might break the process.
If there is no automatic way, If I remove them with api call, does it also clear all information related to them from database?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [July 6, 2018, 5:15pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/11 "2018-07-06T17:15:45Z")
> [@hosna](#):
>
> I followed your suggestion and downloaded and extracted the backup. it was about 2GB. Is it normal that its 1/4 of what discourse reports?
2GB was the size of the compressed backup? Also backups don’t include indexes, and they take a lot of space.
> [@hosna](#):
>
> Btw I realized that a huge amount of data is for excessive number of inactive users. Its more than 100k. Is there an automatic way for deleting all these users? they don’t have any post or other things that might break the process.
An inactive user without posts or likes is just a single line in the users table. Are you sure all the space is coming from inactive users?
Did your forum came in from an import? Maybe the import created some bad data?
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [July 6, 2018, 8:31pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/12 "2018-07-06T20:31:43Z")
> [@Falco](#):
>
> 2GB was the size of the compressed backup?
No. Its the size of extracted backup. The size of compressed backup is about 300MB
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [July 7, 2018, 1:01am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/13 "2018-07-07T01:01:23Z")
```plaintext
backup.topic_views
```
I looks like you did a restore recently and the data before the restore is backup-ed in the `backup` schema. If you’re certain that you no longer need to recover back to the previous state, you can drop the schema by running the following commands.
```plaintext
./launcher enter app
su - postgres
psql discourse
ALTER SCHEMA "backup" TO "backup-moved";
# Check that you site is still working and up to date
DROP SCHEMA "backup-moved" CASCADE;
```
---
### Author:  [@Paracelsus](https://meta.discourse.org/u/Paracelsus)
#### Post date: [June 1, 2020, 7:48am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/14 "2020-06-01T07:48:55Z")
Thought of reviving this topic, since it’s the same issue.
Our discourse postgres\_data folder is 75GB big. Which I think is a lot. According to the admin panel, a backup is around 10.5GB and the uploads take about 9.3GB.
I’ve checked which tables were taking more space and this is what I get:
```
public.posts | 51 GB
public.post_search_data | 9769 MB
public.post_timings | 3997 MB
public.user_actions | 2144 MB
public.post_custom_fields | 1039 MB
public.topics | 676 MB
public.post_stats | 663 MB
public.post_replies | 643 MB
public.quoted_posts | 523 MB
public.user_visits | 476 MB
public.top_topics | 403 MB
public.user_auth_token_logs | 364 MB
public.topic_links | 353 MB
public.topic_users | 335 MB
public.topic_views | 301 MB
public.user_histories | 220 MB
public.users | 209 MB
public.stylesheet_cache | 194 MB
public.directory_items | 143 MB
public.notifications | 139 MB
```
I wonder if it’s normal to have a public.posts table taking so much space (51GB), we’re talking about a 6M post forum, which I don’t see as something extraordinary.
Is this normal?
---
### Author:  [@Paracelsus](https://meta.discourse.org/u/Paracelsus)
#### Post date: [June 1, 2020, 8:09pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/15 "2020-06-01T20:09:54Z")
Completing the above info with `rake db:stats`:
## table\_name | row\_estimate | table\_size | index\_size | total\_size
posts | 8847417 | 39 GB | 12 GB | 51 GB
post\_search\_data | 5880635 | 8377 MB | 1392 MB | 9769 MB
post\_timings | 23728606 | 1571 MB | 2430 MB | 4001 MB
user\_actions | 5424982 | 488 MB | 1657 MB | 2144 MB
post\_custom\_fields | 5832468 | 429 MB | 609 MB | 1039 MB
I’ve seen other examples where 10 million rows for posts table translates in just about 15Gb. We have now 8 million with 39 Gb in size.
Is there a way to optimize this?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [June 1, 2020, 8:42pm UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/16 "2020-06-01T20:42:39Z")
> [@Paracelsus](#):
>
> I’ve seen other examples where 10 million rows for posts table translates in just about 15Gb. We have now 8 million with 39 Gb in size.
There are **lots** of factors in play here. For example, long posts take more space than short ones. As a fellow _lusófono_ I know how crazy verbose our language can be. I see your data comes from an import. Some artifacts, like posts with 5 level-deep quotes aren’t common in Discourse but are found in your site because of the import. Our language also matters as a `ç` takes double the space of a `s`.
I do believe we don’t change the PostgreSQL default, and the `posts.raw` column goes to TOAST and is compressed.
---
### Author:  [@j127](https://meta.discourse.org/u/j127)
#### Post date: [September 27, 2021, 3:56am UTC](https://meta.discourse.org/t/why-my-discourse-postgres-data-is-too-big/91657/17 "2021-09-27T03:56:55Z")
> [@tgxworld](#):
>
> `ALTER SCHEMA "backup" TO "backup-moved";`
Thanks, that worked. I think that line might be missing `RENAME` though. I did it like this:
```sql
ALTER SCHEMA "backup" RENAME TO "backup-moved";
```