ERROR: canceling statement due to conflict with recovery
Error Details:

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

Business again

1. system USES postgresql main library database architecture, main library used in WeChat small program mainly used in the business of writing data from library with the method of flow to copy from the main library update for library data, for the application of the library service is the background of query statistics web applications when the query statistics in the background, the SQL query is slower, table is updated data, and the query used to query for a long time will be following the above error </ ol>

1. Question why

1. Executive time on the main library query process, due to this query involves records may be updated, or deleted in the main library, according to the PostgreSQL MVCC mechanism, update, or delete data is not immediately deleted from the physical block, but after autovacuum process VACUUM data of the old version, the main library to update, or delete data on the old version, after VACUUM from the library will also carry out the operation, thus conflict with from the current query library, cause the query to interrupt and throw more mistakes.

1. The solution

1. On a business level

1. From the business can optimize the slow SQL, whether the data table is too large, whether need to separate the database table, frequently update the frequent table analysis and vacuum and so on. Or segmenting query, avoid peak period data update time, etc

1. From the database level

1. Modify the maxSTANDBY_ STREAMING_DELAY parameter

1. This parameter for 30 seconds, by default when executing SQL for library, may have clashed with is application of a WAL, this query if there is no execution is suspended for 30 seconds, 30 seconds note not for library individual queries allow maximum execution time, refers to allow maximum when case library application on a WAL WAL delay time, so for library query execution time may be less than the value of this setting has been suspended, this parameter can be set to 1, said from the library when WAL application process and executed queries from the library, a WAL application process waits until from the database query execution. (When I was processing, I set a larger value of 120, although occasionally an error will be reported, but the frequency of error will be much less, it is suggested that this exception can be caught in background processing, and the query user will be reminded that the query is currently busy, please try again later)

1. Modify the hotstandby_feedback parameter

1. By default when performing a query from the library will not notify the main library, set this parameter to on after from library to execute queries will notify the main library, when from library query execution process, the main library won’t clean up the data from library need old version, therefore, the query from the library will not be suspended, however, this method also can bring certain disadvantages, table may appear on the main library expansion, the expansion of the main library table level and on the table write transactions and execution time from the library, this parameter is off by default. (This approach is risky, I haven’t tested it)

After modifying the parameter configuration, you need to reload the database configuration file