Re: PostgreSQL as a local in-memory cache

From: Matthew Wakeling
Subject: Re: PostgreSQL as a local in-memory cache
Date: ,
Msg-id: alpine.DEB.2.00.1006181002360.2534@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: PostgreSQL as a local in-memory cache  ("Pierre C")
Responses: Re: PostgreSQL as a local in-memory cache  ("Pierre C")
Re: PostgreSQL as a local in-memory cache  (Josh Berkus)
List: pgsql-performance

Tree view

PostgreSQL as a local in-memory cache  ("", )
 Re: PostgreSQL as a local in-memory cache  ("", )
  Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
   Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
   Re: PostgreSQL as a local in-memory cache  (Dimitri Fontaine, )
    Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
     Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
      Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
       Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
      Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
       Re: PostgreSQL as a local in-memory cache  (Dave Page, )
        Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
         Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
         Re: PostgreSQL as a local in-memory cache  (Dimitri Fontaine, )
       Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
     Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
      Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
       Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
        Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
         Re: PostgreSQL as a local in-memory cache  ("Kevin Grittner", )
         Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
          Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
           Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
            Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
             Re: PostgreSQL as a local in-memory cache  (Jignesh Shah, )
              Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
               Re: PostgreSQL as a local in-memory cache  (Brad Nicholson, )
                Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
               Re: PostgreSQL as a local in-memory cache  (Jignesh Shah, )
                Re: PostgreSQL as a local in-memory cache  (Greg Smith, )
    Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
     Re: PostgreSQL as a local in-memory cache  (Matthew Wakeling, )
      Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
       Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
        Re: PostgreSQL as a local in-memory cache  (Rob Wultsch, )
         Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
          Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
           Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
            Re: PostgreSQL as a local in-memory cache  ("Joshua D. Drake", )
             Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
              Re: PostgreSQL as a local in-memory cache  ("A.M.", )
               Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
            Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
             Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
            Re: PostgreSQL as a local in-memory cache  ("Joshua D. Drake", )
      Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
    Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
     Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
   Re: PostgreSQL as a local in-memory cache  (Greg Smith, )
   Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
 Re: PostgreSQL as a local in-memory cache  (Dave Crooke, )
  Re: PostgreSQL as a local in-memory cache  (Craig James, )

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.


pgsql-performance by date:

From: Robert Haas
Date:
Subject: Re: B-Heaps
From: Kenneth Marshall
Date:
Subject: Re: requested shared memory size overflows size_t