Thread: Will modifications to unlogged tables also be flused to disk?

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

Clemens Eisserer <linuxhippy@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°

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

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

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

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

On Sat, Feb 15, 2014 at 12:38 PM, Clemens Eisserer <linuxhippy@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

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 = 10000, 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

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.