Database seems too big


(Jay Pfaffman) #1

I’ve got a site with 1.6M posts and 25K users. The database dump is about 8.7GB, which takes up 22GB of space in Postgres. I generally scoff at people whining about deleting this or that to save space in the database, but 22GB for 1.6M posts seems like a lot.

I upgraded to PG 10, dumped and restored the database, thinking that might help, but it didn’t. I think that this may have started on RDS before I moved it to Digital Ocean, if that provides some clue.

Does this seem normal? Is there some place I could look to see if there’s something bizarre in there?


(Rafael dos Santos Silva) #2

You can run the queries here to find if there is something strange: Disk Usage - PostgreSQL wiki

First thing to check is if there is another dangling schema from a backup (backup and restore schemas).


(Jay Pfaffman) #3

Thanks, @falco! It looks like there are backup and restore table_schemas. (And thanks to @supermathie and @omarfilip for editing TWO ridiculously glaring typos! I often wish there were a way to :heart: an edit.)

Should I just

drop schema backup;
drop schema restore;

and I guess the pg_catalog and information_schema are not problems.

Does having those schemas in the database affect performance, or do they just waste disk space (which I usually don’t care that much about).

(And then I guess should also rm -rf postgres_data_old, but that’s another issue.)


(Michael Brown) #4

Naturally, before you do ANYTHING take a backup :smiley:

Is there data in those schemas? They’re used in backup_restore.rb to be able to atomically switch to a restore (the restore schema) or rollback a restore (the backup schema).

They should be safe to drop.


(Jay Pfaffman) #5

I think that’s what I thought, but if I drop them, one will come back when a backup happens and the other when there’s a restore? Sounds a bit feckless to fuss with them. But with them, the database takes basically 3X the footprint, it seems.