Re: UPDATE

c k wrote:

> CREATE TABLE accgroups
> (
> accgroupid serial NOT NULL,
> accgroupidname character varying(150) NOT NULL DEFAULT ''::character
> varying,
> accgroupname character varying,
> createdby integer DEFAULT 0,
> createdtimestamp timestamp without time zone DEFAULT
> ('now'::text)::timestamp without time zone,
> locked smallint,
> lastmodifiedby integer DEFAULT 0,
> lastmodifiedtimestamp timestamp without time zone,
> remark character varying(255) DEFAULT NULL::character varying,
> cobranchid integer DEFAULT 0,
> .
> .
> .
> .
> againstid integer DEFAULT 0,
> )
> WITH (OIDS=FALSE);

That "..." might be rather important. How big ARE these records?

Remember, PostgreSQL uses a MVCC approach to transactional isolation.
Unless Pg can take short-cuts (as it sometimes can when, for example,
there is only one active transaction) it must write NEW COPIES of each
record. It can't just update the existing ones.

Effectively, Pg must do an INSERT of the updated record then a DELETE of
the old one. All fields, not just the updated field, must be read and
written.

Some other databases use a locking approach instead. They can just write
the new values in place. The I/O required is dramatically reduced. On
the other hand, other concurrent transactions can't do anything while
you're working with those records, you're more prone to deadlock
situations, achieving decent concurrency is much more difficult, etc. If
you're using something horrifying like MyISAM tables where there IS no
rollback, there's no need to maintain a rollback log or anything like
that. Even with it the I/O is still much less than what Pg requires.

AFAIK if you run the UPDATE while there are no other concurrent
transactions, Pg will write the new values in-place. It still updates
the WAL first, but it won't create whole new copies of each record as
well. That's the case at least if the field you're updating isn't
involved in any indexes; I think doing this relies on HOT, and won't
work if the column being altered is involved in one or more indexes.

--
Craig Ringer