=?utf-8?B?VGhvcnN0ZW4gU2Now7ZuaW5n?=<tschoening@xxxxxxxxxx> writes: > for various reasons I've migrated my database schema from 4 tables > with some additional indexes to keep integrity and stuff to 1 table > only. That made some of the former used indexes obsolete and resulted > in overall less consumed storage: > The old schema consumed ~42 GiB, while the new is ~16 GiB without the > formerly available indexes and ~25 GiB with the same logical indexes. > Though, a created dump of the new schema has increased from ~5,52 GiB > to 6,38 GiB. Of course I'm using the same settings to create both > dumps: >> pg_dump.exe "--username=%USERNAME%" "--encoding=UTF-8" "--compress=9" "--format=c" "--dbname=%DB_NAME%" > "%DMP_PATH%" > My expectation was that the dump would be smaller as well, because the > data itself is the same, while lots of duplicate IDs, obsolete indexes > etc. in not available tables anymore have been removed. Removing indexes won't in itself make any noticeable difference in the size of pg_dump output, since an index is just represented by a CREATE INDEX (or equivalent) command. My guess is that the rearrangement somehow made the table data less amenable to compression. gzip depends on finding similar substrings within a fairly narrow window (a few KB), so at least in principle, just changing the order of rows could make a difference. I'd sort of expect compression opportunity losses to more or less balance out with opportunity gains over such a large data volume, but maybe you were unlucky. Or perhaps the move into a single table was done in such a way that it actually decreased locality-of-reference, eg maybe similar rows were grouped before and now they're not. regards, tom lane
