Re: avoiding WAL logging in 8.3

On Tue, 2007-07-24 at 18:45 +0900, Tatsuo Ishii wrote:
> > > I noticed in 8.3 there are chances where we can avoid WAL logging. For
> > > example, 8.3's pgbench was modified to use TRUNCATE right before
> > > COPY. Is there any documentation which describes that kind of
> > > techniques? If there's none, I would volunteer the work to create such
> > > a documentation since I think this is valuable information for DBAs
> > > who wish to migrate to 8.3.
> >
> > The Performance Tips section has been modified to describe this. Would
> > you like me to add something elsewhere also? Multiple entry points to
> > information helps everybody, so I'll happily add more.
>
> Thanks for pointing out. I found following:
>
> "COPY is fastest when used within the same transaction as an earlier
> CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be
> written, because in case of an error, the files containing the newly
> loaded data will be removed anyway."
>
> Sounds great!
>
> BTW, I noticed that "COPY, CLUSTER, B-Tree split logging improvements"
> in Josh's presentation in Tokyo. Are they just internal changes and
> are nothing to do with DBA's job?

Cluster is also mentioned lower down
http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PITR

The b-tree split logging is an algorithmic reduction in WAL, so isn't
user visible or optional in any way.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com