"By default, VACUUM skips the sort phase for any table where more than 95 percent of the table's rows are already sorted" Is there a reason why the default is 95 and not 100?
Is the performance increase of a 100% to 95% sorted table negligible?
Asked
Active
Viewed 413 times
0
John Rotenstein
- 241,921
- 22
- 380
- 470
pippa dupree
- 155
- 1
- 10
-
When i know i have no real time constraints i always vacuum to 100 percent. the performance difference is dependent upon your use cases. why not run some benchmarks to discover the impact for your situation? – Jon Scott Dec 22 '18 at 11:27
-
@JonScott Thanks. Ya, I am curious of the performance benefits and will try some tests – pippa dupree Dec 22 '18 at 23:40
1 Answers
2
VACUUM is a very intensive operation. The performance benefits of having a 95% vs 100% sorted table is minimal. Therefore, it is saving a lot of wasted effort in the VACUUM operation.
The VACUUM documentation says:
By default, VACUUM skips the sort phase for any table where more than 95 percent of the table's rows are already sorted. Skipping the sort phase can significantly improve VACUUM performance. To change the default sort or delete threshold for a single table, include the table name and the TO threshold PERCENT parameter when you run VACUUM.
Therefore, you can always force a 100% sort if desired.
John Rotenstein
- 241,921
- 22
- 380
- 470