Re: Performance and WAL on big inserts/updates

(Just a note: my comments are not pg-specific .. indeed I don't know
much about pg recovery).

>>>>> "Marty" == Marty Scholes <marty(at)outputservices(dot)com> writes:

Marty> If the DB state cannot be put back to a consistent state
Marty> prior to a SQL statement in the log, then NO amount of
Marty> logging will help. The idea is that the state can be put
Marty> back to what it was prior to a particular log entry, be it
Marty> raw datafile blocks or a SQL statement.

The point is that with redo logging, you can just blindly apply the
log to the data pages in question, without even really restarting the
database.

Note that in ARIES, recovery follows: (1) analysis, (2) redo
_everything_ since last checkpoint, (3) undo losers.

So logging carefully will indeed help get the system to a consistent
state - actually after phase (2) above the system will be in precisely
the state during the crash .. and all that's left to do is undo all
the live transactions (losers).

BTW, logging raw datafile blocks would be pretty gross (physical
logging) and so ARIES logs the changes to each tuple in "logical"
fashion .. so if only one column changes only that value (before and
after images) are logged. This is what's called "physiological
logging".

Marty> See above. If this cannot be resolved prior to
Marty> re-executing a statement in the log, then the problem is
Marty> beyond ANY subsequent logging.

Not true ! By applying the log entries carefully you should be able to
restore the system to a consistent state.

>> Having said that, page-oriented undo logging can be a pain when
>> B-tree pages split. For higher concurrency, ARIES uses logical
>> undo logging. In this case, the logs are akin to your "parsed
>> statement" idea.
>>

Marty> Yes, my experience exactly. Maybe we are the only company
Marty> on the planet that experiences this sort of thing. Maybe

Well, logical undo is still at a much lower level than parsed
statements. Each logical undo log is something like "delete key 5 from
index xyz".

Marty> Maybe this is not a "traditional" RDBMS app, but I am not
Marty> in the mood to write my own storage infrastructure for it.

I agree that your app has a lot of updates .. it's just that I'm not
convinced that logical logging is a clean solution.

I also don't have a solution for your problem :-)

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh