AWS RDS Postgres performance problem by MOVE ALL IN "query-cursor_1" automatic sql instruction

Question

This is a FastAPI project using SQLAlchemy and an AWS RDS postgres database. The waits (AAS) on database suddenly increase above 2 (for a 2 vCPU - t4g.small instance), and when checking on Performace Insights, I see a large load on the CPU from the sql instruction MOVE ALL IN "query-cursor_1", with an average latency above 200 seconds. This is happening a couple of times a day, for about five minutes each time. During these periods, database connections don't increase, and the increase in database load is not outside of the normal range.

Waits during suddently CPU load increment

CPU utilization and DB connections

I'm not executing the MOVE ALL IN "query-cursor_1" instruction, and I have not been able to determine where this instruction comes from.

I'm not using cursors, so I find the execution of this instruction very strange. I do not have a clear understanding of the use of cursors, but I know that their use can lead to performance problems and table blocking. However, as I mentioned above, I am not directly executing any cursor-related instructions.

I have been changing some indexes to improve large data queries performance, but my database is relatively small with 30 tables and max half million rows in a couple of tables.

I did not find references about this query for RDS postgres databases, neither references from SQLAlchemy related to that.

I appreciate your help!

Answer 1

The source of these queries is the Performance Insights feature, according to AWS Support. Performance Insights run batches of 50 up to the setting of pg_stat_statements.max, which default setting is 5000.

There are a couple of things to reduce the impact of the Performance Insights feature:

1. Reduce the pg_stat_statements.max parameter to a value betweeen 1000 and 3000 (instance reboot required).
2. Increase the instance RAM.

Also, deactivating the Performance Insights feature could be a solution.

Answer 2

This statement is caused by your application or by some Amazon application. It does not come from the database itself.

MOVE ALL IN <cursor> moves the cursor to the end of the result set. That effectively executes the complete query behind the cursor. If that query takes 200 seconds to complete, so will MOVE ALL.

There is one extra thing to consider: if you define a cursor, PostgreSQL will optimize statements differently. Rather than optimizing for total execution time of the complete statement, PostgreSQL will optimize for the execution time of fetching the first 10% of the rows. This is usually the right thing to do, but it is not ideal if you execute the complete query at once, like MOVE ALL does. Try to change the configuration parameter cursor_tuple_fraction from its default value of 0.1 to 1.0. Perhaps that will improve the performance for you.