Re: Feature Suggestion: Make synchronous_commit a table level property

Hi Chris and Andy,

Thanks for your comments.

@Chris
I see your point in regards to unlogged tables. While this statement
<https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-UNLOGGED:~:text=considerably%20faster%20than%20ordinary%20tables>
might
be true in a world without constraints, in my case, on AWS RDS, IOPS are
valuable and limited. My workloads are operating at the border by 64000
IOPS before I start paying exponentially more for provisioned IOPS
<https://aws.amazon.com/ebs/provisioned-iops/>. Unlogged tables create more
IO load and result in SLOWER performance. Hence, this is not a viable
option for me. I think more and more engineers today are operating in a
similar environment, so this option is not always viable.

I realize that "Synchronous Commit" can be set at the transaction level;
however, this approach lacks consistency across transactions on a table.
Ideally, I want to enforce the property across all transactions on the
table, since it is inherently a feature of the table, i.e., a table should
be 'durable', not a transaction per se. Yes, it can happen at the
transaction level, but I think it makes sense, in this use case at least,
to set it at the table level and enforce across all transactions as default.

Best,
AK

On Thu, May 22, 2025 at 11:47 AM Christoph Moench-Tegeder <
cmt(at)burggraben(dot)net> wrote:

> ## Anas-ur-Rasheed Khan (annicheez(at)gmail(dot)com):
>
> > 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.
>
> Did you consider unlogged tables?
>
> https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-UNLOGGED
> The documentation says "considerably faster than ordinary tables" but
> "automatically truncated after a crash" which might fit your
> requirements.
>
> "Synchronous Commit" should be understood as transaction property and
> does not really work on a table level.
>
> Regards,
> Christoph
>
> --
> Spare Space.
>