Open Stack

On 10 July 2013 11:00, Jay Pipes <jaypipes at gmail.com> wrote:


> >   I am not up to speed on"InnoDB's gap locking behavior" but it is
>
>> not something I would expect to be a problem in Postgresql.
>>
>
> InnoDB and PostgreSQL behave in very different manners regarding locking
> and transaction isolation, even though they both implement a version of
> MVCC. In InnoDB's case, its implementation of MVCC is optimized more for
> storage space (it allows using a series of log records to reconstruct or
> undo a record to a particular "version" of the record) vs. PostgreSQL,
> which stores every version of every record in its data space.
>

PostgreSQL only keeps enough versions live to ensure the oldest transaction
can still read all the versions that were live at it's start: once the
transaction is closed any obsolete version kept alive by it can be gc'd or
overwritten. I think the distinction you are drawing is that InnoDB only
stores deltas between record versions - an interesting tweak. I guess that
TOAST mitigates the temporary storage overhead: as a particular TOAST value
is immutable, there's no need to copy that data when updating a different
value in a row. That won't help with 'update one int in each of a billion
rows' case though.


> AFAIK, PostgreSQL won't issue gap locks like InnoDB because it will simply
> write a new version of the records -- a version that marks the record as
> deleted -- to its data files, instead of gap locking to ensure that rows
> affected by a DELETE statement with an open-ended (or non-existent) WHERE
> clause can be properly isolated (and rolled back properly in the case of a
> failure).
>

PostgreSQL doesn't do gap locks, but instead you have to deal with
http://wiki.postgresql.org/wiki/SSI : the transaction that is deleting 1M
rows, for instance, will have a query that may return rows which another
transaction is adding; if so one of the two will be rolled back. This is in
many ways equivalent from the point of view of writing good SQL that will
work well on both systems.


> In any case, MySQL is certainly a production-capable database like
> PostgreSQL. It has its quirks and downsides, as does any system, including
> PostgreSQL. Biases and false assumptions should be set aside. ;)
>
> Best,
> -jay
>
>
> ______________________________**_________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.**org <OpenStack-dev at lists.openstack.org>
> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>
>



-- 
Robert Collins <rbtcollins at hp.com>
Distinguished Technologist
HP Cloud Services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130710/e04f61ff/attachment.html>