Postgre DB is big (20+GB) - How can I understand what's stored there?

[AmbroiseUnly]

Postgresql contain 21 GB of data on my Cloudron server that only has a single app: Baserow

The baserow database itself only contains around a hundred records, nothing huge.
So, I wonder why the postgresql on that server is so huge. Can I login to it and navigate the PG DB directly? Or is there another simpler/smarter way to understand what's being stored there?

$ du -h --max-depth=1
21G	./postgresql
84K	./addons
197M	./mysql
20K	./oidc
518M	./mongodb
136K	./nginx
4.0K	./tls
16K	./logrotate.d
4.0K	./cifs
12K	./backup
4.0K	./acme
4.0K	./sshfs
236K	./update
8.0K	./diskusage
24K	./sftp
524K	./redis
207M	./logs
4.0K	./firewall
12K	./collectd
48M	./graphite
22G	.

[joseph]

I guess some app is storing a lot in postgres. With some help from ChatGPT:

docker exec -it postgresql /bin/bash
psql -Uroot  --dbname=postgres

Then, run this query in postgresql prompt:

SELECT pg_database.datname AS database_name,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

This gives a bunch of daabase names like dbf88f51a1c41249a6947427dedb491dfd. Take the first 6 characters like f88f51a1 and put in the search field in the dashboard. This will reveal the app.

[humptydumpty]

@joseph I think he knows which app it’s related to (baserow) but doesn’t understand why it’s so big given he has 100 rows of data only.

[humptydumpty]

@AmbroiseUnly I don’t use baserow but I’ve ran into something similar with other apps and it was the log files. Check the upstream docs and in-app to locate them. Report this upstream too.

[joseph]

Reading fail on my part, sorry. @AmbroiseUnly so the next step is to determine which table in baserow is taking much space. Could be logs as @humptydumpty mentioned. You can access the db from Web Terminal and click the postgres button on nav bar

[AmbroiseUnly]

Okay! I thought about the logs but the folder ./logs mislead me, as I thought it couldn't be that because it was so small. Didn't think there would be both logs in the filesystem and in the db. I'll try to have a look into the db then.

[AmbroiseUnly]

So, db7fdba71d461b44c481e0b8aa8ec62681 is taking 21GB

So it'd be db7fdb, where is the "Dashboard" you mentioned? I didn't understand that part

[joseph]

@AmbroiseUnly look into which table of db7fdba71d461b44c481e0b8aa8ec62681 is taking space .

[AmbroiseUnly]

I ran

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

And got

So it seems what's taking space are 2 items:

• History of audit, which is useless to me because I'm not "enterprise" (and don't plan on switching plan)
• History of rows, which is definitely useful but I need to be careful because I'm storing data related to crypto and value of each crypto, so changes happen every couple minute on each row

[AmbroiseUnly]

My env.sh

# Add Baserow customizations here (https://baserow.io/docs/installation/configuration)

export BASEROW_BACKEND_LOG_LEVEL=INFO

# Changed default because I don't have Enterprise plan and don't have a need for audit log
# But I'm interested in retention history for a bit longer
# See https://forum.cloudron.io/topic/13193/postgre-db-is-big-20-gb-how-can-i-understand-what-s-stored-there/9?_=1738227461250
export BASEROW_ROW_HISTORY_RETENTION_DAYS=365
export BASEROW_ENTERPRISE_AUDIT_LOG_RETENTION_DAYS=30

[AmbroiseUnly]

Yes, you can set BASEROW_ENTERPRISE_AUDIT_LOG_RETENTION_DAYS to control how long audit logs are kept. Setting this to 30 days would automatically prune logs older than a month.
Actions (which power the undo/redo functionality) can also take up significant space. Consider setting BASEROW_JOB_CLEANUP_INTERVAL_MINUTES to run cleanup more frequently.
You can also adjust BASEROW_ROW_HISTORY_RETENTION_DAYS to limit how long row history is kept.

I am not familiar with Cloudron but make sure you are also removing old docker images as they also often take up space

and if you are on a Debian based OS and you do updates often the occasional sudo apt auto-remove won’t hurt…
I suspect you have the bulk of the data in the table you found but these are a couple more things you can check that I often see people forget as well

From my discussion with the Cloudron team

[girish]

Thanks, I have put this info in https://docs.cloudron.io/apps/baserow/#log-retention