Thread: asynchronous commit
I have several tables that I use for logging and real-time stats. These are not critical and since they are a bottleneck I want transactions against them to always be asynchronous. Is there a way to specify this at a table level or do I have to make sure to call set synchronous_commit='off' every time I insert or update to them? And presumably remember to turn it back on again for safety.
On Mon, Jan 19, 2015 at 10:03 AM, Robert DiFalco <robert.difalco@gmail.com> wrote:
I have several tables that I use for logging and real-time stats. These are not critical and since they are a bottleneck I want transactions against them to always be asynchronous. Is there a way to specify this at a table level or do I have to make sure to call set synchronous_commit='off' every time I insert or update to them? And presumably remember to turn it back on again for safety.
There is no clean way to do what you want currently. You have to do the bookkeeping yourself. There was discussion about adding an option for "asynchronous commit" to tables. Then if all the tables changed during a transaction were asynchronous (and a few other conditions were met), the commit would automatically be asynchronous as well. I'd like to see that, but I don't think anyone is actively working on implementing it.
Cheers,
Jeff
Robert DiFalco <robert.difalco@gmail.com> wrote: > I have several tables that I use for logging and real-time stats. These are not > critical and since they are a bottleneck I want transactions against them to > always be asynchronous. Is there a way to specify this at a table level or do I > have to make sure to call set synchronous_commit='off' every time I insert or > update to them? And presumably remember to turn it back on again for safety. I think, you can use unlogged tables instead. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Andreas, I think UNLOGGED would be something different but I'm not totally clear. However, it seems to me that an unlogged table would simply disappear (be truncated) after a server crash. That means instead of maybe loosing a record or two that I could loose a ton or records. But maybe my understanding is off.
On Mon, Jan 19, 2015 at 11:10 AM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
I think, you can use unlogged tables instead.Robert DiFalco <robert.difalco@gmail.com> wrote:
> I have several tables that I use for logging and real-time stats. These are not
> critical and since they are a bottleneck I want transactions against them to
> always be asynchronous. Is there a way to specify this at a table level or do I
> have to make sure to call set synchronous_commit='off' every time I insert or
> update to them? And presumably remember to turn it back on again for safety.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Robert DiFalco <robert.difalco@gmail.com> wrote: > Andreas, I think UNLOGGED would be something different but I'm not totally > clear. However, it seems to me that an unlogged table would simply disappear > (be truncated) after a server crash. That means instead of maybe loosing a > record or two that I could loose a ton or records. But maybe my understanding > is off. That's correct. Maybe you can solve the performance issue with unlogged tables, but yes, you have to save the records into a regular table. Maybe via Cron or so. (rough, with move as (delete from unlogged returning *) insert into regular_table select * from move, or something similar.) Sorry for the delay. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Robert DiFalco <robert.difalco@gmail.com> wrote: > I have several tables that I use for logging and real-time stats. These are not > critical and since they are a bottleneck I want transactions against them to > always be asynchronous. Is there a way to specify this at a table level or do I > have to make sure to call set synchronous_commit='off' every time I insert or > update to them? And presumably remember to turn it back on again for safety. I think, you can use unlogged tables instead. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general