Re: WAL streaming and dropping a large table

On 10/21/21 16:23, Rory Falloon wrote:
> Hi,
>
> My postgres server setup is thus: a production database, which is 
> using WAL streaming (hot standby) to four other hosts of various 
> latencies. I Have a table that is ~200GB which has been backed up, and 
> now I Want to remove it. The table is not in use, it is a child table 
> to a parent table that _is_ in use but I foresee no issues here. The 
> total DB size is roughly 300GB.  The main reason for needing to remove 
> it is to recover the space back on the partition which is humming away 
> at 88% usage, and the table I wish to drop is better off in an archive 
> somewhere.
>
> I just removed around 10% of it with  'delete from', which of course 
> cascaded to the replication hosts. This increased the size of my 
> pg_xlog folder (from ~3GB to ~6.5GB) which of course increased my 
> partition usage. Obviously this is expected, but I wonder what would 
> happen if I had issued the 'drop table'?
>
> I expect the nature of enabling max_replication_slots would mean the 
> database would retain the wal segments until all have caught up; it 
> could take quite a long time to 'catch up' after the 'drop table' 
> command? It took about 10 minutes before the pg_xlog folder size had 
> 'settled down' to what I normally see as default which is ~3GB.
>
> > wal_keep_segments is defined as 128,
> > wal_max_size is not defined,
> > max_replication_slots is enabled
>
> I'd prefer to use 'drop table' as it would recover the data 
> immediately, but not if it will impact the production database in a 
> way that the partition will become full, which defeats the purpose. Is 
> it advisable to move the pg_xlog folder to another volume on the 
> system with more space (which I have) and symlink - obviously a stop & 
> start of the db required - and then let the WAL archives fill up as 
> need be? Or am I missing something obvious (likely)
>
> Thanks
>
Well, the difference between "drop table" and "delete" is that "drop 
table" is transaction on the data dictionary. PostgreSQL has 
transactional DDL, which means that "drop table" can be rolled back, so 
that transaction has to be protected by WAL, but it is just the log of 
the dictionary tables. There is the 3rd option, called "truncate" which 
is also a DDL which creates a new empty table with the same description 
and discards the old files.  Both 'drop" and "truncate" will not 
generate much WAL logs. On the other hand, "delete" is a regular DML 
transaction which will generate logs needed to recover the 200GB table.



-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com