Benchmarking concurrent operations in PostgreSQL

Published 2020-05-28

More:

When preparing the article about concurrency in PostgreSQL (Database transactions, concurrency, isolation levels, and PostgreSQL) I was also working on a benchmark that would give me an overview of how different operations are performing in a concurrent environment, under different load, and under different configuration (transaction isolation level).

I’ve prepared a C++ application that benchmarks a number of different operations in Postgres. It simulates a concurrent environment with different isolation levels. It’s available on my Github account.

Tests

There’s a number of tests to see how SQL operations are affected by increasing concurrency (using the --threads parameter). In this article, I present results obtained by running the application with 100, 200, 400, 800, and 1600 threads at once.

You can see the SQL code executed by tests here.

The following tests were performed:

Observations

Benchmark results for the read committed isolation level Benchmark results for the “read committed” isolation level.

Benchmark results for the repeatable read isolation level Benchmark results for the “repeatable read” isolation level.

Benchmark results for the serializable isolation level Benchmark results for the “serializable” isolation level.

Comparison of operations across transaction isolation levels Comparison of operations across transaction isolation levels.

Benchmark application

It’s written in C++ and uses pqxx library to connect to Postgres. It requires a C++17 compiler (tested with gcc 10). It’s been tested with PostgreSQL 12.3.

It has a number of parameters that change its behavior. Run the application with --help option to see them all.

--threads option tells the program how many threads it should run. By default, the application will open 10 connections to Postgres (it can be changed with --connections) and run threads to simulate the concurrent access to the database. At the end of a run, it prints the timings for each operation.

To gather benchmarks, it’s useful to run it with --repetitions [n] which will run every test n times and compute average timings.

The transaction isolation level used in tests must be changed in the source code (src/tasks.hpp - isolation constant). Available options include: read_committed, repeatable_read, and serializable.

The data gathered for the results presented here is available in an .ods spreadsheet.

To find out how to run the tests on your own machine, see the README file.