8

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 )

Update

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.

4
  • 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?
    – user1822
    May 30, 2016 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, 2016 at 17:43
  • Hey @peter what have you settled on in the end? Could you share your experience after all this time? Nov 13, 2020 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, 2020 at 20:23

1 Answer 1

1

In this particular scenario, the key distinction between employing BRIN (Block Range INdexes) and table partitioning lies in their impact on table size. Table partitioning effectively reduces the overall size of a table by dividing it into smaller, more manageable segments. This contrasts with BRIN, which does not alter the table size. The significance of table size reduction becomes particularly evident when considering the growth of indexes, especially btree indexes. In cases where indexes swell beyond the capacity of memory, performance issues may arise, not only for btrees but for other types of indexes as well.

Moreover, the management and operational aspects of databases also differ markedly between the two approaches. Large tables can significantly slow down maintenance tasks such as VACUUM and ANALYZE. These tasks, along with schema modification operations, might not only run slower but also require longer locks on the tables, which can impede the performance of concurrent operations and affect the overall efficiency of the database system.

On the other side of the coin, while table partitioning does alleviate some of these challenges by reducing table size and potentially improving performance, BRIN indexes would still be necessary even with partitioning. This is because the granularity of filtering provided by BRIN indexes is much finer than the coarse filtering achieved through partition-based constraint exclusion. In essence, BRIN indexes complement table partitioning by offering an efficient way to navigate the partitioned data, ensuring that queries are executed with optimal precision and speed.

Thus, while both BRIN and table partitioning offer significant benefits in managing large datasets, they serve different purposes and are often used in conjunction to balance the database's performance, manageability, and operational efficiency.

Your Answer

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

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