Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "Matthew Wakeling" <matthew@flymine.org>
Cc: "Dimitri Fontaine" <dfontaine@hi-media.com>, "Josh Berkus"
	<josh@agliodbs.com>, "postgres performance list"
	<pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL as a local in-memory cache
References: <cb0fb58c-9134-4314-a1d0-08fc39f911a6@40g2000pry.googlegroups.com>
	<18287446-8677-46d6-a581-c4777f60ebda@40g2000pry.googlegroups.com>
	<4C1A5B81.8050908@agliodbs.com> <m2typ18ow0.fsf@hi-media.com>
	<op.vegnqzoxeorkce@apollo13>
	<alpine.DEB.2.00.1006181002360.2534@aragorn.flymine.org>
Date: Fri, 18 Jun 2010 11:40:43 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.veho55mieorkce@apollo13>
In-Reply-To: <alpine.DEB.2.00.1006181002360.2534@aragorn.flymine.org>
User-Agent: Opera Mail/10.10 (Linux)


> I'd like to point out the costs involved in having a whole separate  
> "version"

It must be a setting, not a version.

For instance suppose you have a session table for your website and a users  
table.

- Having ACID on the users table is of course a must ;
- for the sessions table you can drop the "D"

Server crash would force all users to re-login on your website but if your  
server crashes enough that your users complain about that, you have  
another problem anyway. Having the sessions table not WAL-logged (ie  
faster) would not prevent you from having sessions.user_id REFERENCES  
users( user_id ) ... so mixing safe and unsafe tables would be much more  
powerful than just having unsafe tables.

And I really like the idea of non-WAL-logged indexes, too, since they can  
be rebuilt as needed, the DBA could decide between faster index updates  
but rebuild on crash, or normal updates and fast recovery.

Also materialized views etc, you can rebuild them on crash and the added  
update speed would be good.

> Moreover, we already have a mechanism for taking a table that has had  
> non-logged changes, and turning it into a fully logged table - we do  
> that to the above mentioned tables when the transaction commits. I would  
> strongly recommend providing an option to ALTER TABLE MAKE SAFE, which  
> may involve some more acrobatics if the table is currently in use by  
> multiple transactions, but would be valuable.

I believe the old discussions called this ALTER TABLE SET PERSISTENCE.

> This would allow users to create "temporary tables" that can be shared  
> by several connections. It would also allow bulk loading in parallel of  
> a single large table.

This would need to WAL-log the entire table to send it to the slaves if  
replication is enabled, but it's a lot faster than replicating each record.