Postgresql – changing tablespace seems to waste space

performancepostgresqlpostgresql-performancetablespaces

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.

Best Answer

The comment about file system block size may be relevant. Please note that it is about file system blocks, not hard disk blocks. This is normally referred as a cluster, in NTFS terminology. You may check it with command fsutil fsinfo ntfsinfo [your drive] on a cmd opened as Administrator.

You might also check the storage parameter, like fillfactor. I think (althought I am not sure) that moving a table to a new tablespace will not just copy tha table blocks, but will reorder them. If you copy a table where you never issued update or delete statements, the table on the new tablespace will be as the original one, but if you updated/deleted records, the the table on the new tablespace will be "compacted" and will fill all new blocks only until the fillfactor, leaving some space for future updates on every block. If the fillfactor is too large, this is a waste of disk space.

Related Question