Re: PostgreSQL as a local in-memory cache - Mailing list pgsql-performance

From Pierre C
Subject Re: PostgreSQL as a local in-memory cache
Date
Msg-id op.veho55mieorkce@apollo13
Whole thread Raw
In response to Re: PostgreSQL as a local in-memory cache  (Matthew Wakeling <matthew@flymine.org>)
Responses Re: PostgreSQL as a local in-memory cache  (Josh Berkus <josh@agliodbs.com>)
List 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 by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: PostgreSQL as a local in-memory cache
Next
From: Robert Haas
Date:
Subject: Re: B-Heaps