Re: unlogged tables

On Mon, Apr 13, 2015 at 7:45 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> On 4/13/15 7:32 PM, David G. Johnston wrote:​
>
> 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.
>

​That seems like an automation concern though...the more limited idea was
to simply have a means for a table to exist on the master and allow the
user to cause an exact copy of that table to appear on a replica via direct
data transfer (i.e., without need to create a backup/dump). If the table
already exists on the replica the existing version remains as-is until the
new table is fully push and then a filenode pointer update happens. If
changes are made to the master the two tables will remain diverged until a
new push occurs.​

I imaging this same idea could be handled external to the database though
I'm don't know enough to comment on the specific technical merits of each.​

> 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.
>
>
​Jeff Janes makes a comment about wanting "...to avoid blowing out the log
archive..."; which I also don't quite follow...

WAL does seem to be designed to solve a different problem that what is
described here - lots of small changes versus few large changes. Improving
WAL to move the size at which small becomes large is a win but another
channel designed for few large changes may be less complex to implement.
The current work in logical replication likely has merit here as well but
my familiarity with that technology is fairly limited.

David J.