From: Pierre C <lists@peufeu.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: Dimitri Fontaine <dfontaine@hi-media.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL as a local in-memory cache
Date: Fri, 18 Jun 2010 11:40:43 +0200
Message-ID: <op.veho55mieorkce@apollo13> (raw)
In-Reply-To: <alpine.DEB.2.00.1006181002360.2534@aragorn.flymine.org>
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>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, matthew@flymine.org, dfontaine@hi-media.com, josh@agliodbs.com
Subject: Re: PostgreSQL as a local in-memory cache
In-Reply-To: <op.veho55mieorkce@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox