3/16/2023 0 Comments Postgresql sharding![]() ![]() Relations: Aggregate on orders3 INNER JOIN order_lines3 Relations: Aggregate on orders2 INNER JOIN order_lines2 Relations: Aggregate on orders1 INNER JOIN order_lines1 Because of all the work that has been done over the last several years on join pushdown, aggregate pushdown, partition-wise join, and partition-wise aggregate, it's possible in PostgreSQL 11devel to get a query plan that looks (approximately) like this: One of the most intimidating remaining problems is that the PostgreSQL executor can't run queries against multiple foreign partitions at the same time. ![]() In particular, hash partitioning is very useful for dividing data evenly across a set of remote servers, and partition-wise aggregate allows the aggregate pushdown capability added in PostgreSQL 10 to work with partitioned tables. Most of those improvements are just as relevant for sharding as they are for partitioning, and some of them are considerably more beneficial for sharding. From that point of view, the fact that PostgreSQL 11 made huge improvements in the area of partitioning is very significant. One way to look at sharding is as a form of partitioning where the partitions might happen to be foreign tables rather than local tables. (Note that the atomic commit feature mentioned in that presentation did not make it into PostgreSQL 11.) If you haven't followed progress in this area closely, I highly recommend that you read the Built-in Sharding page which my colleague Bruce Momjian wrote up for the PostgreSQL wiki in December of 2016 as well as the very interesting slides which Etsuro Fujita, Kyotaro Horiguchi, Masahiko Sawada, and Amit Langote presented at PGCONF.ASIA 2016. ![]() Today, the question is about exactly which features are still needed. Just a few years ago, there was serious debate about whether PostgreSQL would ever have built-in sharding. Indeed, I think in a certain sense we already have. The capabilities already added are independently useful, but I believe that some time in the next few years we're going to reach a tipping point. It would be a gross exaggeration to say that PostgreSQL 11 (due to be released this fall) is capable of real sharding, but it seems pretty clear that the momentum is building. Built-in sharding is something that many people have wanted to see in PostgreSQL for a long time. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |