From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: dgabriel <gabriel.dodan@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: unlogged tables
Date: Mon, 13 Apr 2015 21:45:21 -0500
Message-ID: <552C7F41.6070101@BlueTreble.com> (raw)
In-Reply-To: <CAKFQuwZhXngoQ9xG9N0fvQYVTvXQoO4cO8r-ZYHN+xLWiJEqCQ@mail.gmail.com>
References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com>
<1428953502160-5845576.post@n5.nabble.com>
<CAJghg4+NoOaip1VaBK9+cY-LYjmUr_9kFNZ3sdWq5wQRNVc9HA@mail.gmail.com>
<CAKFQuwYtdCF1BFx7pji78tshjUD-oVCoOOnBcr935_tq5gY-Ag@mail.gmail.com>
<CAMkU=1z_QRgLcGd1YTHcScbL4GFBcmPsuPxw92qy6HRXtHiscQ@mail.gmail.com>
<CAKFQuwZhXngoQ9xG9N0fvQYVTvXQoO4cO8r-ZYHN+xLWiJEqCQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 4/13/15 7:32 PM, David G. Johnston wrote:
> The missing feature is an option to leaved restored the last
> checkpoint. Instead, not knowing whether there were changes
> since the last checkpoint, the system truncated the relation.
>
> What use case is there for a behavior that the last checkpoint
> data is left on the relation upon restarting - not knowing
> whether it was possible the other data could have been written
> subsequent?
>
>
> I would like a way to have unlogged tables be available on a replica
> provided that no changes were made to them between the pg_basebackup
> and the recovery point.
>
>
> My use case is that I mark certain read-only-after-bulk-loading
> tables as unlogged solely to avoid blowing out the log archive
> during the loading phase and refresh phase. This is stuff like
> vendor catalogs, NCBI datasets, ChEMBL datasets, etc, which can
> simply be re-derived from the reference. It would be nice if these
> were still available (without having to repeat the ETL) after
> crashes provided they were not written to since a checkpoint, and
> available on cloned test servers without having to repeat the ETL on
> those as well.
>
>
> My gut reaction is that those should be in their own clusters and
> accessed via postgres_fdw...
Likely to produce really crappy plans if the tables are of any real size...
> That particular use-case would probably best be served with a separate
> replication channel which pushes data files from the primary to the
> slaves and allows for the slave to basically "rewrite" its existing
> table by pointing to the newly supplied version. Some kind of "CREATE
> STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}" command
> combo...though ideally with less manual intervention...
You still have the same problem of knowing if someone has scribbled on
the data since the last checkpoint.
There's been recent discussion of adding support for read-only tables.
If we had those, we might be able to support something like...
INSERT INTO unlogged;
ALTER TABLE unlogged SET READ ONLY;
CHECKPOINT;
/* take backup */
This should be safe as long as we WAL log changes to read-only status
(which presumably we would).
How much work that would entail though, I don't know.
Ultimately you still have to get the data over to the other machine
anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL
logging of bulk inserts (and especially COPY into a known empty table) a
lot more efficient.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, david.g.johnston@gmail.com, jeff.janes@gmail.com, matioli.matheus@gmail.com, gabriel.dodan@gmail.com
Subject: Re: unlogged tables
In-Reply-To: <552C7F41.6070101@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox