Set FILLFACTOR for primary key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Set FILLFACTOR for primary key

Ed Behn
I have a table where I INSERT rows and DELETE rows. However, they are UPDATEd. Therefore, I've set FILLFACTOR to 100% for all of the indexes. However, I can't figure out how to set the FILLFACTOR to 100 for the primary key. I think it's just a matter of syntax that I can't seem to wrap my head around. 

My code looks like:

ALTER TABLE table ADD CONSTRAINT table_pkey PRIMARY KEY (col1, col2) USING INDEX TABLESPACE ts_indexes;

How should this be changed to set the FILLFACTOR or am I missing something fundamental about primary keys?

            -Ed 
Reply | Threaded
Open this post in threaded view
|

Re: Set FILLFACTOR for primary key

Peter Geoghegan-4
On Wed, Oct 9, 2019 at 9:37 AM Ed Behn <[hidden email]> wrote:
> I have a table where I INSERT rows and DELETE rows. However, they are UPDATEd. Therefore, I've set FILLFACTOR to 100% for all of the indexes.

I don't recommend it, since concurrent inserts that arrive slightly
out of order (e.g. auto-incrementing sequence values) can now split
pages a second time -- they have no wiggle room.

> However, I can't figure out how to set the FILLFACTOR to 100 for the primary key.

There is an ALTER INDEX for this. It works with primary keys, even
though they're generally though of as constraints, not indexes.

--
Peter Geoghegan