patternsqlMinor
Fill factor based on index ranges
Viewed 0 times
rangesbasedfactorindexfill
Problem
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:
I plan on clustering the table on
Almost all of the events that users add will have a
Is there a way to have Postgres cluster the events table such that the fill factor for events where
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 70%?Solution
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).
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).
Context
StackExchange Database Administrators Q#47504, answer score: 2
Revisions (0)
No revisions yet.