0

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
  • Are you using the Timescale extension? But even for vanilla Postgres 358 transactions/second seems quite slow. How exactly are you running those inserts? Alternatively, SQLite might simply simply be lying and not doing a fsync? – a_horse_with_no_name Jul 13 at 12:15
  • I intend to, but I'm not yet (or rather I am, but the test I'm running just makes normal tables instead of timescaledb Hypertables) – CharlieB Jul 13 at 12:59
  • 1
    That is either lame storage (lame disk network attached over WAN), or every INSERT is running in its own transaction. – Laurenz Albe Jul 13 at 16:07
  • @LaurenzAlbe Both machines are running a local SSD, so I don't think it's the former. You might well be right about the latter though: I'll look into it more – CharlieB Jul 13 at 16:26
  • 1
    Hi @CharlieB my recent post on "13 tips for improving PG/TimescaleDB performance" might help: blog.timescale.com/blog/… My guess is what earlier posters said -- you are writing one row per INSERT/transaction (and through all the ORM overhead at that). And on the ORM side, you should check if you are properly doing connection pooling, or if you are also opening a new connection per second. And any parallelism? If not, you are measuring latency, not throughput. The above experiment had both significant batching and parallelism. – Mike Freedman Jul 17 at 1:48
0

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.

| improve this answer | |

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.