Thanks for the reply...
This seems odd default behavior. A person coming from outside Postgres-land would expect that if you create a table using a tablespace, all the storage for that table would go to the tablespace, and not have bits of the table stored elsewhere. Sure, a lawyerly reading of the gory details of the CREATE TABLE statement in the manual may lead one to figure this out - or not - but it's still pretty weird behavior, and different from behavior of other db's.
I'd guess that this won't change (25 years of existing user scripts depending on this behavior, etc), but at least put something online that says that if you don't specifically declare a tablespace for your index storage, the PK will be stored in the default tablespace associated with the db the table is created in. Something like that may have saved me a few hours of futzing with all the other things that cause a db to randomly eat disk space :)
Anyway, thanks again for the reply, and it's good to see PG take off after having worked on it a lot 30+ years ago :)
Greg
On Wednesday, December 4, 2024, Greg Kemnitz <gjkemnitz@gmail.com> wrote:
In PG version 16.6.1.pgdg24.04+1 on Ubuntu:
CREATE TABLE xxx (a int, b int, PRIMARY KEY (a)) TABLESPACE xyz;
will store the table's base storage in xyz, but will store the table's PRIMARY KEY storage in pg_default.
This seems a weird behavior and caused a load of a six billion row table to crash as the / partition unexpectedly ran out of space.
Workaround:
Read the docs for the “using index tablespace” clause.
David J.
