PostgreSQL的磁盘空间占用问题

　　3Somelessknownparameters

　　3.1random_page_cost

　　Thisparametersetsthecosttofetcharandomtuplefromthedatabase,whichinfluencestheplanner'schoiceofindexvs.tablescan.Thisissettoahighvalueasthedefaultdefaultbasedontheexpectationofslowdiskaccess.IfyouhavereasonablyfastdiskslikeSCSIorRAID,youcanlowerthecostto2.Youneedtoexperimenttofindoutwhatworksbestforyoursetupbyrunningavarietyofqueriesandcomparingexecutiontimes.　

　　3.2Vacuum_mem

　　ThisparametersetsthememoryallocatedtoVacuum.Normally,vacuumisadiskintensiveprocess,butraisingthisparameterwillspeeditupbyallowingPostgreSQLtocopylargerblocksintomemory.Justdon'tsetitsohighittakessignificantmemoryawayfromnormaldatabaseoperation.Thingsbetween16-32MBshouldbegoodenoughformostsetups.　

　　3.3max_fsm_pages

　　PostgreSQLrecordsfreespaceineachofitsdatapages.Thisinformationisusefulforvacuumtofindouthowmanyandwhichpagestolookforwhenitfreesupthespace.　

　　Ifyouhaveadatabasethatdoeslotsofupdatesanddeletes,thatisgoingtogeneratedeadtuples,duetoPostgreSQL'sMVCCsystem.Thespaceoccupiedbydeadtuplescanbefreedwithvacuum,unlessthereismorewastedspacethaniscoveredbytheFreeSpaceMap,inwhichcasethemuchlessconvenient"vacuumfull"isrequired.ByexpandingtheFSMtocoverallofthosedeadtuples,youmightneveragainneedtorunvacuumfullexceptonholidays.　

　　Thebestwaytosetmax_fsm_pagesisinteractive;First,figureoutthevacuum(regular)frequencyofyourdatabasebasedonwriteactivity;next,runthedatabaseundernormalproductionload,andrun"vacuumverboseanalyze"insteadofvacuum,savingtheoutputtoafile;finally,calculatethemaximumtotalnumberofpagesreclaimedbetweenvacuumsbasedontheoutput,andusethat.