CODEWITHSUNDEEP
postgresqlpostgresql-9.4
David S
David S

Reputation: 13851

Fillfactor recommendation for index on UUID column in PostgreSQL 9.4

Background

I have a new table in a PostgreSQL 9.4 database with a UUID primary key. This table collects information for 1 week and then is archived on AWS S3 and deletes it. It will insert an average of 40K-50K records per day. There are virtually never any updates to the table or UUID primary key. I have no ability to change this table's primary key type. The UUIDs generated and used as the primary keys are version 4 (random).

Question

If disk space is not my primary concern and I want to optimize for inserts, what fillfactor should I be using for this index. I know the default is 90%, but my hunch is that a number around 50 would help increase the throughput on the table.

Upvotes: 2

Views: 248

Answers (1)

David S
David S

Reputation: 13851

I created 7 different tables with 1 uniquely indexed uuid column. The I used fillfactors of 30, 40, 50, 60, 70, 80, 90 (the default). Then I inserted 1,000,000 records into the table (using uuid_generate_v4()). There was no significant difference in the times.

Upvotes: 2

Related Questions