pkalva@xxxxxxxxxxxxxxxxx (Pallav Kalva) writes: > Tom Lane wrote: >> Pallav Kalva <pkalva@xxxxxxxxxxxxxxxxx> writes: >> >>> We turned on autovacuums on 8.2 and we have a database which is >>> read only , it is basically a USPS database used only for address >>> lookups (only SELECTS, no updates/deletes/inserts). >>> >> >> >>> This database has about 10gig data and yesterday autovacuum >>> started on this database and all of a sudden I see lot of archive >>> logs generated during this time, I guess it might have generated >>> close to 3-4gig data during this period. >>> >> >> Probably represents freezing of old tuples, which is a WAL-logged >> operation as of 8.2. Is it likely that the data is 200M transactions >> old? >> > If nothing changed on these tables how can it freeze old tuples ? It does so very easily, by changing the XID from whatever it was to 2 (which indicates that a tuple has been "frozen.") I don't imagine you were wondering how it is done - more likely you were wondering why. "Why" is to prevent transaction ID wraparound failures. > Does it mean that once it reaches 200M transactions it will do the > same thing all over again ? It won't freeze those same tuples again, as they're obviously already frozen, but a vacuum next week may be expected to freeze tuples that are roughly a week newer. > If I am doing just SELECTS on these tables ? how can there be any > transactions ? or SELECTS considered transactions too ? Every query submitted comes in the context of a transaction. If there wasn't a BEGIN submitted somewhere, then yes, every SELECT could potentially invoke a transaction, irrespective of whether it writes data or not. If you submit a million SELECT statements, yes, that could, indeed, indicate a million transactions. -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://cbbrowne.com/info/nonrdbms.html How much deeper would the ocean be if sponges didn't live there? ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
