Thread: How to _really_use a non-default tablespace
It seems that PG uses the PGDATA directory even for operations that affect databases on different tablespaces. For example, when an index is created on a table that is in tablespace TS, the index ends up in TS (as it should), but first a temporary file (with size comparable to the final index) is written below PGDATA, and is truncated only at the end of the operation. As a more drastic example: I created a DB on a tablespace TS, and copied a 25 MB file into one of its tables. I ended up having 60 MB of extra data in $PGDATA/pg_xlog that doesn't go away even after dropping the database. The docs (19.6 Tablespaces) says "The tablespace associated with a database is used to store the system catalogs of that database, as well as any temporary files created by server processes using that database." so I must be missing something. Andras
On Tue, Oct 17, 2006 at 04:56:09PM +0200, Andras Simon wrote: > As a more drastic example: I created a DB on a tablespace TS, and > copied a 25 MB file into one of its tables. I ended up having 60 > MB of extra data in $PGDATA/pg_xlog that doesn't go away even > after dropping the database. The xlog is not split by database, all databases share the same xlog. Have a ncie day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On 10/17/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Tue, Oct 17, 2006 at 04:56:09PM +0200, Andras Simon wrote: > > As a more drastic example: I created a DB on a tablespace TS, and > > copied a 25 MB file into one of its tables. I ended up having 60 > > MB of extra data in $PGDATA/pg_xlog that doesn't go away even > > after dropping the database. > > The xlog is not split by database, all databases share the same xlog. OK, I see. The question then is how far does it grow. If its size is comparable to that of the actual data, then having separate tablespaces is not as useful as it first seemed to me. But I might be missing something. And there's still the question of indices... Thanks, Andras
On Tue, Oct 17, 2006 at 05:54:42PM +0200, Andras Simon wrote: > >The xlog is not split by database, all databases share the same xlog. > > OK, I see. The question then is how far does it grow. If its size is > comparable to that of the actual data, then having separate > tablespaces is not as useful as it first seemed to me. But I might be > missing something. xlogs are recycled. You can control the growth somewhat by playing with the xlog settings in the config. It should stabilise at about 16MB times the wal segments. Have a nice dat, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On 10/17/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > xlogs are recycled. You can control the growth somewhat by playing with > the xlog settings in the config. It should stabilise at about 16MB > times the wal segments. This is very good news! Thanks, Andras