Re: Partition tables in AWS RDS environment

Greetings Lance,

* Campbell, Lance (lance(at)illinois(dot)edu) wrote:
> In 9.6 if you do a pg_dump on a schema and you have a few very large tables in the schema will this cause the database to lock the table for writing?

In PG, readers do not block writers.

> This is an issue I ran into with older version of PostgreSQL using pg_dump on entire schemas. To get around this issue I partitioned the really large tables so that the locking would be a much shorter time period while running pg_dump. But this may not be necessary with 9.6

I'm not sure what issue you ran into, but pg_dump would block someone
from DROP'ing or TRUNACTE'ing a table (or other operations requiring a
very strong lock), not from simply doing an INSERT, UPDATE, or DELETE
(which take a lower lock that doesn't conflict with the locks pg_dump
takes).

Information about the various locks in PG is available here:

https://www.postgresql.org/docs/current/static/explicit-locking.html

Thanks!

Stephen