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