PostgreSQL, pg_shard, and what we learned from our failures

Indeed, that's confusing. You don't want to push down the count; the plan must execute "select distinct customer_id" separately on each shard, then merge the results and count.

Not sure if that's what they really meant — since their business is sharding, this shouldn't be new to them.

This isn't clear in the blog post; I'll try to clarify.

The buckets correspond to hash token ranges. For example, the database takes one row from orders_2013, hashes the customer_id value, and gets a hashed value of 1.5B. It then puts it into the second bucket orders_2013_[1B-2B[.

After the distributed shuffle, you end up with orders_cust_id[1B-2B[ on machine 2. So all orders that belong to a particular / disjoint set of customers live on one machine. You can then push down the count(distinct customer_id) to each node, and then add up the results.

We use hash token ranges in pg_shard. I'll include this clarification to the blog post.

(Ozgun from Citus Data)

It's Postgres' Extension APIs. They enable you to extend, cooperate with, or override any database submodule's behavior without forking the database: https://goo.gl/rr2EIm

As an example, let's say you leveraged planner hooks to parallelize PostgreSQL queries. When a new feature such as JSONB gets checked into Postgres, it becomes immediately available for parallel querying.

To my knowledge, no other database provides the means to extend the database to such an extent. These APIs became official in PG 9.0, and two example links from the PostgreSQL documentation are:

http://www.postgresql.org/docs/9.4/static/extend.html http://www.postgresql.org/docs/9.4/static/spi.html

For basic reads and writes the FDW APIs are quite suitable and they work well for cstore_fdw. However, to get the most out of columnar storage you also need vectorized execution that can calculate aggregates on blocks of values. The FDW APIs are not well-suited for this purpose, since they pull one row of data at a time. We've done some experiments in which we used the executor hooks to implement a vectorized executor for cstore_fdw [1] and it can give dramatic performance gains. However, the SQL support of this solution is limited as the APIs force you to build a new executor from scratch.

With sharding, the distributed executor can work on the SQL level and translate one SQL query to many SQL queries. In that case your distributed executor can focus on issues such as partitioning and merging data, while the low-level work is still being done by the regular PostgreSQL executor, so you don't have to implement it from scratch.

[1] https://github.com/citusdata/postgres_vectorization_test

Unfortunately this comment is really a good summary of the article. I guess it makes marketing sense to cut up big blog posts into two, but as it is the article really is vapid, while it could easily have been the article that makes me consider CitusDB over MongoDB for a particular application.