2

When I run pg_dump the main application starts to lag out and take 1 to 2 seconds for even the simplest queries on primary keys of small tables. When I run pg_basebackup it's unnoticable. Why is this?

I know there are many questions on stackexchange about why pg_dump is slow, that's not what I'm asking; I'm asking what pg_basebackup does differently to make it unnoticable. And ideally is there any way to make pg_dump run "in the same way" so that I can use it without messing up the application?

1
  • What parameters are your using when you execute the commands?
    – John K. N.
    Commented Apr 28, 2020 at 7:27

2 Answers 2

1

pg_dump doesn't use more I/O that pg_basebackup. If anything, then fewer, since it doesn't have to read indexes. What could be is that you have a lot of TOASTed overlength values, and reading from the TOAST table causes random and maybe more I/O.

Typically, what is more used by pg_dump is CPU, because it has to parse all the table data and format them for output. So I'd guess that CPU is the resource which is scarce on your system.

As a first measure, you should identify the bottleneck, and then optimize your workload. If you have reached the limit there, consider adding more of the scarce resource.

Another, quite different cause for what you observe would be if your application misguidedly takes ACCESS EXCLUSIVE locks on tables. Such locks would be blocked until pg_dump is done and are a bad idea for other reasons as well (autovacuum). Check pg_locks for entries where granted is FALSE.

2
  • Isn't it also because pg_basebackup simply reads complete files directly (without "SQL overhead) while pg_dump reads "rows" through SQL statements?
    – user1822
    Commented Apr 28, 2020 at 21:26
  • @a_horse_with_no_name Sure, but that is CPU overhead. Concerning I/O, SQL statements read data in 8kB blocks. Commented Apr 29, 2020 at 4:22
0

pg_dump runs inside a long-lived transaction, which inhibits the clean up of obsolete tuples; even the clean up done as part of HOT-update pruning. pg_basebackup does not inhibit this type of clean up.

If your workload is exquisitely sensitive to HOT-update cleaning, then using pg_dump will make is slower.

And ideally is there any way to make pg_dump run "in the same way" so that I can use it without messing up the application?

Take a pg_basebackup, restore it to a "dummy" task-specific server, and do a pg_dump from that.

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.