Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
Jeff Janes wrote That's not really true. There are no per-row WAL records. There is still a per-transaction WAL record, the commit record. If you only care about the timing of the WAL and not the volume, changing to unlogged will not make a difference. (These commit-only records are automatically dealt with asynchronously, even if synchronous-commit is on.) Cheers, Jeff Hi, just to understand this answer: Is it *NOT* possible to create an unlogged table where inserts or updates will *NOT* modify the WAL files? Regards, hall -- View this message in context: http://postgresql.nabble.com/Will-modifications-to-unlogged-tables-also-be-flused-to-disk-tp5792158p5829600.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
On Mon, Dec 8, 2014 at 4:43 AM, frank halldu0...@gmail.com wrote: Jeff Janes wrote That's not really true. There are no per-row WAL records. There is still a per-transaction WAL record, the commit record. If you only care about the timing of the WAL and not the volume, changing to unlogged will not make a difference. (These commit-only records are automatically dealt with asynchronously, even if synchronous-commit is on.) Cheers, Jeff Hi, just to understand this answer: Is it *NOT* possible to create an unlogged table where inserts or updates will *NOT* modify the WAL files? Correct.
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
On Sat, Feb 15, 2014 at 3:03 AM, Clemens Eisserer linuxhi...@gmail.comwrote: Hi, I would like to use postgresql's unlogged tables on an embedded system to avoid frequent writes to flash memory. While documentation clearly states that unlogged tables don't have to go through the WAL, That's not really true. There are no per-row WAL records. There is still a per-transaction WAL record, the commit record. If you only care about the timing of the WAL and not the volume, changing to unlogged will not make a difference. (These commit-only records are automatically dealt with asynchronously, even if synchronous-commit is on.) Cheers, Jeff
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
Clemens Eisserer linuxhi...@gmail.com wrote: Hi, I would like to use postgresql's unlogged tables on an embedded system to avoid frequent writes to flash memory. While documentation clearly states that unlogged tables don't have to go through the WAL, it doesn't mention what happens to the data when it is written directly to the database. Will unlogged tables also cause sync/fsync calls, or will they stay in the page cache of the system as they will be lost anyway after a recovery? Thank you in advance, Clemens They will lost after a crash, but after a regular shutdown / restart all data in the table. 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
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
Hi Andreas, They will lost after a crash, but after a regular shutdown / restart all data in the table. Yes, the semantics are clearly stated in the documentation. What I wonder is whether postgresql will issue flush/fsync operations when unlogged tables are modified? Regards, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
On 02/15/2014 12:22 PM, Clemens Eisserer wrote: Hi Andreas, They will lost after a crash, but after a regular shutdown / restart all data in the table. Yes, the semantics are clearly stated in the documentation. What I wonder is whether postgresql will issue flush/fsync operations when unlogged tables are modified? Yes. The only difference between logged and unlogged tables is the lack of WAL. As long as there's no crash, unlogged tables are treated the same as logged tables as far as flushing/fsync-ing is concerned. -- Vik -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
Hi Vik, Yes. The only difference between logged and unlogged tables is the lack of WAL. As long as there's no crash, unlogged tables are treated the same as logged tables as far as flushing/fsync-ing is concerned. Ok thats really bad news :/ After reading the discussion about calling unlogged tables in memory or cached I actually had high hopes pgql would take advantage of the fact that data of unlogged tables are not preserved at recovery. However, it seems pgsql fsyncs frequently to guarantee durability of table data which will be rejected anyway at recovery from an unclean shutdown. I'll have a look at the source and probably prepare a patch. Regards, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
Hi, On 2014-02-15 12:22:56 +0100, Clemens Eisserer wrote: They will lost after a crash, but after a regular shutdown / restart all data in the table. Yes, the semantics are clearly stated in the documentation. What I wonder is whether postgresql will issue flush/fsync operations when unlogged tables are modified? No fsyncs will be issued in the common paths, and they won't be written to disk by !shutdown checkpoints. But they *will* be written to disk if there's not enough space in shared_buffers for all the data. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
On Sat, Feb 15, 2014 at 12:38 PM, Clemens Eisserer linuxhi...@gmail.com wrote: Ok thats really bad news :/ After reading the discussion about calling unlogged tables in memory or cached I actually had high hopes pgql would take advantage of the fact that data of unlogged tables are not preserved at recovery. Sorry, I could be misunderstanding here, but if the final aim is to reduce the writes, why not tweaking wal settings and checkpoints? I mean, is it possible to find a good solution or you need a kind of in memory storage? Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?
Hi Andres, No fsyncs will be issued in the common paths, and they won't be written to disk by !shutdown checkpoints. But they *will* be written to disk if there's not enough space in shared_buffers for all the data. With unlogged tables I still see data written to the device every 10 seconds, so I did some digging and found bgwriter_delay = 1, again with an artifical 10s limit applied on the parameter. To disable background writing, I set bgwriter_lru_maxpages = 0 which seems to help. What is left, is the wal-writer-process which still seems to fsync every 10s, even though there is no data written to any logged table. Regards, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
