PostgreSQL Performance - ALTER TABLE SET TABLESPACE and pg

Author

ALTER TABLE SET TABLESPACE and pg_toast

PostgreSQL

2005-12-16, 9:23 am

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

Tom Lane

2005-12-16, 9:23 am

"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/...g-pg-class.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links