GCP SQL Postgres 11 : Spike in storage size due to indexes

Viewed 587

We have a cloud sql postgres installation with 16vcpu . Suddenly , there is an increase in storage size related to indexes. The table size is 20gb and 4 indexes in it are consuming 70gb+ as per pgadmin stats. Table has a bulk delete and insert ops in transactions.

Is there any special flags that needs to be added ?

PS: I heard about WAL retention settings but not sure whether it will have any impact and I cannot actually ssh to the machine as it a GCP SQL.

[Update 1]: After deleting table/indexes, re-created:

Table T1 Stats :

Sequential scans    911504
Sequential tuples read  7338381399896
Index scans 173853066
Index tuples fetched    98226419759
Tuples inserted 109307632
Tuples updated  804
Tuples deleted  100410620
Tuples HOT updated  0
Live tuples 8797115
Dead tuples 24503371
Heap blocks read    1829681
Heap blocks hit 287184313804
Index blocks read   11094719
Index blocks hit    4239641531
Toast blocks read   0
Toast blocks hit    0
Toast index blocks read 0
Toast index blocks hit  0
Last vacuum 
Last autovacuum 2020-03-31 18:48:45.626151+00
Last analyze    2020-03-31 14:17:20.834182+00
Last autoanalyze    2020-03-31 18:48:53.828135+00
Vacuum counter  0
Autovacuum counter  24
Analyze counter 3
Autoanalyze counter 53
Table size  5726 MB
Toast table size    8192
Indexes size    21 GB

T1 pg_stat_user_indexes

indexrelname    idx_scan    idx_tup_read    idx_tup_fetch   Size    
index1          0           0                0              4608 kB
index2          21          103,913,145      0              3417 MB
index3          2,786       1,110,430        135,322        4007 MB
index4          949,981     1,284,602        794,130        4020 MB
index5          7,549,112   1,043,077,414    1,043,060,187  1860 MB
index6          165,334,371 13,962,773,344   12,209,134,553 1692 MB

Table Struct - (Total 14 Col)

Col1    character varying   
Col2    character varying   
Col3    character varying   
Col4    character varying   
Col5    character varying   
Col6    character varying   
Col7    character varying   
Col8    timestamp without time zone 
Col9    timestamp without time zone 

Index Definition - index1 and index4 are unique index

index1  Col1    Col2    Col3    Col4    Col5    Col6    Col7    Col8    Col9
where Col8 is null 
index2  Col5    Col6                    
index3  Col1    Col2    Col3    Col5    Col6        
index4  Col1    Col2    Col3    Col4    Col5    Col6    Col7    Col8    Col9
where Col8 is not null
index5  Col4    Col5                    
index6  Col1    Col2    Col3    Col4    Col5

One strange thing I noticed while extracting stats is pg_stat_user_indexes have two additional indexes7&8 which are not visible under schemaname->tablename> indexes

1 Answers

Thanks for the comments . Really useful in troubleshooting the issue in google cloud. The main problem was bloating due to frequent deletes/inserts and fine tuning indexes.

Setting below cloud sql flags helped in resolving the problem : autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor,autovacuum_max_workers,maintenance_work_mem

Pgtune was helpful in finding the optimum values.

Related