Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!newsfeed.utanet.at!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail From: "Laurenz Albe" Newsgroups: comp.databases.postgresql References: <1317281868.445091@proxy.dienste.wien.at> <1317975042.921423@proxy.dienste.wien.at> Subject: Re: Constraints for R/O ./data/{base, et al.} Date: Mon, 10 Oct 2011 16:48:23 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 X-RFC2646: Format=Flowed; Response X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 Organization: MagistratWien newsserver Message-Id: <1318258124.527489@proxy.dienste.wien.at> X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/) X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/) Lines: 57 NNTP-Posting-Host: 141.203.254.23 X-Trace: 1318258129 aconews.univie.ac.at 73248 141.203.254.23 X-Complaints-To: abuse@univie.ac.at Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:260 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