SQLAlchemy Core bulk insert slow
<p>I'm trying to truncate a table and insert only ~3000 rows of data using SQLAlchemy, and it's very slow (~10 minutes). </p>

<p>I followed the recommendations on this <a href="http://docs.sqlalchemy.org/en/latest/faq/performance.html" rel="nofollow">doc</a> and leveraged sqlalchemy core to do my inserts, but it's still running very very slow. What are possible culprits for me to look at? Database is a postgres RDS instance. Thanks!</p>

<pre><code>engine = sa.create_engine(db_string, **kwargs, pool_recycle=3600)
with engine.begin() as conn:
            conn.execute("TRUNCATE my_table")
            conn.execute(
                MyTable.__table__.insert(),
                data #where data is a list of dicts
            )
</code></pre>

<p>I was bummed when I saw this didn't have an answer... I ran into the exact same problem the other day: Trying to bulk-insert about millions of rows to a Postgres RDS Instance using CORE. It was taking <em>hours</em>.</p>

<p>As a workaround, I ended up writing my own bulk-insert script that generated the raw sql itself:</p>

<pre><code>bulk_insert_str = []
for entry in entry_list:
    val_str = "('{}', '{}', ...)".format(entry["column1"], entry["column2"], ...)
    bulk_insert_str.append(val_str)

engine.execute(
    """
    INSERT INTO my_table (column1, column2 ...)
    VALUES {}
    """.format(",".join(bulk_insert_str))
)
</code></pre>

<p>While ugly, this gave me the performance we needed (~500,000 rows/minute)</p>

<p>Did you find a CORE-based solution? If not, hope this helps!</p>

<p>UPDATE: Ended up moving my old script into a spare EC2 instance that we weren't using which actually fixed the slow performance issue. Not sure what your setup is, but apparently there's a network overhead in communicating with RDS from an external (non-AWS) connection.</p>

python postgresql orm sqlalchemy