11

What is the additional cost incurred by inserting large volume or rows (millions), in many tables in a single transaction ?

Can something be done (tuning parameters) so that the cost of inserting in large volume in a single transaction approaches the cost of doing it in autocommit ?

3 Answers 3

9

I'm not sure what your limit is or what your concern is but millions of rows is not a problem. Billions of rows isn't a really a problem either. The larger the transaction the better for performance. Transactions have overhead.

Here on my old x230 I

  1. create a table with a million rows.
  2. add a million rows.
  3. add a BILLION rows. Damn. That's a lot of rowzzz.

Here is the code and results.

test=# CREATE TABLE foo AS SELECT id::bigint FROM generate_series(1,1e6) AS gs(id);
SELECT 1000000
Time: 722.075 ms
test=# INSERT INTO foo SELECT id FROM generate_series(1,1e6) AS gs(id);
INSERT 0 1000000
Time: 1285.631 ms
test=# INSERT INTO foo SELECT id FROM generate_series(1,1e9) AS gs(id);
INSERT 0 1000000000
Time: 2142933.903 ms

So you can see, you can do a million rows in a second or a billion in 35 minutes.

If you're asking why the bigger batch was slower, I think that's the overhead of WAL which would eventually show to be even greater if I did them in smaller batches (I think).

The maximum transaction size is like 2-4 billion, but just to not be excessive I would cut it off at a 2 billion rows per transaction.

5
  • 1
    Yeah the cost of checkpoints over time add up. A million rows might fit in before a checkpoint gets kicked off etc. Sep 7, 2017 at 21:08
  • 1
    So, I was correct then, there is a size beyond which performance declines
    – Gaius
    Sep 8, 2017 at 6:57
  • @Gaius no, you weren't correct. I hit a checkpoint: "yeah the cost of checkpoints over time add up. A million rows might fit in before a checkpoint gets kicked off etc." Sep 8, 2017 at 6:59
  • Your own experiment shows "the bigger batch was slower"
    – Gaius
    Sep 8, 2017 at 7:00
  • 1
    It's more like performance averages out. You won't go faster with more smaller transactions. If you're loading data over a long haul, like hours of inserts, then the difference between inserting 1,000,000 rows 1,000 times and inserting 1,000 rows 1,000,000 times will be negligable. Sep 8, 2017 at 18:58
4

You have it backwards - it is generally better to do many rows in a single transaction than one at a time with autocommit. The reasons are a) disk I/O and b) network round trips between client and server. You will need to run benchmarks to find the ideal batch size for your data and your hardware - try 100, 1000, 10000 sized transactions and see. At some point it will peak and past that the transactions will be "too big" as you bump into some other limit.

9
  • 2
    "At some point it will peak and past that the transactions will be "too big" as you bump into some other limit." I'm not sure about that. I can't think of any such limit that would indicate that this statement is true in PostgreSQL. Sep 7, 2017 at 16:19
  • You mean postgres will not for example run out of memory and start swapping when handling a transaction processing too much data? That is interesting as idea - sadly it goes against basics of physics. There is always a limit where things are peaking. For example, it may take too long to send the data for the transaction - it may simply be more efficient to send smaller batches.
    – TomTom
    Sep 7, 2017 at 16:43
  • Yep that is indeed what I meant - the OP hasn't provided any details about the actual size in bytes nor their hardware or postgresql.conf. So it will need to be determined experimentally
    – Gaius
    Sep 7, 2017 at 16:47
  • PostgreSQL, afaik, materializes the rows on disk. At the end of the transaction those rows are made live or left dead. It doesn't hold all of the rows in memory. And, why would it? So essentially if you commit one at a time, they're placed on the table and made active right away. If you commit them all at once, they're placed on the table and made active later (or left dead when you rollback). @TomTom Sep 7, 2017 at 17:05
  • 3
    PG is writing things to the disks, not holding them in memory. Only a toy database would try to hold a whole transaction in memory. Sep 7, 2017 at 21:10
3

Just for the internet as some years has past since this question raised:

I just did an actual migration project to using PG12 as the target. The DB was sufficiently large to draw some conclusions:

The entire database was:

  • ~300 mio rows
  • ~300 tables
  • ~30 mio rows / table max
  • ~22 GB SQL text
  • ~30 Gb final size on disk.

It was committed as one big transaction, using "COPY stdin". It was executed ~25 minutes on my laptop. The insert speed was pretty stable over the time. It seems that it had more dependency on the type of data rather than time passed/transaction size, commit was instant. (I'll try to make a graph to be more concrete)

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.