Postgresql – Why does pg_basebackup use so much less resources than pg_dump

postgresql

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?

Best Answer

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.

Related Question