Обсуждение: Truncate and delete adds wal logs for slave to process.
I have a question regaring delete & truncate versus a drop of the tables and recreating it.
We have a database that gets recreated each week that is 31 GB in size.
The way that it is currently being done is to truncate all of the tables.
I would like to confirm.
Because both truncate and delete, I would think that this action would be put into the pg_log as a log file that can be rolled back. And, when complete, it would be shipped to the standby to be processed?
To reduce this logging, shipping and processing would it be smarter to have the tables dropped and recreated?
On Thu, Feb 11, 2010 at 5:47 PM, Chris Barnes <compuguruchrisbarnes@hotmail.com> wrote: > > Because both truncate and delete, I would think that this action would be > put into the pg_log as a log file that can be rolled back. And, when > complete, it would be shipped to the standby to be processed? > > To reduce this logging, shipping and processing would it be smarter to have > the tables dropped and recreated? Truncate will generate about the same amount of log data as dropping and creating the table. Delete will generate quite a bit more, but still much less than 31G. It will also leave you needing to vacuum and reindex more often. -- greg
Chris Barnes wrote: > Because both truncate and delete, I would think that this action would > be put into the pg_log as a log file that can be rolled back. And, > when complete, it would be shipped to the standby to be processed? You would be wrong -- truncate does not log the full data, only the fact that a truncate happened. In order to be able to roll it back, the underlying file is kept around until transaction commit. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support