1

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?

2
  • 1
    There's a kind of a check list on this blog post, maybe you can run through that and if you have questions after update with what you tried and if you saw any effect? blog.timescale.com/blog/…
    – greenweeds
    Commented Sep 10, 2021 at 8:23
  • 1
    If the bottleneck is on the client side, no amount of screwing around on the server is going to remove it.
    – jjanes
    Commented Sep 10, 2021 at 15:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.