I am considering log-shipping of Write Ahead Logs (WAL) in PostgreSQL to create a warm-standby database. However I have one table in the database that receives a huge amount of INSERT/DELETEs each day, but which I don't care about protecting the data in it. To reduce the amount of WALs produced I was wondering, is there a way to prevent any activity on one table from being recorded in the WALs?
+4
A:
Unfortunately, I don't believe there is. The WAL logging operates on the page level, which is much lower than the table level and doesn't even know which page holds data from which table. In fact, the WAL files don't even know which pages belong to which database.
You might consider moving your high activity table to a completely different instance of PostgreSQL. This seems drastic, but I can't think of another way off the top of my head to avoid having that activity show up in your WAL files.
Greg Hewgill
2008-09-02 08:34:37
A:
To offer one option to my own question. There are temp tables - "temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below)" - which I think don't generate WALs. Even so, this might not be ideal as the table creation & design will be have to be in the code.
Guy C
2008-09-03 12:54:39
Temp tables will generate WAL entries for system catalog updates (pg_class) even if they don't for data updates (I'm not sure). You could move the busy table elsewhere and use the dblink interface to access it or switch to a table-based replication system like Slony.
mjy
2008-12-25 12:34:42
A:
I'd consider memcached for use-cases like this. You can even spread the load over a bunch of cheap machines too.
Ken Arnold
2009-05-03 03:33:47