Re: PostgreSQL as a local in-memory cache

On Jun 24, 2010, at 4:01 PM, Pavel Stehule wrote:

> 2010/6/24 Joshua D. Drake <jd(at)commandprompt(dot)com>:
>> On Thu, 2010-06-24 at 21:14 +0200, Pavel Stehule wrote:
>>> 2010/6/24 Josh Berkus <josh(at)agliodbs(dot)com>:
>>>>
>>>>> And I'm also planning to implement unlogged tables, which have the
>>>>> same contents for all sessions but are not WAL-logged (and are
>>>>> truncated on startup).
>>>
>>> this is similar MySQL's memory tables. Personally, I don't see any
>>> practical sense do same work on PostgreSQL now, when memcached exists.
>>
>> Because memcache is yet another layer and increases overhead to the
>> application developers by adding yet another layer to work with. Non
>> logged tables would rock.
>
> I see only one positive point - it can help to people with broken
> design application with migration to PostgreSQL.

The broken design is being required to work around PostgreSQL's lack of this optimization.

>
> There are different interesting feature - cached procedure's results
> like Oracle 11. - it's more general.
>
> only idea.
>
> For me memory tables are nonsens, but what about memory cached
> materialised views (maybe periodically refreshed)?

Non-WAL-logged, non-fsynced tables are not equivalent to MySQL "memory tables". Such tables simply contain transient information. One can already make "memory tables" in PostgreSQL by making a tablespace in a tmpfs partition.

I have been eagerly waiting for this feature for six years so that I can write proper queries against ever-changing session data with transactional semantics (which memcached cannot offer). The only restriction I see for these transient data tables is that they cannot be referenced by standard tables using foreign key constraints. Otherwise, these tables behave like any other. That's the benefit.

Cheers,
M