0

Having 3 files with one data set of daily data inside:

2019-09-11.txt
2019-09-12.txt
2019-09-13.txt

Having lines like:

2019-09-11 15:59:37.459802,1
2019-09-11 15:59:38.959802,1
[...]

The files are sorted by a timestamp, which is the main index in the hypertable:

CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data INT

So it is a lot of data... Is there a way to reach a higher insert performance rate by asyncing the INSERT INTO commands to one insert process per input file/date?

Improve this question
2
  • Have you checked out the COPY command? postgresql.org/docs/current/sql-copy.html or github.com/timescale/timescaledb-parallel-copy ?
    – Feike Steenbergen
    Commented Sep 18, 2019 at 11:22
  • Thanks @FeikeSteenbergen. The COPY command is a more static approach. I am searching for a more general approach, which can be used also as endpoint of an automation chain. Like: Input one date of data --> Process/Modify/Enrich it --> Push it db. And than there will be one parallel process per day. And within this process I would like to put one timescale-db output at the end of each per-day-chain. Therefore, I am searching for the fastest and most performant way to split the data for the automation chains and keep timeorder in place.
    – gies0r
    Commented Sep 18, 2019 at 11:29

1 Answer 1

Reset to default
1

If the files are comma separated or can be converted into CVS, then use Timescale tool to insert data from CVS file in parallel: timescaledb-parallel-copy

A manual approach to insert data into hypertable can be to create several sessions of PostgreSQL, e.g., by executing psql my_database in several command prompts and insert data from different files into the same hyperatble. It is important that parallel loading will insert data into different chunks. For example, if hypertable is created with default chunks size, one chunk will fit one week of data. Thus different parallel sessions should read data from files belonging to different weeks. Otherwise, parallel execution will not perform well due to conflicts. Also you want to fit active chunks into memory.

In both cases it is good that the data are already sorted by timestamp as mentioned in the question.

Improve this answer
1
  • That is pretty much what I expected @k_rus. Thanks for confirmation.
    – gies0r
    Commented Sep 18, 2019 at 12:56

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.