postgresql – timescaleDB insertion speed issue

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?