Should I exclude database files when running a defrag?

[yossarian974]

After I did housekeeping I went to run a defrag and a good portion of the the postgres8.3\data\base files showed up as fragmented-at least in O & O defrag, fwiw they ddnt in Perfectdisc. I am guessing that the O & O algorythym doesnt reconize the way it is organized. So should I avoid doing a defrag with that software? Would it affect my performance one way or the other?

[WhiteRider]

I don't know for sure, but if the files are defragged then I suspect that you'll need to re-cluster afterwards at least as the defrag may undo some of the work of the last cluster.
I expect that clustering would put the files on the disk in the most efficient places so the defrag wouldn't help (and would probably make it worse).

I think that the best performance will probably be gained by defragmenting the whole disk (including DB files - make sure the postgres service is stopped first) and then re-running full housekeeping. A postgres expert may correct me, though..

[vicnice]

I don't know for sure, but if the files are defragged then I suspect that you'll need to re-cluster afterwards at least as the defrag may undo some of the work of the last cluster.
I expect that clustering would put the files on the disk in the most efficient places so the defrag wouldn't help (and would probably make it worse).

I think that the best performance will probably be gained by defragmenting the whole disk (including DB files - make sure the postgres service is stopped first) and then re-running full housekeeping. A postgres expert may correct me, though..

I would like to know this as well. I have been running Diskeeper 2009 which has a background process that keeps my HD defragged during idle CPU time. If defragging the Postgres folder would make my database slower by interfering with clustering results then I should be exempting that folder.

[yossarian974]

as I said perfect disc doest read them as fragmented, but o and o does, just got perfectdisc btw and think it is a far better product- I think that the cluster algorithm is not reconized by O&O b/c i tried what u recomended before housekeep defrag(&O) housekeep and it took 6 hours(it took 2 hoursthe first time) to cluster and when done it show up as fragmented again. But then I dont really have a deep understanding of either. Bye O&O

[kraada]

As far as I understand it, there are two different levels of fragmentation that we're talking about here.

First is the low level hard drive fragmentation. This is what your defragger is fixing - the physical files on the hard drive are written in multiple different locations (because of how hard drives write files) and the defragger makes it so that on the physical hard drive, the files are located just next to one another.

Then there's the "higher level" PostgreSQL fragmenting. PostgreSQL saves its database data in different files called nodes in your data/base/ directory. I will admit all of the technical details are beyond me, but the main purpose of clustering is to make sure all of the data in a given table is ordered intelligently in as few files as fit the parameters. That way fewer files need to be opened and the most used data is "near the top" so to speak.

You can see that these two functions are similar in theory but not actually related - so you can have a hard drive that needs defragging with a perfectly clustered PostgreSQL install, and vice versa. When you have a defragged HD and CLUSTER runs, the reason your hard drive gets more fragmented is simply the normal way hard drives get fragmented - files are written. When you have a perfectly clustered database, the reason the database gets "unclustered" is that you add (and remove) data from the database.

I hope that clears things up for you guys.