Batch Inserts are slower in one of the transaction table in azure postgresql flexible server

Question

Explain_Analyze_from_Equivalent_Environment_For_Problematic_insert.txt
Hi team, 

we have table which is of total size(inc indexes) is 173GB and of total records of around (132530734). We have 28 indexes in that table. And these are the specifics to the table 

    FILLFACTOR = 85, 

    autovacuum_enabled = TRUE, 

    autovacuum_analyze_scale_factor = 0.002, 

    autovacuum_vacuum_cost_delay = 0, 

    autovacuum_vacuum_scale_factor = 0, 

    autovacuum_vacuum_threshold = 100000 

 

No Partitioning is done on Transaction table 

 

We have a module which process which uses 5000 records per batch for every 15s. In entire stored procedure, Insert into this table is alone taking more than 60s. I have attached explain analyze plan for insert query. Entire selction of data is taking only 273ms but insert into main table is taking 60s. Is there any suggestions for improvising batch insert timing in big tables. 

 

Note: Work_mem is set as 40MB in procedure level. 

 

We have tried below mentioned ways 

1. insert data into temp table and then insert the data from temp table to main table ( This is not increasing the timing. Temp table data insertion is taking 1s but insert into main table is taking more than 56s) 
 

Kindly suggest if any other ways to improve batch insert timing in big tables

Thanks and regards,

Manimahesh S

Answer 1

@Mani Mahesh S Thank you for reaching out.

My understanding is that you are having issues with the insert performance of the large table with 28 indexes.

Could you please try the below assuming this is an incremental load.

1. Use Bulk Inserts: you might need to experiment different values for work_mem to find the optimal setting for your specific batch insert process, and you might need to parallel this process by dividing the batch into smaller sub-batches and inserting them concurrently using multiple threads or processes.
2. Make sure the table is not bloated and do regularly analyze and vacuum your table, depending on the nature of the transactions of this table.
3. Review the indexes, 28 indexes are a high number for single table, how many columns we have here? and you can review and remove the unusable ones if exist.
4. triggers/constraints can impact the insert performance.
5. Think of Partitioning.

 And I recommend reviewing this Doc https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-bulk-load-data#best-practices-for-incremental-data-loads

Regards,

Oury