Today I Learned

A Hashrocket project

Ready to join Hashrocket? Find Openings here and apply today.

PostgreSQL Indexes on Partition tables

When we CREATE INDEX in a partitioned table, PostgreSQL automatically “recursively” creates the same index on all its partitions.

As this operation could take a while we can specify the ONLYparameter to the main table index to avoid the index to be created on all partitions and later creating the same index on each partition individually.

CREATE INDEX index_users_on_country ON ONLY users USING btree (country);

CREATE INDEX users_shard_0_country_idx ON users_shard_0 USING btree (country);
CREATE INDEX users_shard_1_country_idx ON users_shard_0 USING btree (country);
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.