Thread: caching written values?

Hi

A quick question, when pg receives data to be written to a table, does
it cache that data in memory in case a subsequent request/query would
need it?

As I understand it, data is stored in pages and those pages have to be
retrieved in order to write or read data from them. So my assumption is
that a page used to write data would not be replaced until memory is low
and different pages needs to be retrieved. Is this approximately correct?

Thomas

On Thu, Jan 22, 2009 at 4:42 PM, Thomas Finneid <tfinneid@fcon.no> wrote:

>
> As I understand it, data is stored in pages and those pages have to be
> retrieved in order to write or read data from them. So my assumption is that
> a page used to write data would not be replaced until memory is low and
> different pages needs to be retrieved. Is this approximately correct?
>

Yes. That's how it works.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com

>
> A quick question, when pg receives data to be written to a
> table, does it cache that data in memory in case a
> subsequent request/query would need it?
>

Afaik all pages are modified in memory, so the modified data would still be cached.

Pavan Deolasee wrote:
> On Thu, Jan 22, 2009 at 4:42 PM, Thomas Finneid <tfinneid@fcon.no> wrote:
>
>> As I understand it, data is stored in pages and those pages have to be
>> retrieved in order to write or read data from them. So my assumption is that
>> a page used to write data would not be replaced until memory is low and
>> different pages needs to be retrieved. Is this approximately correct?
>>
>
> Yes. That's how it works.

Is there any possibilites of telling pg to save to disk that memory
cached data and state when the server is shutdown, so that when the
server starts up again, itreads it back into the memory?

regards

thomas

(Sorry, did not include the list in the reply)

Pavan Deolasee wrote:

> Yes. That's how it works.

Is that how it works for an index as well? I just found out that I have
  an index that is 35GB, and the table is 85GB.  ( I will look into the
index, it works fine, but an index that is almost one third of the size
of the table, seems a little bit strange. )
So if it works the same way and the index uses a B-tree, I assume it
only loads the pages that contains the subpart of the index that are
relevant, is this correct?

thomas

> Is that how it works for an index as well? I just found out that I have  an
> index that is 35GB, and the table is 85GB.  ( I will look into the index, it
> works fine, but an index that is almost one third of the size of the table,
> seems a little bit strange. )
> So if it works the same way and the index uses a B-tree, I assume it only
> loads the pages that contains the subpart of the index that are relevant, is
> this correct?

Yes.

See shared_buffers:

http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html

...Robert

On Thu, 2009-01-22 at 13:11 +0100, Thomas Finneid wrote:

> Is there any possibilites of telling pg to save to disk that memory
> cached data and state when the server is shutdown, so that when the
> server starts up again, itreads it back into the memory?

It's possible, but not by any directly supported mechanism.

You have to consider whether the data you saved would still be required
when the server restarts.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support