Postgresql – Fill factor based on index ranges

fill-factorpostgresql

I'm designing a Postgres database for an events app. The app lists events sorted by when they start. Initially the app displays only 30 events. As users scroll through the list of events, more events are fetched from the database. In reduced form, the queries (depending on the direction in which the user is scrolling) are:

SELECT ?
FROM events 
WHERE starts_at >= ? 
ORDER BY starts_at 
OFFSET ? LIMIT ?

SELECT ? 
FROM events 
WHERE starts_at < ? 
ORDER BY starts_at DESC 
OFFSET ? LIMIT ?

I plan on clustering the table on starts_at with a fill factor of about 70%, and expect I'll want to run the cluster command periodically to maintain performance.

Almost all of the events that users add will have a starts_at value in the future. Therefore, although specifying a 70% fill factor for events which start in the future makes sense, using a 70% fill factor for events that started in the past seems like a waste of disk space.

Is there a way to have Postgres cluster the events table such that the fill factor for events where starts_at < CURRENT_TIMESTAMP is 99% while the fill factor for events where starts_at >= CURRENT_TIMESTAMP is 70%?

Best Answer

Unless you plan on regularly updating portions of your table, I'm curious why you would set your FILLFACTOR on the table to anything other than the default of 100%?

Regarding your question about different fillfactor settings for the same table, I don't think that is possible. If you were planning to set the fillfactor per index, then that might have some benefit -- but if you are only inserting then set the fillfactor to 100% (or close to it).

Related Solutions

SQL Server Fill Factor – Behavior of Data in Indexes

Fill factor only comes into play when an index is created or rebuilt. It is the amount of consumption for the index of the leaf level pages that are filled during these operations. (see the note below for more clarification on affected page levels)

When there is a DML command to data (INSERT, UPDATE, and/or DELETE), it will happen to the corresponding affected indexes. In other words, if you have a page that is 20% filled and you insert data into that page, the page will contain more than 20% of the data (let's say 35% just for example's sake). Do another insert, now the page is 64% filled. Rebuild the index, and the leaf level pages will now relatively contain the space percentage that you specify (or implicitly the default value for the server).

(Note, when you don't specify PAD_INDEX to be ON, fill factor is only applied to leaf level pages. But when you set PAD_INDEX = ON, the fill factor will be taken into account for intermediate-level pages of the index. The default is OFF)

The reason to adjust fill factor (instead of using the default 100/0) is so that you minimize page splits when inserting or updating data. But keep in mind, nothing is for free. The lower the fill factor, the more space data would normally take up. If you keep an 80% free page space for your indexes, they will consume a relatively larger amount of disk space, which can lead to more reads.

From my understanding, when the data is inserted there will be roughly 20% of data in the pages. When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?

When data is inserted it will insert into the appropriate indexes at the appropriate page. This could and most likely would very well cause the page consumption to be higher than the fill factor.

A page split will happen when new data is added to a full index page. SQL Server will then split the page and approximately place half of the data from the full page into a new page. Again, fill factor does not come into play here.

A legitimate reason to lower fill factor would be to minimize page splits, therefore minimizing index page fragmentation.

Sql-server – How to set the index fill factor back to server default

Through testing, it seems as though to get back to the default fillfactor on an index you need to drop and recreate it. When you do an ALTER INDEX ... REBUILD, any unspecified parameters will remain the same in the index's metadata unless otherwise explicitly changed in the ALTER INDEX command.

drop index IX_YourIndex
on YourTable
go

create index IX_YourIndex
on YourTable(YourKeyColumn)
go

To verify this:

select
    name,
    fill_factor
from testdb.sys.indexes
where name = 'IX_YourIndex'

fill_factor should be set to 0 to denote that it is using the instance's default value. (Note: 0 is for the default value, even if the instance configured fill factor is set to 95)

Best Answer

Related Solutions

SQL Server Fill Factor – Behavior of Data in Indexes

Sql-server – How to set the index fill factor back to server default

Related Question