`subscribe` causes WAL bloating when no changes being made on publishing tables.
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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