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 <matthew@flymine.org>)
Ответы Re: PostgreSQL as a local in-memory cache  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-performance
> 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 по дате отправления:

Предыдущее
От: Matthew Wakeling
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache
Следующее
От: Robert Haas
Дата:
Сообщение: Re: B-Heaps