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

Groups > comp.databases.postgresql > #260

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

Show all headers | View raw

Don Y wrote:
[wants to put part of the data directory on read-only storage]
>> 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).

> If you've never made any changes to the tables/indexes, why
> would VACUUM touch those files?  (i.e., assuming the DB had been
> vacuumed prior to being cast in stone)

I don't know the server code well enough to answer that question
without some research.

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

> So, *big* query results just rely on the VM system?

I *think* so. Again I don't know the relevant parts of the server
code well enough.

>> 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.

> Yes, but temporary files can be recognized as such.  I.e.,
> it is relatively trivial to hook the creat(3c) call so that it
> always uses writable media.

It would be simpler than that, because these files will be
created in the temporary tablespace.

>> 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.

> Thanks!  We'll look to see if we can identify the places where
> each of these actions take place and see what "rules" we can
> distill from them.

To really get definite answers which files in the data directory
will never change, you should look for somebody who knows
PostgreSQL better than I do. I suggest asking the pgsql-hackers
mailing list.

You would probably have to read a fair bit of server code and
understand the design principles well.

Maybe a tablespace with tables and indexes on read-only storage
would work. You could easily try it out.

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