On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote:

> Does PostgreSQL lock the entire row in a table if I update only 1
> column?

Know that updating 1 column is actually updating the whole row. So if
one transaction updates column A of a row, it will block another
concurrent transaction that tries to update column B of the same row.
As was mentioned however, neither of these transactions block others
reading the row in question, though they see the row as it existed
before the updates until those update transactions commit.

If you know that your application will suffer excessive update
contention trying to update different columns of the same row, you
could consider splitting the columns into separate tables. This is an
optimization to favor write contention over read performance (since
you would likely need to join the tables when selecting) and I
wouldn't do it speculatively. I'd only do it if profiling the
application demonstrated significantly better performance with two
tables.

-Casey

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Casey Duncan wrote:
> On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote:
>
>> Does PostgreSQL lock the entire row in a table if I update only 1
>> column?
>
> Know that updating 1 column is actually updating the whole row. So if
> one transaction updates column A of a row, it will block another
> concurrent transaction that tries to update column B of the same row.
> As was mentioned however, neither of these transactions block others
> reading the row in question, though they see the row as it existed
> before the updates until those update transactions commit.
>
> If you know that your application will suffer excessive update
> contention trying to update different columns of the same row, you
> could consider splitting the columns into separate tables. This is an
> optimization to favor write contention over read performance (since
> you would likely need to join the tables when selecting) and I
> wouldn't do it speculatively. I'd only do it if profiling the
> application demonstrated significantly better performance with two
> tables.
>
> -Casey
Or, come up with some kind of (pre)caching strategy for your updates
wherein you could then combine multiple updates to the same row into one
update.

--
erik jones <erik@myemma.com>
software development
emma(r)


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate