Search code examples
postgresqlperformanceautocommit

PostgreSQL Autocommit efficiency on SELECT queries

I am using Psycopg2 to issue many SELECT queries to my Postgres DB. These queries have a small size result and they are sent at a high frequency.

In order to avoid managing transactions myself (because I'm lazy), I have set autocommit = True in the session settings.

Edit: there is no long pause between queries, no data manipulation, and the cursor gets closed immediately after I stop making queries.

I would like to know: When issuing SELECT queries at a high frequency, is there any performance penalty of using autocommit?

Using Postgres 9.6 and Psycopg 2.7.7. And yes, I am using prepared statements.

Solution

  • Autocommit is the right thing because:

    • There is no performance penalty because a read-only transaction does not have to write to the transaction log (WAL).

    • No locks are held, like a_horse_with_no_name commented. Even a SELECT holds a n ACCESS SHARE lock on the table, which will block commands like TRUNCATE, DROP TABLE and vacuum truncation.

    • The driver doesn't have to send those extra BEGIN and COMMIT statements, which will cause unnecessary client-server round trips.