avoiding WAL logging in 8.3

On Tue, 2007-07-24 at 13:04 +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.

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

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?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

On Jul 23, 2007, at 11:30 PM, Simon Riggs wrote:

On Tue, 2007-07-24 at 13:04 +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.

I would mention it in the documentation for each affected command
(COPY, TRUNCATE, etc). I suspect a lot of folks end up only using the
SQL reference section.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)