Re: PostgreSQL as a local in-memory cache

Dimitri Fontaine wrote:
>> Well I guess I'd prefer a per-transaction setting

Not possible, as many others have said. As soon as you make an unsafe
transaction, all the other transactions have nothing to rely on.

On Thu, 17 Jun 2010, Pierre C wrote:
> A per-table (or per-index) setting makes more sense IMHO. For instance "on
> recovery, truncate this table" (this was mentioned before).

That would be much more valuable.

I'd like to point out the costs involved in having a whole separate
"version" of Postgres that has all this safety switched off. Package
managers will not thank anyone for having to distribute another version of
the system, and woe betide the user who installs the wrong version because
"it runs faster". No, this is much better as a configurable option.

Going back to the "on recovery, truncate this table". We already have a
mechanism for skipping the WAL writes on an entire table - we do that for
tables that have been created in the current transaction. It would surely
be a small step to allow this to be configurably permanent on a particular
table.

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.

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.

With these suggestions, we would still need to WAL-log all the metadata
changes, but I think in most circumstances that is not going to be a large
burden on performance.

Matthew

--
Picard: I was just paid a visit from Q.
Riker: Q! Any idea what he's up to?
Picard: No. He said he wanted to be "nice" to me.
Riker: I'll alert the crew.