On Sat, 2006-10-21 at 15:17 -0400, Theo Schlossnagle wrote: > On Oct 21, 2006, at 3:12 PM, Simon Riggs wrote: > > > On Sat, 2006-10-21 at 09:00 -0400, Theo Schlossnagle wrote: > >> On Oct 21, 2006, at 6:08 AM, Martijn van Oosterhout wrote: > >> > >>> On Sat, Oct 21, 2006 at 10:37:51AM +0100, Simon Riggs wrote: > >>>> Turning off WAL is a difficult topic. Without it you have no crash > >>>> recovery, which IMHO everybody says they don't care about until > >>>> they > >>>> crash, then they realise. It's hard to be selective about > >>>> writing WAL > >>>> for specific operations also. > >>> > >>> It's been discussed before. One idea is to declare tables without > >>> logging. The idea being that during recovery those tables and > >>> related > >>> indexes are simply truncated. No foreign keys allowed. Obviously > >>> they > >>> will not be saved via PITR either. > >>> > >>> Put another way, the table structure is saved in WAL, but the data > >>> isn't. > >> > >> This is exactly what I'd like. Simon suggested turning off WAL > >> during the loads as a possible hack solution. The reason this won't > >> work is that we snap all the time, lots of tables. We have between > >> 2000 and 4000 snapshot operations per day (throughout). At the same > >> time we have reporting queries running (that create and/or populate > >> other tables) that last from 5 minutes to 18 hours. It is important > >> that we run everything but the snapshots with WAL on (as we must have > >> PITR -- sans snapshots) > > > > These tables are loaded once then read-only, yes? > > No, they are loaded, and then reloaded, and then reloaded. Queries > that use them will get the most recently loaded version of them. It > meets a business rule like: table foo on the warehouse should be > representative of version of table foo on OLTP no older than 30 minutes.
But they can be re-created anew with the same name each time? Or I guess not, but you redefine a view every 30 minutes to point to the latest one? If so, then I have a patch that will speed up COPY when in the same transaction as the table that created it. I've finally fixed a bug in my earlier prototypes that seems to make that work now, in all cases. I was being slightly slow before; I thought this was a new requirement but its just the old one slightly restated. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster