I've recently been doing some performance testing with unlogged tables
vs logged tables on 9.5-rc1. Basically we're trying to do big loads of
data into the database periodically. If on the very rare occasion the
server crashes half way through the import it's no big deal so I've been
looking specifically at unlogged tables with transactions having
synchronous_commit set to OFF. When we do the inserts on a logged table
with default WAL configuration settings we get a *lot* of disk IO
generated (500mb/sec of pretty random IO - we have a nice ssd raid array
but even so this maxes it out). Tweaking WAL settings (commit_delay,
max_wal_size, min_wal_size) improves the situation quite a bit
(50-100mb/sec of writes), but still we have no need to log the inserts
into the WAL at the moment.
Doing the imports to unlogged tables we get virtually no IO until the
insert process has finished when the table gets flushed to disk which is
great for us. However I read in the manuals that if the server ever has
an unclean shutdown all unlogged tables will be truncated. Obviously
with 9.5 we can now alter tables to be logged/unlogged after insert but
this will still write all the inserts into the WAL. I can understand the
requirement to truncate tables with active IO at the point of unclean
shutdown where you may get corrupted data; but I'm interested to find
out how easy it would be to not perform the truncate for historical
unlogged tables. If the last data modification statement was run more
than eg 30 seconds or 1 minute before an unclean shutdown (or the data
was otherwise flushed to disk and there was no IO since then) can we not
assume that the data is not corrupted and hence not truncate the
unlogged tables?
Thanks
Mark
