Presently, my feeling is that we could address this use-case without
having to introduce a new cluster-wide WAL level, and that's the
direction I'd want to see this going. Perhaps I'm missing something
about why the approach I've set forth above wouldn't work, and
wal_level=none would, but I've not seen it yet.
+1
We are trying to address a performance optimization for an insert-only scenario on a limited set of tables by placing the entire cluster in a dangerous state. The "copy table unlogged" solution is definitely closer to what we want - this is
demonstrably worse.
For this case the fundamental feature that would seem to be required is an ability for a transaction commit to return only after the system has ensured that all of the new pages added to the relation during the scope of the transaction have made it to disk. Something like:
BEGIN UNLOGGED TRANSACTION FOR table1, table2;
-- locking probably allows reads, definitely disallows concurrent writes, to the named tables
-- Disallow updates and deletes, do not use dead tuple space, for the tables named. Should be able to do normal stuff for other tables?
-- Always create new pages
COPY TO table1;
COPY TO table2;
COMMIT; -- wait here until data files for table1 and table2 are completely written and the transaction alive flag is committed to the WAL.
I suppose the above could be written "BEGIN UNLOGGED TRANSACTION FOR ALL TABLES" and you'd get the initial database population optimization capability.
If the commit doesn't complete all of the newly created pages are junk. Otherwise, you have a crash-recoverable state for those tables as regards those specific pages.
Conceptually, we need an ability to perform a partial CHECKPOINT that names specific tables, and make sure the crash-recovery works for those tables while figuring out what amount of effort to expend on informing the dba and alerting/preventing features that require wal from using those tables.
David J.