porsager / porsager/postgres

`subscribe` causes WAL bloating when no changes being made on publishing tables.

Open
#790 1 comment 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
JavaScript
Stars
8.7k
Forks
374
Avg merge
11d 16h
Merged PRs (30d)
1

Description

Description

Subscribing to a publication causes WAL to bloat on database if there is not changes being made to the publishing tables while modification is being made on other non-publishing tables.

Reproduction

Setup the following via psql

create table tbl_a (id int);
create table tbl_b (id int);
create publication pub_a for table tbl_a;

Now subscribe to the publication.

import postgres from 'postgres'

const sql = postgres({..., publications: ['pub_a']});

sql.subscribe('*',async (row, meta) => {});

setInterval(async () => {
  // writes to non-publishing table
  await sql`insert into public.tbl_b (id) select generate_series(0, 1000)`;
  console.log(await sql`select sent_lsn, write_lsn from pg_stat_replication`)
}, 5000)

Observe the sent_lsn and write_lag keep increasing, while write_lsn will stay the same forever. This causes WAL to bloat as the database will hold on to the old logs.

...
Result(1) [
  {
    sent_lsn: '0/1588F0E0',
    write_lsn: '0/156E80B0',
    write_lag: '00:01:55.005627'
  }
]
Result(1) [
  {
    sent_lsn: '0/15899E88',
    write_lsn: '0/156E80B0',
    write_lag: '00:01:55.005627'
  }
]
...
Result(1) [
  {
    sent_lsn: '0/158C7FF8',
    write_lsn: '0/156E80B0',
    write_lag: '00:02:25.007645'
  }
]
Result(1) [
  {
    sent_lsn: '0/158E13D8',
    write_lsn: '0/156E80B0',
    write_lag: '00:02:25.007645'
  }
]
...

Now if instead of writing to tbl_b, we update our example to write to tbl_a instead, which is part of the publication, now the write_lsn will increment over time as expected.

setInterval(async () => {
 await sql`insert into public.tbl_a (id) select generate_series(0, 1000)`;
 console.log(await sql`select sent_lsn, write_lsn, write_lag from pg_stat_replication`)
}, 5000)
Result(1) [
  {
    sent_lsn: '0/15AA3798',
    write_lsn: '0/15A8A2E0',
    write_lag: '00:00:05.005519'
  }
]
...
Result(1) [
  {
    sent_lsn: '0/15B03730',
    write_lsn: '0/15AE98E0',
    write_lag: '00:00:04.971771'
  }
]
....
Result(1) [
  {
    sent_lsn: '0/15B3BE48',
    write_lsn: '0/15B38140',
    write_lag: '00:00:09.993504'
  }
]

Problem
The state.lsn currently is only being updated after receiving a Begin Message. But this type of message is only be sent by walsender when there is changes to the publishing table.
https://github.com/porsager/postgres/blob/6f20a4820c683b33e7670b606d8daf5670f4b973/src/subscribe.js#L178-L181

I think the state.lsn should also be update here on receiving a Primary Keep Alive.
https://github.com/porsager/postgres/blob/6f20a4820c683b33e7670b606d8daf5670f4b973/src/subscribe.js#L112
At the moment, postgres.js responses to the primary keep alive message by using the current state.lsn without updating the lsn first.
https://github.com/porsager/postgres/blob/6f20a4820c683b33e7670b606d8daf5670f4b973/src/subscribe.js#L126-L133
This causes the database to think our replication process is stalled and hold on the old WAL indefinitely.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in src/subscribe.js at the primary keep-alive handling around lines 112 and 126-133, then compare it with the Begin Message handling around lines 178-181. Reproduce the publication and non-publishing-table writes from the issue, and verify that the replication state advances so sent_lsn and write_lsn no longer diverge indefinitely.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.