Thread: Re: [PATCHES] COPY with no WAL, in certain circumstances
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Saturday 06 January 2007 16:36, Simon Riggs wrote:
> <snip>
>> BEGIN;
>> CREATE TABLE foo...
>> INSERT INTO foo --uses WAL
>> COPY foo.. --no WAL
>> INSERT INTO foo --uses WAL
>> COPY foo.. --no WAL
>> INSERT INTO foo --uses WAL
>> COPY foo... --no WAL
>> COMMIT;
> Is there some technical reason that the INSERT statements need to use WAL in
> these scenarios?
First, there's enough other overhead to an INSERT that you'd not save
much percentagewise. Second, not using WAL doesn't come for free: the
cost is having to fsync the whole table afterwards. So it really only
makes sense for commands that one can expect are writing pretty much
all of the table. I could easily see it being a net loss for individual
INSERTs.
regards, tom lane
> > Is there some technical reason that the INSERT statements need to use WAL in
> > these scenarios?
>
> First, there's enough other overhead to an INSERT that you'd not save
> much percentagewise. Second, not using WAL doesn't come for free: the
> cost is having to fsync the whole table afterwards. So it really only
> makes sense for commands that one can expect are writing pretty much
> all of the table. I could easily see it being a net loss for individual
> INSERTs.
What about multi value inserts? Just curious.
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
"Joshua D. Drake" <jd@commandprompt.com> writes:
>> cost is having to fsync the whole table afterwards. So it really only
>> makes sense for commands that one can expect are writing pretty much
>> all of the table. I could easily see it being a net loss for individual
>> INSERTs.
> What about multi value inserts? Just curious.
I wouldn't want the system to assume that a multi-VALUES insert is
writing most of the table. Would you? The thing is reasonable for
inserting maybe a few hundred or few thousand rows at most, and that's
still small in comparison to typical tables.
regards, tom lane
On Sat, 2007-01-06 at 22:09 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> >> cost is having to fsync the whole table afterwards. So it really only
> >> makes sense for commands that one can expect are writing pretty much
> >> all of the table. I could easily see it being a net loss for individual
> >> INSERTs.
>
> > What about multi value inserts? Just curious.
>
> I wouldn't want the system to assume that a multi-VALUES insert is
> writing most of the table. Would you? The thing is reasonable for
> inserting maybe a few hundred or few thousand rows at most, and that's
> still small in comparison to typical tables.
Good point. :)
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On Sat, 2007-01-06 at 21:32 -0500, Tom Lane wrote: > Robert Treat <xzilla@users.sourceforge.net> writes: > > On Saturday 06 January 2007 16:36, Simon Riggs wrote: > > <snip> > >> BEGIN; > >> CREATE TABLE foo... > >> INSERT INTO foo --uses WAL > >> COPY foo.. --no WAL > >> INSERT INTO foo --uses WAL > >> COPY foo.. --no WAL > >> INSERT INTO foo --uses WAL > >> COPY foo... --no WAL > >> COMMIT; > > > Is there some technical reason that the INSERT statements need to use WAL in > > these scenarios? > > First, there's enough other overhead to an INSERT that you'd not save > much percentagewise. Second, not using WAL doesn't come for free: the > cost is having to fsync the whole table afterwards. So it really only > makes sense for commands that one can expect are writing pretty much > all of the table. I could easily see it being a net loss for individual > INSERTs. Agreed. We agreed that before, on the original design thread. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com