Thread by @RustProofLabs

Gave the #psycopg3 connection pool to #PostgreSQL an initial test run...

Spoiler: Loving it! Thank you @psycopg!!

A thread. 1/?

https://twitter.com/psycopg/status/1370408830283223040?s=20

Created a Flask app to use pgbench tables/queries for easy setup/scaling.

Currently 2 routes: account balance and update balance. Update intentionally uses 1 transaction/query to amplify the effect of many short connections (not a real bank app!)

github.com/rustprooflabs/…

2/?

db.py was pulled/updated from another project to use psycopg3. Seems to be drop-in replacement! 👍

db_pool.py is reworked version to use conn pool instead of creating/destroying connections per query. The docs were helpful: psycopg.org/psycopg3/docs/…

3/?

Repo has a JMeter test file (.jmx) used to load the app.

The following results are from 200 concurrent threads, 25 loops. Runs ~8 minutes

Setup for tests: pgbench -i -s 100 bench_test

4/?

Reminder: This is a Twitter thread.

What I'm doing here is a gut check for performance, not benchmarking. Don't get hung up on minor nuances or inconsistencies (they are there)

4.5/?

JMeter and Flask app are running on my laptop.

Postgres v12 running on a #RaspberryPi 4 (4GB RAM) w/ data dir on SSD via USB3.

App --> Pg is over local WiFi

5/?

NO connection pool: Response time trend shows the account balance route hovers around the 80ms range (median 73ms). Update route bounces around the 400-500ms range (median 398ms).

Not bad for Postgres on RPi over WiFi...

6/?

With connection pool: Response time trend shows account balance route hovers around 10-20ms (median 13ms) and the update route bounces around the 40-60ms range (median 56ms).

84% and 86% faster median times when using psycopg3 pool! 😍

Note: Local ping to the Pi is 2-4 ms

7/?

Charts with Average, Median, 95% and 99% times reported by JMeter.

Using in-app connection pool removes the need for the app to connect to the DB at query time.

Turns out, this makes every query from the app MUCH faster even under low load.

8/?

The connection pool in #psycopg3 is making the user experience from the app faster and more predictable.

How does #PostgreSQL react to this traffic?

9/?

First, no pool. htop screenshot captured as the JMeter load started to wind down. Note the top right corner shows it ran for 7min 20s with 171/200 threads still active.

1-min load at 5.55. Ouch. The Pi is struggling.

10/?

Still no pool, a couple charts from pgbadger.

The # of sessions/second chart shows a peak at 86 sessions/second. The sessions time chart shows nearly 35k short lived sessions (<500ms) over this ~8 min test.

11/?

Now w/ pool. htop from 7 minutes in, 182 threads remaining (JMeter winding down).

1-min load at 0.56. The Pi isn't breaking a sweat on this!

Noticeably less memory consumption too.

12/?

Again w/ pool, pgbadger charts. Sessions/second peaked at 9 (vs 86).

Session times show 4 long-lived sessions (aligns with the pool min_size) and a small handful (single digit numbers!) of shorter lived sessions.

35,000 sessions vs 15 sessions

13/15

Why pool in-app instead of pgbouncer or other? In-app means the app doesn't have to negotiate connections all the time! It isn't just about protecting Postgres, it's about improving app performance too.

Also, this was super easy to setup/configure w/ #psycopg3

14/15

I plan to work in more queries/routes to the project. Particularly looking at multiple pools, e.g. one for fast and one for slow (reporting) queries.

The reporting queries would have lower max_size and higher timeout than the fast pool.

15/15