Re: PostgreSQL as a local in-memory cache

От: Pierre C
Тема: Re: PostgreSQL as a local in-memory cache
Дата: ,
Msg-id: op.veho55mieorkce@apollo13
(см: обсуждение, исходный текст)
Ответ на: Re: PostgreSQL as a local in-memory cache  (Matthew Wakeling)
Ответы: Re: PostgreSQL as a local in-memory cache  (Josh Berkus)
Список: pgsql-performance

Скрыть дерево обсуждения

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, )

> 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.



В списке pgsql-performance по дате сообщения:

От: Kenneth Marshall
Дата:
Сообщение: Re: requested shared memory size overflows size_t
От: "Jatinder Sangha"
Дата:
Сообщение: HashAggregate slower than sort?