G'day Gavin, In maillist.postgres.dev, you wrote: > On Thu, 1 Feb 2007, Chris Dunlop wrote: >> The main idea is that, there might be space utilisation and >> performance advantages if postgres had "hard" read-only >> tables, i.e. tables which were guaranteed (by postgres) to >> never have their data changed (insert/update/delete). >> >> This could potentially save disk space by allowing "book >> keeping" elements in the page/tuple headers to be removed, >> e.g. visibility information etc. Also, some indexes could >> potentially be packed tighter if we know the data will never >> change (of course this is already available using the >> fillfactor control). > > Well, there is also CPU overhead doing MVCC but there are a > few fundamental problems that must be overcome. The most > significant is that no useful table is always read only, > otherwise you could never load it.
Yes, that's why I was proposing ALTER TABLE... SET ARCHIVE rather than CREATE TABLE... ARCHIVE. (Although, for consistency, perhaps the CREATE TABLE would be allowed, it's just that you couldn't load anything into it until you did a ALTER TABLE... DROP ARCHIVE.) > What do we do in the presence of a failure during the load or > a user issued ABORT? I guess we'd truncate the table... I was thinking the load simply couldn't happen if the table were SET ARCHIVE. > What about replay after a crash? No replay would be required on that table as it would *NOT* be changed once an SET ARCHIVE were done (unless a DROP ARCHIVE were done). > Another way of looking at it is, we use the 'bookkeeping' > information in the tuple header for concurrency and for > handling the abortion of the transaction. So, unless there's something I'm missing (not completely unlikely!), as long as the table (including it's on-disk representation) was never changed, the bookkeeping information wouldn't be required? >> The idea would be to introduce a statement something like: >> >> ALTER TABLE foo SET ARCHIVE; > > I'd not thought of that approach. There are two problems: some > archive tables are so large that loading them and then > reprocessing them isn't appealing. Yes - it would only work if you were prepared to wear the cost of the SET ARCHIVE, which could certainly be considerable. ...oh, I think I see what you were getting at above: you were thinking of loading the data into the already SET ARCHIVE table to avoid the considerable cost of rewriting the disk format etc. I hadn't considered that, but yes, if you were to allow that I suppose in the presence of load errors or ABORTS etc. the table could simply be truncated. (For whatever value of "simply" is appropriate!) > Secondly, we'd be rewriting the binary structure of the table > and this does not suit the non-overwriting nature of > Postgres's storage system. Rather than writing in-place, perhaps the SET ARCHIVE would create a on-disk copy of the table. Of course this would demand you have twice the disk space available which may be prohibitive in a large warehouse. On the other hand, I'm not sure if you would have a single humongous table that you'd SET ARCHIVE on, you might be as likely to archive on a weekly or yearly or whatever is manageable basis, along the lines of: begin; select * into foo_2006 from foo where date_trunc('year', timestamp) = '2006-01-01'; delete from foo where date_trunc('year', timestamp) = '2006-01-01'; alter table foo_2006 set archive; alter table foo_2006 inherit foo; commit; > A different approach discussed earlier involves greatly > restricting the way in which the table is used. This table > could only be written to if an exclusive lock is held; on > error or ABORT, the table is truncated. You're talking about the "no-WAL" concept? Not quite the same thing I think, but perhaps complimentary to the ARCHIVE idea: I wouldn't expect an ARCHIVE table to need to generate any WAL entries as it would be read only. Cheers, Chris. ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate