CODEWITHSUNDEEP
postgresqlconcurrencylocking
ssb
ssb

Reputation: 1482

How does postgresql lock tables when inserting and selecting?

I'm migrating data from one table to another in an environment where any long locks or downtime is not acceptable, in total about 80000 rows. Essentially the query boils down to this simple case:

INSERT INTO table_2
SELECT * FROM table_1
JOIN table_3 on table_1.id = table_3.id

All 3 tables are being read from and could have an insert at any time. I want to just run the query above, but I'm not sure how the locking works and whether the tables will be totally inaccessible during the operation. My understanding tells me that only the affected rows (newly inserted) will be locked. Table 1 is just being selected, so no harm, and concurrent inserts are safe so table 2 should be freely accessible.

Is this understanding correct, and can I run this query in a production environment without fear? If it's not safe, what is the standard way to accomplish this?

Upvotes: 17

Views: 25768

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21385

You're fine.

If you're interested in the details, you can read up on multiversion concurrency control, or on the details of the Postgres MVCC implementation, or how its various locking modes interact, but the implications for your case are nicely summarised in the docs:

reading never blocks writing and writing never blocks reading

In short, every record stored in the database has some version number attached to it, and every query knows which versions to consider and which to ignore.

This means that an INSERT can safely write to a table without locking it, as any concurrent queries will simply ignore the new rows until the inserting transaction decides to commit.

Upvotes: 27

Related Questions