asynchronous commit

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:

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general