I have a large (several TBytes) Postgres database (v10 on windows) containing tables that are partitioned by date-range. Depending on volumes, some of them are partitioned by day, others by month.
I have two main tablespaces 'fastspace' on an SSD drive, and 'slowspace' on a traditional magnetic hard disk.
As data becomes older (and less relevant) I move the partitions from fastspace to slowspace using
ALTER TABLE..... SET TABLESPACE slowspace
I've started checking available disk space (at an OS level) before and after changing the tablespace, and have been surprised to notice that the destination drive (slowspace) is losing a lot more free space than the source drive (fastspace) is gaining.
A typical table might contain a 100 million rows of data for a month occupying several Gbytes of space. Changing the tablespace might free up x Gbytes of space from fastspace, whilst the destination tablespace slowspace will lose 2x Gbytes of space. (I'm moving both the table, and the indexes at the same time)
Why the difference, and what can be done to improve the situation?
Changing the tablespace is a relatively quick process (time measured in minutes) but doing another VACUUM after moving the partition to slowspace is very slow (time measured in hours). This is doubly frustrating because I do my best to ensure that performance intensive operations (like a VACUUM) are done on my fastest disk. To move the data to slower disk, and then be immediately forced to start a very slow operation to regain lost space is very annoying!.
Update in response to comments... Not sure it's a block size issue. Both my discs have 512 bytes/sector, and the postgres page size is (I think) set per database, not per tablespace)
Both disks are NTFS. According to system information/components/Storage/Disks the 'fastspace' disk looks like this....
Description Disk drive
Manufacturer (Standard disk drives)
Model BIWIN SSD
Bytes/Sector 512
Media Loaded Yes
Media Type Fixed hard disk
Partitions 4
SCSI Bus 0
SCSI Logical Unit 0
SCSI Port 0
SCSI Target ID 0
Sectors/Track 63
Size 238.47 GB (256,052,966,400 bytes)
Total Cylinders 31,130
Total Sectors 500,103,450
Total Tracks 7,938,150
Tracks/Cylinder 255
Partition Disk #0, Partition #0
Partition Size 260.00 MB (272,629,760 bytes)
Partition Starting Offset 1,048,576 bytes
Partition Disk #0, Partition #1
Partition Size 236.67 GB (254,119,553,536 bytes)
Partition Starting Offset 407,896,064 bytes
Partition Disk #0, Partition #2
Partition Size 960.00 MB (1,006,632,960 bytes)
Partition Starting Offset 254,528,192,512 bytes
Partition Disk #0, Partition #3
Partition Size 500.00 MB (524,288,000 bytes)
Partition Starting Offset 255,535,874,048 bytes
And the 'slowspace' disk like this.....
Description Disk drive
Manufacturer (Standard disk drives)
Model TOSHIBA MQ01ABD100
Bytes/Sector 512
Media Loaded Yes
Media Type Fixed hard disk
Partitions 1
SCSI Bus 2
SCSI Logical Unit 0
SCSI Port 0
SCSI Target ID 0
Sectors/Track 63
Size 931.51 GB (1,000,202,273,280 bytes)
Total Cylinders 121,601
Total Sectors 1,953,520,065
Total Tracks 31,008,255
Tracks/Cylinder 255
Partition Disk #1, Partition #0
Partition Size 931.39 GB (1,000,068,874,240 bytes)
Partition Starting Offset 135,266,304 bytes
I'm using a c# app to monitor disk space before/after the move using DriveInfo.AvailableFreeSpace immediately before and after using the ALTER TABLE ... SET TABLESPACE command.
