Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #257

Re: Constraints for R/O ./data/{base, et al.}

Show all headers | View raw

Don Y wrote:
>>> Given largely static tables (etc.), how much of ./data
>>> can I move onto R/O media?  And, which types of operations
>>> would a user then have to avoid?

>> As has been mentioned, that is not supported.

> "Supporting" R/O media and "working (properly) in its
> presence" are two different issues.  E.g., one could
> argue that it is folly to have tables based in non-writable
> media -- but that assumes you would ALWAYS want to be able
> to alter those tables!

"Not supported" in that context means that if you have a problem
starting a PostgreSQL server where (part of) the data directory
is on a CDROM, very few people would be willing to investigate
and help you solve the problem.

Is that a theoretical question or do you really plan to move
your database to read-only media?

What is the problem behind your question?

>> For example, every checkpoint changes files in the data directory.
>
> *Every* file?  I.e., the "modified" times of all files are
> changed AND their contents differ from what they were just
> prior to the event?

Of course not. I didn't say "all files".

I can't give you any guarantees which files will change and
which will not (I know too little). It is probably safe to
assume that user tables and indexes won't ever change if you
don't update (and if no VACUUM is run).

>>> Loosening constraints a little, how much more could a user
>>> do if the R/O media was just "very slowly writable" media?

>> He or she could do everything, DML would be very slow.
>> The only file that is updated frequently is
>> $PGDATA/pg_stat_tmp/pgstat.stat, that should be the only impact
>> on read-only queries.

> What about the *results* of those queries?  Or, are they just
> pushed off to VM?

Query results are in memory and get sent to the client.

But that brings up a good point: large sorts and the like
will use temporary files in the temporary tablespace if they
don't fit in memory. There's another example of things that
*will* be written in a read-only database.

> Queries need to be able to store their results "somewhere".
> The amount and speed of that "somewhere" determines the
> effective rate at which queries can happen.

The effective query speed is determined by many things,
depending on the kind of query. Often disk speed is the
limiting factor, but with big sort and hash operations
it can be RAM and CPU.

> Note that you can move individual files ("how much of ./data")
> to different media as their needs dictate.  What I am after
> is some guidance as to how often/extensively particular files
> are updated in ./data and what operations *cause* those
> updates (i.e., controlling those operations means I can
> control those updates).

Most files in a PostgreSQL data directory have their fixed
places, they can't be moved around.

> To think of it in a more conventional environment, if you had
> limited local store, what would you chose to move off to an
> NAS?  And, what operations would that affect?

I would have the database directory on fast storage and
create tablespaces in slow storage. Tables and indexes that
are not used a lot (or are small and don't change much)
would go there.

Yours,
Laurenz Albe 

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Constraints for R/O ./data/{base, et al.} Don Y <nowhere@here.com> - 2011-09-28 08:58 -0700
  Re: Constraints for R/O ./data/{base, et al.} Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-28 18:51 +0000
  Re: Constraints for R/O ./data/{base, et al.} "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-29 09:37 +0200
    Re: Constraints for R/O ./data/{base, et al.} Don Y <nowhere@here.com> - 2011-10-06 22:17 -0700
      Re: Constraints for R/O ./data/{base, et al.} "Laurenz Albe" <invite@spam.to.invalid> - 2011-10-07 10:10 +0200
        Re: Constraints for R/O ./data/{base, et al.} Don Y <nowhere@here.com> - 2011-10-07 11:12 -0700
          Re: Constraints for R/O ./data/{base, et al.} Mladen Gogala <gogala.mladen@gmail.com> - 2011-10-08 05:48 +0000
          Re: Constraints for R/O ./data/{base, et al.} "Laurenz Albe" <invite@spam.to.invalid> - 2011-10-10 16:48 +0200
            Re: Constraints for R/O ./data/{base, et al.} "Laurenz Albe" <invite@spam.to.invalid> - 2011-10-25 12:37 +0200

csiph-web