Re: making an unlogged table logged

On Wed, Jan 5, 2011 at 6:25 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 1/5/11 3:14 PM, Robert Haas wrote:
>> I think that's probably a dead end - just to take one example, if you
>> don't sync often enough, the standby might have transaction ID
>> wraparound problems.  Autovacuum on the master will prevent that for
>> permanent tables, but not for an only-occasionally-updated copy of an
>> unlogged table.
>
> I think you're missing Agent M's idea: if you could write to unlogged
> tables on the standby, then you could use application code to
> periodically synch them.
>
> Mind you, I personally don't find that idea that useful -- unlogged
> tables are supposed to be for highly volatile data, after all.  No doubt
> M was thinking that in a failover situation, it would be better to have
> stale data than none at all.
>
> However, if an unlogged table created on the master could be available
> for writing and initially empty on the standbys, it would give each
> standby available temporary/buffer tables they could use. That would be
> *really* useful.

OIC, sorry. Well, that could possibly be done, but it'd be tricky.
The obvious problem is that the backend doing the writing would need
an XID, and it'd probably have to ask the master to assign it one...
which is possibly doable, but certainly not ideal (you can't write on
the slave if the master is down, unless you promote it). Then there's
a whole bunch of follow-on problems, like now the standby needs to run
autovacuum - but only on the unlogged tables, and without being able
to update or rely on pg_database.datfrozenxid.

I think we have to face up to the fact that WAL shipping is an
extremely limiting way to do replication. It has its perks, certainly
- principally, that it minimizes the amount of extra work that must be
done on the master, which is an extremely valuable consideration for
many applications. However, it's also got some pretty major
disadvantages, and one of the big ones is that it's not well-suited to
partial replication. If it were possible to replicate individual
tables, we wouldn't be having this conversation. You'd just replicate
some tables from the master to the standby and then create a few extra
ones on the standby (perhaps permanent, perhaps unlogged, perhaps
temporary) and call it good.

I think we ought to seriously consider having both physical and
logical replication in core. Physical replication, which we have
today, is great for what it does, but trying to make it do things that
it's not good at is going to be an awful lot of work, and require an
awful lot of hacks, to make it cover everything that people really
want to be able to do. Adding logical replication would be a lot of
work but we'd get a lot of collateral benefits. Imagine that PG had
the ability to spit out INSERT/UPDATE/DELETE statements for designated
tables, as they were modified. That would provide a framework for
partial replication, replication from PG into other databases, even
multi-master replication if you add some kind of conflict resolution.
Even though this would require essentially building a whole new
system, it's starting to seem to me that it would be simpler than
trying to remove the limitations of our existing system incrementally.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company