How fast should I expect postgresql to be?
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=&quot;values&quot;:
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.