Re: prevent immature WAL streaming

On 2021-Oct-13, Andres Freund wrote:

> > > Another thing: filling a segment by inserting lots of very tiny rows is pretty
> > > expensive. Can't we use something a bit wider? Perhaps even emit_message?
>
> FWIW, the count of inserted rows is something like 171985 ;)

This does ~1600 iterations to fill one segment, 10 rows per iteration,
row size is variable; exits when two BLCKSZ remain to complete the WAL
segment:

create table filler (a int, b text);
do $$
declare
wal_segsize int := setting::int from pg_settings where name = 'wal_segment_size';
remain int;
iters int := 0;
begin
loop
insert into filler
select g, repeat(md5(g::text), (random() * 60 + 1)::int)
from generate_series(1, 10) g;

remain := wal_segsize - (pg_current_wal_insert_lsn() - '0/0') % wal_segsize;
raise notice '(%) remain: %', iters, remain;
if remain < 2 * setting::int from pg_settings where name = 'block_size' then
exit;
end if;
iters := iters + 1;
end loop;
end
$$ ;

(Of course, I'm not proposing that the 'raise notice' be there in the
committed form.)

If I enlarge the 'repeat' count, it gets worse (more iterations
required) because a lot of the rows become toasted and thus subject to
compression. If I do 20 rows per iteration rather than 10, the risk is
that we'll do too many near the end of the segment and we'll have to
continue running until completing the next one.

So, this seems good enough.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL. This is by far the most pleasant management experience of
any database I've worked on." (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php