Hi,
I have a PostgreSQL database with 50 tables.
Every minute, sequentially, a batch load 10.000 rows of 250 bytes with a COPY.
After a day, i got a database with 50 tables with 1.440 set of 10.000 rows.
The tables are cleany and naturally clustered by the inserted timestamp.
Each table has data in a file with 1.440 fragments (each day)
Now, there is a partition rotation script, that suppress old tables when some size limit happens.
Let suppose, that this script runs and suppress only one table qith few days of data, then recreates a new empty one.
I got a disk freespace very fragmented, the space used by the rotated table.
Then some COPY inserts new data, the tables got new data in theirs files and continue to be fragmented.
The new tables begins to grows from the begining of the free space to and is more fragmented that ever.
But all the data are always clustered in the tables.
After few more rotated tables, all the tables are heavily fragmented and even if the data is clustered inside the file
fragmentsare spread all over the drive.
After few days, I see IO wait grows and grows, even when the size of the database stabilises due to the rotation
script.
I suspect the heavy fragmented files to the cause of the IO wait grows (PostgreSQL on WIndows).
How to cope with that ?
It seems I can not pregrow file in PostgreSQL.
I found a trick: if i created an empty table and i insert dummy data, then i insert good data, then i suppress dummy
data,then i vacuum the table (but not a full vacuum) i got a large file with freespace at the begining of the file. If
allthe files were created with that tricks and larger than the maximum data COPYed, i will have no fragmented files.
JG
