Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) index on data which is queried by id. Additionally, data will be queried by the timestamp for analysis. And for this I wanted to partition the table on a monthly or yearly base.

But since my table is append only and the timestamp will be quite continuous (maybe not to a 100% but generally I won't deliberately post items from the past) shouldn't a BRIN index basically give me the same functionality as table partitioning without the trouble of manually creating sub tables and instead of triggers? (at least that's how partitioning was described on the documentation: https://www.postgresql.org/docs/current/static/ddl-partitioning.html )


Maybe some additional context - I thought about partitioning it by year or month since that is the only predicable thing. In both cases I'll have to perform queries on this table which will not include any timestamp information so for those queries I'd still be using the btree indices and in case of a pratition postgresql wouldn't be able to infer from the query which smaller table (and index) could be used.

  • Partitioning only helps for performance if all queries contain a restriction on the partitioning key. If you don't have that, queries that do not include the partitioning key will probably be slower compared to a non-partitioned table. Why do you think a btree index on the timestamp won't help? – a_horse_with_no_name May 30 '16 at 17:23
  • 1
    The queries for which I'd create a btree index would only return a hand full of data (maybe couple hundred rows at most) which could be distributed all around that table. But from time to time I'll need to run batch jobs on that timestamp data where I'll be querying 10,000+ rows in order to analyse them. And since I'll be querying rows from one timestamp until another with only minor filtering, I though brim index with sequential read of the affected pages would require less resources and might even be faster – peter May 30 '16 at 17:43
  • Hey @peter what have you settled on in the end? Could you share your experience after all this time? – Vitalii Zurian Nov 13 '20 at 15:11
  • 1
    @VitaliiZurian I left the project before I could have implemented anything. Just a hint - as far as I understood it, partitioning the table would also help you make your indexes smaller. And I've heard of occasions where an Index was too big to fit comfortably into some part of Postgres memory and Postgres therefore didn't use it. So in that case a partition definitely helps. – peter Nov 13 '20 at 20:23

Your Answer

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

Browse other questions tagged or ask your own question.