I am quite new in database so it is possible I missed something. Anyway, I need to execute a test in which I have to simulate a 20years historical data in Postgresql (and TimescaleDB) DB. My problem is that I have to generate .sql files and ingest them (thanks to psql-client) in the targeted DB.
But we made some calculations and the result is that our database will have 261 Billion rows in our table for 20years, so each year contains 13.05B data. For each row, we have a timestamp (Integer type) and I thought, to be more efficient, I write, in my .sql files transactions of 10.000 elements. To make the generated .sql files small in disk spaces (I generate those files in python), I limited each file to 20M rows each.
So I thought that I could generate these files dynamically in a bash file and, when a file is generated, I run a psql command to ingest it into the DB, but the problem is that I don't know how to handle it: the ingest process takes much more time than .sql file generation, so in bash commands, I am afraid my bash script will wait the ingest process before starting to generate a new .sql file and execute the ingest process again...
So to summarize, I try to create an ingest process pseudo-batch based in which each generated .sql file that has been ingested successfully will be removed to avoid to take too much disk space.
But how to avoid the fact it will wait the ingest process before starting an other .sql file generation and then start the ingest process?
Hope it was clear, if you have more questions, do not hesitate to make me know, I will answer asap.