Thread: avoiding WAL logging in 8.3
Hi, 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. -- Tatsuo Ishii SRA OSS, Inc. Japan
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)