1

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.

Improve this question
4
  • Could work. It might be index maintenance on indexes with sizes exceeding the usable cache that slows you down, but I would expect that slow down to be more than "a bit". What other indexes are present besides blockno? And what are their types?
    – jjanes
    Mar 11 at 0:40
  • @jjanes If my understanding of an index is correct, the only other index would be a p-key for transaction UID on the partitioned tables.
    – itgav
    Mar 11 at 14:45
  • Are the UIDs in order, like with a sequence, or are they random, like with a random UUID?
    – jjanes
    Mar 11 at 16:09
  • @jjanes they're random
    – itgav
    Mar 11 at 17:49

1 Answer 1

Reset to default
0

Partitioning might help you here, by keeping the partition's UID index smaller. Maintaining the index on the random UID will involve jumping to some random leaf page of the index and dirtying it. The performance of doing this will greatly suffer once the index no longer fits in memory. But by ingesting into only one partition at a time, this means it is the size of the index on that hot partition which matters, not the total size over all partitions.

Improve this answer
1
  • great, thank you!
    – itgav
    Mar 13 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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