Thread: caching written values?

caching written values?

From
Thomas Finneid
Date:
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

Re: caching written values?

From
Pavan Deolasee
Date:
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

Re: caching written values?

From
Glyn Astill
Date:
>
> 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.




Re: caching written values?

From
Thomas Finneid
Date:
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

Re: caching written values?

From
Thomas Finneid
Date:
(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

Re: caching written values?

From
Robert Haas
Date:
> 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

Re: caching written values?

From
Simon Riggs
Date:
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