Using Postgresql version 15.1 I'm making a table of blockchain transactions (1 row per transaction). In total, it will have ~2 billion rows. Once the table reached ~50 million rows, the data ingestion seemed to slow down a bit so I would anticipate a continued degradation as rows increase. Currently, I'm adding rows to the table in batches of ~15k and am closing the cursor and connection after each upload. To add the data to the table I'm using Python (psycopg2 lib), a CSV file holding the data, and the SQL "COPY" command.
Will partitions speed up data ingestion if the data that I'm adding to the table mainly touches 1 partition?
The blockchain contains blocks with a block # that increments over time as blocks are added. Transactions are "within" blocks and therefore have a block # associated with them. My plan was to create partitions by block # so that each partition will contain ~15 million rows. In theory, I think this will speed up data ingestion because each 15k chunk I'm adding to the table is ordered sequentially by block #.
Any feedback on the main question or the general strategy would be appreciated.
Also, the table is only accessed by me and I won't be deleting any old data as new data is added.