Re: BUG #10675: alter database set tablespace and unlogged table

On 2014-08-13 23:31:46 +0900, MauMau wrote:
> From: "Andres Freund" <andres(at)2ndquadrant(dot)com>
> >That'd mean that the next pointrelease will incur *significantly* higher
> >IO in many setups. If you currently have a workload where all dirty
> >buffers of unlogged tables fit in s_b you'll never have any OS
> >writes. That'd completely change.
>
> Yes, that's the only headache... But I'm not worried so much, because
> bgwriter and/or backends may write out dirty buffers for unlogged tables, so
> the total I/O is not low anyway.

If the workload fits in s_b - not an infrequent thing with today's
memory sizes - that's simply not true.

> >>* There's a greater danger of losing data during operating system
> >>restart.
> >>For example, IIRC, Windows gives only 20 seconds to terminate all
> >>services
> >>during OS shutdown. If many dirty buffers for unlogged tables linger in
> >>the
> >>shared buffers, PostgreSQL service may fail to complete database
> >>shutdown.
> >>Even if the online checkpoint writes out all dirty buffers, the
> >>possibility
> >>of there being many dirty buffers at shutdown is not zero, but the
> >>probability would be lower.
> >
> >Meh. That won't lead to data loss, just recovery on restart. And 20s
> >isn't sufficient for any halfway busy database anyway.
>
> The unlogged tables are emptied (= data loss) at recovery restart.

If that's data loss you shouldn't be using unlogged tables. That's not
an argument.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services