Skip site navigation (1) Skip section navigation (2)

WAL bypass for INSERT, UPDATE and DELETE?

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: bizgres-general <bizgres-general(at)pgfoundry(dot)org>,pgsql-hackers(at)postgresql(dot)org
Subject: WAL bypass for INSERT, UPDATE and DELETE?
Date: 2005-12-22 14:31:33
Message-ID: 1135261893.2964.502.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackers
Having just optimized COPY to avoid writing WAL during the transaction
in which a table was first created, it seems worth considering whether
this should occur for INSERT, UPDATE and DELETE also.

It is fairly common to do data transformation using INSERT SELECTs and
UPDATEs. This is usually done with temporary tables however. (DELETE
would most efficiently be handled as an additional NOT clause on the
insert, so it is uncommonly used in this circumstance.)

However, CREATE TABLE AS SELECT (CTAS) does not allow inheritance, so a
new permanent partition has to be created using CREATE TABLE, followed
by an INSERT SELECT or COPY.

Is that sufficient reason to optimise INSERT SELECT and UPDATE also? Or
should I not bother? Or should I try to teach CTAS to use inheritance
(which sounds harder and has a few gotchas).

Currently, CTAS optimization requires a heap_sync during ExecEndPlan. It
would be easy enough to extend this so that it also works for INSERT,
UPDATE and DELETE.

Best Regards, Simon Riggs


Responses

pgsql-hackers by date

Next:From: Volkan YAZICIDate: 2005-12-22 14:54:53
Subject: Re: problem with nasty latin2 sorting
Previous:From: Euler Taveira de OliveiraDate: 2005-12-22 13:59:35
Subject: Re: to_char and i18n

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group