I’m trying to use pandas to interact with timescaleDB. I initiate my DB in DBeaver with the following
CREATE TABLE db_009a005a_df_downloaded_grand (
symbol VARCHAR(32),
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
volume DOUBLE PRECISION,
close DOUBLE PRECISION,
high DOUBLE PRECISION,
low DOUBLE PRECISION,
open DOUBLE PRECISION
);
SELECT create_hypertable('db_009a005a_df_downloaded_grand', 'timestamp');
CREATE INDEX idx_symbol ON "db_009a005a_df_downloaded_grand" (symbol);
My data looks like this in python
df_downloaded_grand
| | symbol | timestamp | volume | close | high | low | open |
|---:|:------------|:--------------------|---------:|--------:|-------:|-------:|-------:|
| 0 | ALPACA_USDT | 2021-08-14 22:45:00 | 426.541 | 1.0868 | 1.0882 | 1.0859 | 1.0868 |
| 1 | ALPACA_USDT | 2021-08-18 11:55:00 | 3303.5 | 1.3875 | 1.4008 | 1.3713 | 1.3807 |
| 2 | ALPACA_USDT | 2021-08-16 18:00:00 | 73.953 | 1.134 | 1.1348 | 1.1322 | 1.1322 |
| 3 | ALPACA_USDT | 2021-08-18 21:45:00 | 117.792 | 1.3869 | 1.3956 | 1.3869 | 1.3875 |
| 4 | ALPACA_USDT | 2021-08-18 08:45:00 | 8709.14 | 1.4665 | 1.4832 | 1.46 | 1.4832 |
| 5 | ALPACA_USDT | 2021-08-17 00:45:00 | 280.158 | 1.1262 | 1.1305 | 1.1262 | 1.1288 |
| 6 | ALPACA_USDT | 2021-08-15 08:15:00 | 651.886 | 1.0857 | 1.1024 | 1.0857 | 1.0912 |
| 7 | ALPACA_USDT | 2021-08-15 15:40:00 | 108.985 | 1.104 | 1.1099 | 1.1036 | 1.1099 |
| 8 | ALPACA_USDT | 2021-08-16 06:50:00 | 952.071 | 1.2048 | 1.2048 | 1.1949 | 1.1949 |
| 9 | ALPACA_USDT | 2021-08-14 14:50:00 | 131.221 | 1.0976 | 1.0987 | 1.0945 | 1.098 |
... 67145 rows × 7 columns
I’m trying to insert it with pandas
from sqlalchemy import create_engine
engine = create_engine(f'postgresql+psycopg2://{config.DB_USER}:{config.DB_PASS}@{config.DB_HOST}:{config.DB_PORT}/{config.DB_NAME}')
df_downloaded_grand.to_sql(target_table_name, engine, if_exists="append",index=False)
On my dedicated AWS ubuntu instance with 32GB RAM and 8 cores, it took 4s to insert 60K testing result…. I’m actually targetting inserting 200M rows into the DB … Did I do anything wrong? Is it possible to be faster?
