PostgreSQL performance test script

In case you want to run a quick performance test (in simple terms of access to DB) on a PostgreSQL server, this can be done quickly through a python module called psycopg2.

This is the python script I’ve used:

 1#!/usr/bin/python3
 2import psycopg2
 3from psycopg2 import Error
 4import time
 5
 6def create_connection():
 7    """ create a database connection to a PostgreSQL database """
 8    try:
 9        conn = psycopg2.connect(
10            database="postgres",
11            user="postgres",
12            password="postgres",
13            host="1.2.3.4",
14            port="5432"
15        )
16        print('Successfully Connected to PostgreSQL')
17        return conn
18    except Error as e:
19        print(e)
20
21def check_table_exists(conn):
22    """ check if a table exists in the PostgreSQL database """
23    cur = conn.cursor()
24    cur.execute(f"SELECT 1;")
25
26def main():
27#    table_name = 'pg_auth_members'
28
29    # create a database connection
30    conn = create_connection()
31
32    # check if table exists
33    with conn:
34        start_time = time.time()
35        checks = 0
36        while True:
37            check_table_exists(conn)
38            checks += 1
39            elapsed_time = time.time() - start_time
40            if elapsed_time > 0:
41                tps = checks / elapsed_time
42                print(f'Current TPS: {tps}')
43                print (f'Current checks: {checks}')
44
45if __name__ == '__main__':
46    main()

In here you should update the database, user, password, host and port to something relevant to your environment. Everything else can stay the same.

But in order to run it, you will need to have python installed, pip3 package manager and psycopg2_binary python library.

In my case I already had python installed, I had to install python3-pip from a remote yum repository. I also had the pyscopg2 locally downloaded, but in most cases this could be installed as per the official documentation.

1[user@hostname ~]$ sudo yum install -y python3-pip
2...
3[user@hostname ~]$ sudo python3 -m pip install psycopg2_binary-2.8.6-cp36-cp36m-manylinux1_x86_64.whl
4WARNING: Running pip install with root privileges is generally not a good idea. Try `__main__.py install --user` instead.
5Processing ./psycopg2_binary-2.8.6-cp36-cp36m-manylinux1_x86_64.whl
6Installing collected packages: psycopg2-binary
7Successfully installed psycopg2-binary-2.8.6
8[user@hostname ~]$

Then all you have to do is to start the test script:

 1[user@hostname ~]$ python3 db-test.py
 2...
 3Current checks: 2356
 4Current TPS: 4067.3281247042887
 5Current checks: 2357
 6Current TPS: 4067.341871726699
 7Current checks: 2358
 8Current TPS: 4067.4275044859687
 9Current checks: 2359
10...

As you can see, here is only a snippet, but the script is pretty powerfull, it runs continuously until stopped, and it gets to +4000 TPS. Don’t forget to stop the script otherwise you may end up DOS-ing your own DB server.