Thread

1. BUG #19051: Is it possible to move a TOAST table to a different tablespace than its parent table?

   PG Bug reporting form <noreply@postgresql.org> — 2025-09-14T09:17:05Z

   The following bug has been logged on the website:
   
   Bug reference:      19051
   Logged by:          Mustafa HARARCI
   Email address:      mhararci@gmail.com
   PostgreSQL version: 16.10
   Operating system:   Ubuntu 16.10-1.pgdg22.04+1
   Description:        
   
   Hello everyone,
   
   I'm currently using PostgreSQL 16.10 and I've run into a challenge regarding
   moving TOAST data for a table. My goal is to have the TOAST data for a
   column stored in a different tablespace from the parent table.
   
   Specifically, I have a users table with a profile_data column of type JSONB,
   which can contain large objects. I want the users table itself to reside in
   the ts_medium_data tablespace, while its associated TOAST data is stored in
   a separate ts_medium_toast tablespace.
   
   I've attempted the following steps:
   
   I created the users table in the ts_medium_data tablespace.
   
   I identified the associated TOAST table using the pg_class catalog (SELECT
   reltoastrelid::regclass FROM pg_class WHERE relname = 'users';), which
   returned an object like pg_toast.pg_toast_16646.
   
   As a superuser (postgres), I tried to move the TOAST table to the new
   tablespace using ALTER TABLE pg_toast.pg_toast_16646 SET TABLESPACE
   ts_medium_toast;.
   
   This command, however, resulted in the following error: ERROR: permission
   denied: "pg_toast_16646" is a system catalog.
   
   This error suggests that direct modification of the TOAST table is not
   allowed, even with superuser privileges.
   
   My question is, is it fundamentally possible in PostgreSQL to store a
   table's main data and its TOAST data in different tablespaces? Is this
   behavior a design constraint? If so, is there any workaround for this, or is
   this simply not a supported feature?
   
   Any insight into this behavior or guidance on a potential solution would be
   greatly appreciated.
   
   Thank you.
   
   

2. Re: BUG #19051: Is it possible to move a TOAST table to a different tablespace than its parent table?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-09-14T10:29:09Z

   On Sun, 2025-09-14 at 09:17 +0000, PG Bug reporting form wrote:
   > I'm currently using PostgreSQL 16.10 and I've run into a challenge regarding
   > moving TOAST data for a table. My goal is to have the TOAST data for a
   > column stored in a different tablespace from the parent table.
   > 
   > Specifically, I have a users table with a profile_data column of type JSONB,
   > which can contain large objects. I want the users table itself to reside in
   > the ts_medium_data tablespace, while its associated TOAST data is stored in
   > a separate ts_medium_toast tablespace.
   
   Don't write to the bugs mailing list if you need help.
   I have directed replies to the -general mailing list.
   
   You won't be able to put a table and its TOAST table into different tablespaces.
   
   But there wouldn't be any benefit in doing that anyway, so that
   is no problem.  Perhaps you could detail the *real problem* you are trying
   to solve...
   
   Yours,
   Laurenz Albe