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.

  • Could it be related to different block sizes of the filesystems used on the SSD and the HD? – a_horse_with_no_name Sep 14 at 10:52
  • What are the parameters of your disks / partitions / volumes? (Cluster size, block size, ....) – hot2use Sep 14 at 11:22
  • Are the two disks running the same filesystem type? – dezso Sep 14 at 12:07
  • What tool at the OS level do you use to monitor the free space? Are you sure it responds instantly to reflect the current amount of free space, rather than showing some lazily updated value? – jjanes Sep 14 at 13:08

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.