2

I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours).

Now, I tend to drop the index on a column of the table, update the data and then create the index again with FILLFACTOR = 100.

Is that a good practice? I'm not sure about that because I don't understannd the FILLFACTOR concept in general. By default it's 90% and they say that this is needed to perform updates and insertions.

But I tried to create an index with FILLFACTOR = 100 and then update some row and add new one and it executed fine. But I have autovacuum turned off. Running ANALYZE, however required only 3secs.

So, how does the free space left by fillfactor work?

Improve this question

1 Answer 1

Reset to default
4

Free space within the table can be used to speed up certain kinds of updates on tables, do inserts faster by avoiding the need to extend the table, and make index growth more efficient.

If your table only changes infrequently and you don't mind reindexing it (or dropping & re-creating the index) each time, that's totally fine. A fillfactor of 100 will save a little bit of I/O when scanning the index, and the spare space is no use to you unless you do inserts/updates on the indexed table.

Improve this answer
3
  • May I ask you one more question. The FILLFACTOR parameter that is less than default can reduce amount random disk accesses if the table is being update/inserted. Couldn't you give some explanation or reference explaining that. So far, it seems as some magic to me.
    – St.Antario
    Oct 19, 2015 at 14:13
  • 1
    @St.Antario It's because of MVCC - the copy-on-write lazy-overwrite model PostgreSQL uses for concurrency. In PostgreSQL's implementation old versions stay in place, rather than being moved to undo/redo logs. More in the manual - see the vacuum and concurrency control chapters.
    – Craig Ringer
    Oct 19, 2015 at 14:45
  • 1
    @St.Antario: One possible application would be for H.O.T. updates (Heap Only Tuples): dba.stackexchange.com/a/36443/3684
    – Erwin Brandstetter
    Oct 19, 2015 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.