0

Python seems to slow to insert rows into database. I am using timescaleDB and executemany for insert. For testing, I use simply table:

time, seq, symbol, tick

for testing

The sample data can be:

2019-02-05 07:44:59.326+00  1   VGM9 Index  Test

So I try:

CREATE TABLE ticks(
 time               TIMESTAMPTZ       NOT NULL,
 seq int NOT NULL,
 symbol             VARCHAR(20)       NOT NULL,
 tick               VARCHAR(20),
 PRIMARY KEY (time, seq, symbol)
); 

insert_query = "INSERT INTO ticks(time, seq, symbol, tick) VALUES (%s, %s, %s, %s);"

self._cursor.executemany(insert_query, values_list)

It takes me around 17-18s to insert 100,000 rows into local DB

Is there a better way to do this?

Charles Landau
  • 4,187
  • 1
  • 8
  • 24
alan
  • 67
  • 2
  • 8
  • Can you try something like "insert into ticks (time, seq, symbol, tick) select * from unnest(array[1, 2, 3, 4], array[1, 2, 3, 4]);" – Harshit Agarwal Feb 07 '19 at 12:16
  • Python just send your query to the database, so if it's slow the issue is not with Python itself, but with either your database adapter, transaction handling, and of course network (if using a dedicated database server) and database setup . FWIW since timescaleDB is a PGSql extension, you might be interested in this: https://www.postgresql.org/message-id/CA+mi_8Zeeg93nHDXiZo0U16y-ygYN+FSCxD34nsmkTjkZqqfZA@mail.gmail.com and this (possible duplicate): https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query – bruno desthuilliers Feb 07 '19 at 12:35

0 Answers0