3

I'm trying to find information on whether it's safe to run ANALYZE on a large table that has stale statistics. The table is accessed in an "append-only" fashion and is constantly written to by numerous writers, so I'm concerned whether analyzing the table can block those writers for the duration of ANALYZE.

The PG documentation states:

ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.

What exactly is this "other activity" that can run in parallel if a read lock is acquired? Only other reads?

I then tried looking at the PG documentation to find what exactly a "read lock" is, and the docs for table locks don't event mention a "read lock" existing, but use some completely different terminology like "ACCESS SHARE", "ROW SHARE", etc.

Additionally, a few independent articles I found all mention that it's safe to use because it doesn't lock, and then proceed to quote the docs where it's clearly stated that it does lock.

I'm sure I'm missing something here. Does this "read lock" only prevent definition changes to the table while it's being analyzed (like a schema lock)?

2 Answers 2

2

The answer, as is often the case, can be found in the source code for the VACUUM command:

     * Determine the type of lock we want --- hard exclusive lock for a FULL
     * vacuum, but just ShareUpdateExclusiveLock for concurrent vacuum. Either
     * way, we can be sure that no other backend is vacuuming the same table.

and similarly for the ANALYZE command:

     * Open the relation, getting ShareUpdateExclusiveLock to ensure that two
     * ANALYZEs don't run on it concurrently. 

We can then find in the documentation this fragment:

SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs.

Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands).

0
0

Also need to be aware that running Analyze on a table will invalidate "blow the plan cache" for plans involving that table. Causing new sessions to compile/regenerate new plans involving that table. For a DB with small amount of sessions - don't matter. Thousands of sessions - a bigger deal - a CPU hit for a bit until plans involving that table are regenerated in plan cache. Always good to run analyze during low usage time.

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.