How fast should I expect postgresql to be?

Question

I'm assessing postgresql performance for a time-series application, particularly for inserts. I'm running the speed test script from sqlalchemy at https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow. You can ignore the ORM stuff, it's the penultimate "core" line which shows the performance.

I was expecting to see from 10 to 100k writes per second as per e.g. (https://docs.timescale.com/latest/introduction/timescaledb-vs-postgres):

However, I'm instead getting the following:

So that's 358 writes per second! Compared to 83k writes per second for sqlite.

That seems really slow to me, but I'm getting this performance consistently across a Windows installation, Docker on Windows, native Ubuntu and Docker on Ubuntu.

Are my expectations just misaligned with reality, or am I doing something wrong?

EDIT:

To clarify, I'm not using Timescale DB's hyptertables feature yet, just plain vanilla PostgreSQL tables. I am using the TimescaleDB docker installation for my docker tests (though not the native test). Specifically, I'm invoking

docker run -d --rm -p 5433:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg12

Answer 1

The trick to getting good performance out of PostgreSQL for inserts using SQLAlchemy is, as @LaurenzAlb hinted at, better management of transactions. SQLAlchemy can, but doesn't by default, use psycopg2's executemany helpers for PostgreSQL.

Turning these on transforms performance for multiple inserts into the same table. Here's the modified code for the python test.

    engine = create_engine(DB_NAME, echo=False, executemany_mode='values')

With executemany_mode=None:

SQLAlchemy Core: Total time for 50000 records 95.0814561843872 secs = 525.8648952855459 /s

with executemany_mode="values":

SQLAlchemy Core: Total time for 50000 records 1.761573314666748 secs = 28383.71788656377 /s

See https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-batch-mode for an explanation.