4

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%?

Improve this question
0

1 Answer 1

Reset to default
2

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).

Improve this answer
2
  • 1
    Having read your answer and again reading the PostgreSQL 9.1 manual page on CLUSTER, I see that while updates are written to the same page when possible, inserts are not. In my particular case, I expect events updates to occur infrequently, so I plan to use a very high fill factor. New events that are created will almost always have a starts_at value that is in between the starts_at values of other events, so for the index I'll use a lower fillfactor. Thanks.
    – Mark Schneider
    Aug 4, 2013 at 1:55
  • My original question, then, should have been asked with regard to FILLFACTOR for an index rather than a table. Based on your answer and what I've read, I'm gathering what I would like (different fill factor for the index based on index value range) is not possible. (BTW, your answer helped me and I'd up-vote it, but I don't yet have enough reputation points.)
    – Mark Schneider
    Aug 4, 2013 at 2:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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