Обсуждение: Huge pg_toast table
I have a frequently updated table called 'audit' with 10,000 rows of about 90kb per row (90kb * 10k = 900mb). There is a 35gb system table called pg_toast_NNNNN where NNNNN is the oid of the 'audit' table. My question: should I expect a 'vacuum full' to shrink the size of this pg_toast table? I ask instead of just doing it because doing so would require scheduling customer downtime since 'vacuum full' blocks access. TIA.
On Thursday July 24 2003 2:53, Ed L. wrote: > I have a frequently updated table called 'audit' with 10,000 rows of > about 90kb per row (90kb * 10k = 900mb). There is a 35gb system table > called pg_toast_NNNNN where NNNNN is the oid of the 'audit' table. > > My question: should I expect a 'vacuum full' to shrink the size of this > pg_toast table? I ask instead of just doing it because doing so would > require scheduling customer downtime since 'vacuum full' blocks access. BTW, we currently run 'analyze' every 2 hours and vacuum once daily on this 7.3.2 cluster.... TIA.
"Ed L." <pgsql@bluepolka.net> writes: > On Thursday July 24 2003 2:53, Ed L. wrote: > > I have a frequently updated table called 'audit' with 10,000 rows of > > about 90kb per row (90kb * 10k = 900mb). There is a 35gb system table > > called pg_toast_NNNNN where NNNNN is the oid of the 'audit' table. > > > > My question: should I expect a 'vacuum full' to shrink the size of this > > pg_toast table? I ask instead of just doing it because doing so would > > require scheduling customer downtime since 'vacuum full' blocks access. > > BTW, we currently run 'analyze' every 2 hours and vacuum once daily on this > 7.3.2 cluster.... You might want to check your FSM (free space map) settings--it sounds like you're losing track of some pages that (non-blocking) vacuum could salvage, saving you from having to VACUUM FULL. There's been a fair amount of discussion about FSM tuning if you check the archives... -Doug