Обсуждение: Feature Suggestion: Make synchronous_commit a table level property
Hi hackers,
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?
Thanks,
AK
Anas-ur-Rasheed Khan <annicheez@gmail.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
Re: Feature Suggestion: Make synchronous_commit a table level property
От
Christoph Moench-Tegeder
Дата:
## Anas-ur-Rasheed Khan (annicheez@gmail.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.
Re: Feature Suggestion: Make synchronous_commit a table level property
От
Anas-ur-Rasheed Khan
Дата:
Hi Chris and Andy,
Thanks for your comments.
I see your point in regards to unlogged tables. While this statement 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. 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
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@burggraben.net> wrote:
## Anas-ur-Rasheed Khan (annicheez@gmail.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.