linuxhippy@gmail.com writes:
> It seems wal_writer_delay is limited to 10s without any technical reason.
The "technical reason" is that values much larger than that would be a
performance and reliability disaster for typical installations.
> I am using postgresql in an embedded system and writing every 10s
> burns flash at an amazing rate (a 32GB SDHC card survived 4 days with
> only a few GB written) - I've now switched from ext4 to nilfs2 which is able
> to distribute writes more evenly - but I am still not confident this setup
> would last longer than a few weeks/months.
> A wal_writer_delay of arround ~1h would be a lot more efficient and solve
> this issue.
It's not apparent to me what usage pattern that would actually help with.
If the system is idle then it doesn't matter whether the WAL writer wakes
up or not. But if you're generating WAL, it's going to get written anyway
whenever a transaction commits. You could forestall that with fsync=off,
perhaps, but if you do that then the WAL writer won't fsync either, so
it shouldn't matter how often it wakes up. Checkpoints will force WAL
output more often than once per hour by default, too. So I'm wondering
exactly what combination of other settings you envision using this with,
and what's the workload of the database server.
While it could certainly be argued that the limit of 10s is a bit too
tight, allowing values as large as 1h would be a large-caliber foot gun
for most people. So I'm very hesitant to raise it that far without a
more convincing argument that useful behavior can be had there.
The bigger picture is that if you allow WAL to not reach disk for 1h,
that's up to 1h worth of data that you lose irretrievably in a crash.
So even if this adjustment allowed you to get that behavior, it's not
very attractive behavior. If you actually are OK with such little data
security, maybe you should consider other approaches. Perhaps you could
keep the whole database on a RAM disk (tmpfs) and rsync it to flash every
hour, or some variant of that?
Also, if I understand your problem correctly, it's not so much that
you'd like the WAL writer to not write often as that you'd like it
to not write partial blocks. We might be willing to accept a patch
that added a setting and some logic to control things that way.
But I'm pretty dubious that just changing the upper limit of
wal_writer_delay is going to have useful results, even in your scenario.
regards, tom lane