Postgres - indexes partitioning

Question

I have psql (12) database installed on ultra fast (and expensive) SSDs - works great there However my DB is rapidly growing so instead of investing fortune in SSDs expanding - Im more thinking to install cheaper disk volume based on standard (magnetic) HDDs.

I read about table partitioning and this is what might help here, but I coudnt find information what about indexes? Can those be also partitioned / stored on 2 different volumes, depending where table partition reside ?

This is quite important to me because indexes are almost 30% size of biggest table

Appreciate your comments / advice

"Table partitioning" is a fancy name for "split a table into multiple tables". And since indexes are created per table then each partition has to have its own index. And so "yes", indexes can be partitioned as well. — freakish
– freakish, Commented Sep 8, 2020 at 7:04

Laurenz Albe · Accepted Answer · 2020-09-08 08:42:41Z

1

The documentation says:

Create an index [...] on the partitioned table. [...] This automatically creates one index on each partition, and any partitions you create or attach later will also contain the index.

So yes, indexes will be partitioned just like the table.

answered Sep 8, 2020 at 8:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maciej Over a year ago

Thanks for reply. So in my case - does it mean that index will be store in 2 tablespaces ? (SSD & magnetic HDDs volumes) ?

Laurenz Albe Over a year ago

You will have to move the appropriate index partitions to the desired tablespace. That is not done automatically, but you can do it manually.

Collectives™ on Stack Overflow

Postgres - indexes partitioning

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related