Re: Feature Suggestion: Make synchronous_commit a table level property

Anas-ur-Rasheed Khan <annicheez(at)gmail(dot)com> writes:

Hi,

> We have a use case where some tables are derived, i.e., can be reconstructed entirely from 'source' tables (similar to
> views, but more complex mathematical transformations are applied). Data integrity and durability are important for
> 'source' tables, but not so much for derived tables. In the event of a crash, it is important that we can recover data
> from volatile memory for 'source' tables.
>
> What are your thoughts on this, and potentially adding such support in
> future postgres versions?

I think it is possible to do that since PostgreSQL already checked if a
table need a write log, we can check if the table needs a immedately
xlog flush.

But the final result would be if *any relation* need an immedate flush,
the transction need to flush the xlog during the commit stage, so only a
transaction which touch the derived table *only* could get benefit from
this feature. As an alternative, your application could identify the
"derived table only" transaction and set synchronous_commit to off by
your own.

--
Best Regards
Andy Fan