tag:blogger.com,1999:blog-20038672.post6420471033147264853..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: Hint BitsRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-20038672.post-87373241997233944662011-11-10T08:52:53.432-05:002011-11-10T08:52:53.432-05:00Hi Robert,
Thanks for all the detail. It's g...Hi Robert,<br /><br />Thanks for all the detail. It's great that it can be set on a per transaction basis, but I personally think it would still be a really nice feature if we could set it on a per table basis.<br /><br />As with foreign keys and referential integrity, this may be something that can be done application side but in my view is always far better to enforce within the DB itself. So if you generally use asynchronous transactions but occasionally want to enforce that updates are handled synchronously then ideally the DB should be able to do this for you.<br /><br />That way if you have multiple applications or programs that access the same database then you can enforce the same rules across the board. Likewise a change in policy can be implemented once in one place rather than worrying about all the places throughout the code base that need to be kept in sync.<br /><br />Completely understand if this is a low priority though, or if the performance trade off isn't worth the extra functionality.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-8808500024644202402011-11-10T08:50:46.261-05:002011-11-10T08:50:46.261-05:00I had the same thought while reading your article....I had the same thought while reading your article. It seems to me that this information does belong into the database; for example, some unlogged table might have a trigger that in some cases writes stuff to a permanent table, turning an apparently innocuous write to an unlogged table into something that requires a sync commit. It doesn't seem reasonable to expect the application developer to cope with this sort of situation.Álvaro Herreranoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-80221825866684731162011-11-08T09:17:27.785-05:002011-11-08T09:17:27.785-05:00Chris, I've thought about that.
You can alrea...Chris, I've thought about that.<br /><br />You can already decide the level of durability you want on a per-transaction, per-session, per-user, or per-database basis using SET LOCAL, SET, ALTER ROLE .. SET, or ALTER DATABASE .. SET to adjust synchronous_commit.<br /><br />Adding a per-table option would possibly make this more automated. Instead of making it the transaction's job to know what kind of durability it wants, the durability policy would be built into the database: if you've touched only async-commit-ok tables, then you automatically get async commit; otherwise, you get whatever synchronous_commit provides for.<br /><br />But since this isn't really adding any fundamentally new capability, it's not clear whether it's worth doing. The bookkeeping would have some (probably trivial) overhead even for people who didn't use the feature, so we have to ask whether it would get enough use to justify that overhead, and the time it would take to implement it.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-19202662170342159422011-11-08T05:13:20.369-05:002011-11-08T05:13:20.369-05:00I'm going to guess that this would be too hard...I'm going to guess that this would be too hard to co-ordinate internally, but it would be great if we could decide on a table by table basis how much data we'd be willing to risk in a crash. For many projects there are certain tables, orders for example, where I'd want synchronous commits but within the same database there is CMS data where I'd be fine with an asynchronous commit after a couple of seconds. Equally there may be statistical information where I'd be happy to risk 30+ seconds of data if it helped performance.<br /><br />Would such flexibility be either worthwhile or possible in a future version of Postgres?Anonymousnoreply@blogger.com