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?

András Váczi
  • 31,278
  • 13
  • 101
  • 147
St.Antario
  • 1,205
  • 1
  • 10
  • 15

1 Answers1

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.

Craig Ringer
  • 56,343
  • 5
  • 158
  • 190
  • 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 '15 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 '15 at 14:45
  • 1
    @St.Antario: One possible application would be for H.O.T. updates (Heap Only Tuples): http://dba.stackexchange.com/a/36443/3684 – Erwin Brandstetter Oct 19 '15 at 22:50