Dynamically generate batched data for import
The actual rate of ingest is going to be based on the number of spindles (physical hard disks) behind the RAID that holds your data files and log files.

A decent RAID setup should grant you the ability to achieve ~1 M rows/sec or higher.

Your algorithm is another major bottle neck.

Method 1

You can skip the create file step by making Python connect to the database directly.

I don't know if there is a Timescale DB driver for Python.

For speed:


Use BIND variables
Cache your Statement Handle
If the driver supports it, use BIND Arrays.


Make sure the "number of rows per BIND Array" and "number of groups between COMMIT" are variables in your program.  You'll want to play with these to find the sweet spot for your system.  That is: You will need to run benchmarks to find the best values for your setup.

The insert_vendor_list in this example uses the BIND Array technique.

Method 2

Sometimes, you want to review the data before it is generated.  In that case, you want to create a .csv file, not an .sql file full of INSERT statements.  Each of those INSERT statements will need to be hard parsed.

The RDBMS databases I have played with come with specialized applications that can ingest a CSV at (or near) the maximum rate (as defined by your RAID setup).

Notes on Speed Performance

Depending on what you are really testing, you should disable/remove all indexes and constraints on the target table prior to ingesting the data.  Then, create them afterwards.