On Sat, Jan 23, 2016 at 12:13 PM, Jinhua Luo <luajit...@gmail.com> wrote: > > Hi All, > > Here is my test environment: > > postgresql 9.4.5, Ubuntu 14.04, 8 CPU core, 8GB ram, SCSI hard disk > > I have a table with 70 columns, and 6 indexes. The data flow is a > special OLTP model: frequent inserts (2000 tps), and each inserted row > would be updated very soon (i.e. the number of inserts is equal to the > number of updates). > > > At last, I found it's not the problem of autovacuum. > I do a simple test: I truncate the table, disable the autovacuum, and > run the application for a few minutes, then I invokes vacuum manually, > it gives a strange output: > found 598 removable, 25662 nonremovable row versions in 3476 pages > DETAIL: 0 dead row versions cannot be removed yet > > As said before, the number of inserts is equal to the number of > updates. So the bloat of the table should be 100%, and the number of > removable rows should be equal to the number of nonremovable rows, > which is the real number of inserts issued by the application. > > But the result shows that the number of nonremovable rows is just a > small fraction. If it's true, then no wonder that the table would keep > extending for a long time, because the free space is almost small. > > Why the free space after vacuum is not 50% of the original size in my case? >
Vacuum just removes the deleted rows (provided they are not visible to any active transaction), it won't reduce the size which is already extended, unless the empty space is at end of relation. Are you updating any index column? I think if you should once try with higher fill factor as you have lot of updates. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
