1

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): PostgreSQL and Timescale comparison

However, I'm instead getting the following:

Performance test results

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
1
  • Both machines are running a local SSD
    – CharlieB
    Jul 13 '20 at 16:26
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.