pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
ALTER TABLE SET TABLESPACE and pg_toast 2+ messages / 2 participants [nested] [flat]
* ALTER TABLE SET TABLESPACE and pg_toast @ 2005-12-16 14:25 PostgreSQL <martin@portant.com> 0 siblings, 1 reply; 2+ messages in thread From: PostgreSQL @ 2005-12-16 14:25 UTC (permalink / raw) To: pgsql-performance We're storing tif images in a table as bytea. We were running low on our primary space and moved several tables, including the one with the images, to a second tablespace using ALTER TABLE SET TABLESPACE. This moved quite cleaned out quite a bit of space on the original tablespace, but not as much as it should have. It does not appear that the corresponding pg_toast tables were moved. So, my questions are: 1) Is there a way to move pg_toast tables to new tablespaces (or at least assure that new ones are created there)? 2) Also, is there a good way to determine which pg_toast tables are associated with any particular table and column? Thank you for your help, Martin ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ALTER TABLE SET TABLESPACE and pg_toast @ 2005-12-16 15:23 Tom Lane <tgl@sss.pgh.pa.us> parent: PostgreSQL <martin@portant.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2005-12-16 15:23 UTC (permalink / raw) To: PostgreSQL <martin@portant.com>; +Cc: pgsql-performance "PostgreSQL" <martin@portant.com> writes: > We're storing tif images in a table as bytea. We were running low on our > primary space and moved several tables, including the one with the images, > to a second tablespace using ALTER TABLE SET TABLESPACE. > This moved quite cleaned out quite a bit of space on the original > tablespace, but not as much as it should have. It does not appear that the > corresponding pg_toast tables were moved. I think you're mistaken; at least, the SET TABLESPACE code certainly intends to move a table's toast table and index along with the table. What's your evidence for saying it didn't happen, and which PG version are you using exactly? > 2) Also, is there a good way to determine which pg_toast tables are > associated with any particular table and column? pg_class.reltoastrelid and reltoastidxid. See http://www.postgresql.org/docs/8.1/static/storage.html http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-16 15:23 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-12-16 14:25 ALTER TABLE SET TABLESPACE and pg_toast PostgreSQL <martin@portant.com> 2005-12-16 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox