PostgreSQL, pg_shard, and what we learned from our failures
1–10 of 20 posts
Re: PostgreSQL, pg_shard, and what we learned from our failures
#2> The problem was that we were trying to retrofit [the Foreign Data Wrapper] API for something that it was fundamentally not designed to do.
> The PostgreSQL Extension APIs ... enable you to extend, cooperate with, or override any database submodule's behavior. We could therefore change our design to use the planner and executor hook APIs, and we found that things followed nicely from there.
Re: PostgreSQL, pg_shard, and what we learned from our failures
#3> The problem was that we were trying to retrofit [the Foreign Data Wrapper] API for something that it was fundamentally not designed to do. > The PostgreSQL Extension APIs ... enable you to extend, cooperate with, or override any database submodule's behavior. We could therefore change our design to use the planner and executor hook APIs, and we found that things followed nicely from there.
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.
Re: PostgreSQL, pg_shard, and what we learned from our failures
#4I think PG is a great database, but I don't see what makes it a good fit for a distributed system. You're going to either give up too much performance or a lot of the guarantees that make standard SQL databases interesting in the first place.
Re: PostgreSQL, pg_shard, and what we learned from our failures
#5> The problem was that we were trying to retrofit [the Foreign Data Wrapper] API for something that it was fundamentally not designed to do. > The PostgreSQL Extension APIs ... enable you to extend, cooperate with, or override any database submodule's behavior. We could therefore change our design to use the planner and executor hook APIs, and we found that things followed nicely from there.
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.
To be fair, the details of why statement one is true are still interesting though.
Re: PostgreSQL, pg_shard, and what we learned from our failures
#6I think PG is a great database, but I don't see what makes it a good fit for a distributed system. You're going to either give up too much performance or a lot of the guarantees that make standard SQL databases interesting in the first place.
(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
Re: PostgreSQL, pg_shard, and what we learned from our failures
#7I find the proposed solution for 'SELECT COUNT(customer_id) FROM orders' confusing.
They suggest placing the 3 billion order rows into three sets of 1 billion, to be distributed among three machines.
But summing the number of distinct customer IDs in each bucket doesn't give you the number of distinct customer IDs in the orders table because an ID could be in more than one bucket.
What am I missing?
Re: PostgreSQL, pg_shard, and what we learned from our failures
#8I think PG is a great database, but I don't see what makes it a good fit for a distributed system. You're going to either give up too much performance or a lot of the guarantees that make standard SQL databases interesting in the first place.
(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…
This doesn't really answer my question. I'm not asking "how can you turn PG into a distributed database", I'm asking "what do I get with that that I don't get with, eg, Cassandra"?
Re: PostgreSQL, pg_shard, and what we learned from our failures
#9Earlier quoted context omitted.
(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…
This doesn't really answer my question. I'm not asking "how can you turn PG into a distributed database", I'm asking "what do I get with that that I don't get with, eg, Cassandra"?
Structured Query Language ... that's the biggest thing you get. Also database resolved relational data.
It also allows you to present said information from other stores through a well defined interface, such as via stored proceedures that can be locked down via login/identity/application and other features typically in SQL, while being able to act as a gateway for more optimized data stores for specific types of data. Not to mention that JSON data in pgsql has gotten pretty good... I think with plv8 it gets event better. Where I think pg falls is in terms of replica sets, hot failover and automated recovery options which many nosql servers give you in the box. With PG you pretty much have to pay for a decent HA solution.
That said, I honestly really like document stores, and RethinkDB as an example offers some of the relational features of a typical SQL database while being mainly available as a document store.
Re: PostgreSQL, pg_shard, and what we learned from our failures
#10I find the proposed solution for 'SELECT COUNT(customer_id) FROM orders' confusing. They suggest placing the 3 billion order rows into three sets of 1 billion, to be distributed among three machines. But summing the number of distinct customer IDs in each bucket doesn't give you the number of distinct customer IDs in the orders table because an ID could be in more than one bucket. What am I missing?
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.