dyrset
asked on
PostgreSQL disk fragmentation causes performance problems on Windows
I am using PostgreSQL to log data in my application. A number of rows are added periodically, but there are no updates or deletes. There are several applications that log to different databases.
This causes terrible disk fragmentation which again causes performance degradation when retrieving data from the databases. The table files are getting more than 50000 fragments over time (max size abount 1 GB).
The problem seems to be that PostgreSQL grows the database with only the room it need for the new data each time it is added. Because several applications are adding data to different databases, the additions are never contiguous.
I think that preallocating lumps of a given size, say 4 MB, would remove this problem. The max number of fragments on a 1 GB file would then be 250, which is no problem. Is this possible to configure in PostgreSQL? If not, how difficult is it to implement in the database?
This causes terrible disk fragmentation which again causes performance degradation when retrieving data from the databases. The table files are getting more than 50000 fragments over time (max size abount 1 GB).
The problem seems to be that PostgreSQL grows the database with only the room it need for the new data each time it is added. Because several applications are adding data to different databases, the additions are never contiguous.
I think that preallocating lumps of a given size, say 4 MB, would remove this problem. The max number of fragments on a 1 GB file would then be 250, which is no problem. Is this possible to configure in PostgreSQL? If not, how difficult is it to implement in the database?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And to rectify what you have now:
vacuum full analyze verbose
and use contig from sysinternals to defragment compacted files.
vacuum full analyze verbose
and use contig from sysinternals to defragment compacted files.
ASKER
When the disk fills up, I assume that fragmentation will be a problem even for other file systems than NTFS (Windows).
Adding an extra computer is also not desirable even if the hardware is cheap, as this will add extra costs to the maintenance of the system.
Using SSD drives is clearly a possibility, though an expensive one.
So to sum up this topic then; it is NOT possible to preallocate chunks of data for PostgreSQL tables.
This is a simple solution that would solve this issue, and would be of great use to others as well. How hard would it be to implement this in PostgreSQL?
Adding an extra computer is also not desirable even if the hardware is cheap, as this will add extra costs to the maintenance of the system.
Using SSD drives is clearly a possibility, though an expensive one.
So to sum up this topic then; it is NOT possible to preallocate chunks of data for PostgreSQL tables.
This is a simple solution that would solve this issue, and would be of great use to others as well. How hard would it be to implement this in PostgreSQL?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
However, the number of logging applications can sometimes be quite high (up to 100 or more). Then it is impractical to create a physical partition for each of the databases.
Does this mean that it is not possible to preallocate file space for tables in PostgreSQL?