Re: how to slow down parts of Pg - Mailing list pgsql-general

On 4/21/20 2:32 PM, Virendra Kumar wrote:
> Autovacuum does takes care of dead tuples and return space to table's 
> allocated size and can be re-used by fresh incoming rows or any updates.
> 
> Index bloat is still not being taken care of by autovacuum process. You 
> should use pg_repack to do index rebuild. Keep in mind that pg_repack 
> requires double the space of indexes, since there will be two indexes 
> existing during rebuild processes.

You sure about that? On Postgres 12:

--2020-04-21 15:47:27.452 PDT-0DEBUG:  plant1: vac: 5154 (threshold 
1081), anl: 5154 (threshold 565)
--2020-04-21 15:47:27.452 PDT-0DEBUG:  autovac_balance_cost(pid=18701 
db=25092, rel=26497, dobalance=yes cost_limit=200, cost_limit_base=200, 
cost_delay=2)
--2020-04-21 15:47:27.452 PDT-0DEBUG:  CommitTransaction(1) name: 
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG:  StartTransaction(1) name: 
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG:  vacuuming "public.plant1"
--2020-04-21 15:47:27.504 PDT-0DEBUG:  scanned index "p_no_pkey" to 
remove 5114 row versions
--2020-04-21 15:47:27.504 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.00 s
--2020-04-21 15:47:27.514 PDT-0DEBUG:  scanned index "common_idx" to 
remove 5114 row versions
--2020-04-21 15:47:27.514 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.01 s
--2020-04-21 15:47:27.515 PDT-0DEBUG:  scanned index "genus_idx" to 
remove 5114 row versions
--2020-04-21 15:47:27.515 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG:  "plant1": removed 5114 row 
versions in 121 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  CPU: user: 0.00 s, system: 0.00 
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG:  index "p_no_pkey" now contains 
5154 row versions in 31 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  5114 index row versions were 
removed.
         0 index pages have been deleted, 0 are currently reusable.
         CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG:  index "common_idx" now contains 
5154 row versions in 60 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  5114 index row versions were 
removed.
         0 index pages have been deleted, 0 are currently reusable.
         CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG:  index "genus_idx" now contains 
5154 row versions in 47 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL:  5114 index row versions were 
removed.
         0 index pages have been deleted, 0 are currently reusable.
         CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.518 PDT-0DEBUG:  "plant1": found 5154 removable, 
5154 nonremovable row versions in 195 out of 195 pages
--2020-04-21 15:47:27.518 PDT-0DETAIL:  0 dead row versions cannot be 
removed yet, oldest xmin: 9715
         There were 256 unused item identifiers.
         Skipped 0 pages due to buffer pins, 0 frozen pages.
         0 pages are entirely empty.
         CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s.
--2020-04-21 15:47:27.518 PDT-0LOG:  automatic vacuum of table 
"production.public.plant1": index scans: 1
         pages: 0 removed, 195 remain, 0 skipped due to pins, 0 skipped 
frozen
         tuples: 5154 removed, 5154 remain, 0 are dead but not yet 
removable, oldest xmin: 9715
         buffer usage: 753 hits, 0 misses, 255 dirtied
         avg read rate: 0.000 MB/s, avg write rate: 30.586 MB/s
         system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s



> 
> Regards,
> Virendra Kumar
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com