Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL, pg_shard, and what we learned from our failures

citusdata.com

Post reply on HN
Print view View original

11–20 of 20 posts

Re: PostgreSQL, pg_shard, and what we learned from our failures

#11

Post by ozgune »

jabbernotty wrote: post #7

I 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?

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.

Re: PostgreSQL, pg_shard, and what we learned from our failures

#12

Post by threeseed »

tracker1 wrote: post #9
mercurial wrote: post #8

Earlier quoted context omitted.

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…

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least.

And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

Re: PostgreSQL, pg_shard, and what we learned from our failures

#13

Post by qaqy »

threeseed wrote: post #12
tracker1 wrote: post #9

Earlier quoted context omitted.

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…

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least. And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

There is thins annoying thing called security with such things as trust boundaries etc. So if you are creating a mildly secure web app the only permission your web apps db user has is to execute a subset of stored procedures and that is it.

Re: PostgreSQL, pg_shard, and what we learned from our failures

#14

Post by threeseed »

qaqy wrote: post #13
threeseed wrote: post #12

Earlier quoted context omitted.

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least. And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

There is thins annoying thing called security with such things as trust boundaries etc. So if you are creating a mildly secure web app the only permission your web apps db user has is to execute a subset of stored procedures and that is it.

I guess you better let everyone know that their web platform isn't secure unless they are using a SQL database with stored procedures. Which would make PSN, Steam, iCloud, Office365, Gmail, Facebook, Twitter all insecure right ?

Generally if you have exposed your db credentials it's because something has gone very wrong i.e. your entire host has been compromised in which case stored procedures aren't going to save you.

Re: PostgreSQL, pg_shard, and what we learned from our failures

#15

Post by ozgune »

threeseed wrote: post #12
tracker1 wrote: post #9

Earlier quoted context omitted.

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…

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least. And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

I think it's the extent of SQL coverage. As an example, PostgreSQL covers all join types (inner, outer, anti, lateral) that I can think of.

Postgres has been building its SQL coverage for over a decade, and compares favorably to others databases: https://news.ycombinator.com/item?id=9018129

Re: PostgreSQL, pg_shard, and what we learned from our failures

#16

Post by mercurial »

ozgune wrote: post #15
threeseed wrote: post #12

Earlier quoted context omitted.

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least. And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

I think it's the extent of SQL coverage. As an example, PostgreSQL covers all join types (inner, outer, anti, lateral) that I can think of. Postgres has been building its SQL coverage for over a decade, and compares favorably to others databases: https://news.ycombinator.com/item?id=9018129

Well, of course it does, but "join" in a distributed database is notoriously different (and often not present at all) from "join" in a non-distributed scenario, so this doesn't make it a strong argument for Postgres.

Re: PostgreSQL, pg_shard, and what we learned from our failures

#18

Post by vladsanchez »

threeseed wrote: post #12
tracker1 wrote: post #9

Earlier quoted context omitted.

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…

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least. And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

You definitely don't know what context switching is, and that is dumber than business logic in stored procs. Ever heard of Data Access Layer or Business Layer? Ever heard about Premature Optimization?

Re: PostgreSQL, pg_shard, and what we learned from our failures

#19

Post by mslot »

dharbin wrote: post #17

Should Citus reevaluate cstore_fdw in light of this extensions API? I wonder if a foreign data wrapper is similarly ill-fitted for this application.

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

Re: PostgreSQL, pg_shard, and what we learned from our failures

#20

Post by mslot »

mercurial wrote: post #16
ozgune wrote: post #15

Earlier quoted context omitted.

I think it's the extent of SQL coverage. As an example, PostgreSQL covers all join types (inner, outer, anti, lateral) that I can think of. Postgres has been building its SQL coverage for over a decade, and compares favorably to others databases: https://news.ycombinator.com/item?id=9018129

Well, of course it does, but "join" in a distributed database is notoriously different (and often not present at all) from "join" in a non-distributed scenario, so this doesn't make it a strong argument for Postgres.

The problem with joins and reason why most distributed databases don't support them is primarily that the local machinery is complicated to implement.

Given local join machinery, a join between 2 distributed tables can be achieved by joining the partitions of the tables locally after ensuring that tables are co-partitioned and partitions are co-located. That is, if performing A JOIN B ON (A.X = B.Y), ensure that A is partitioned by X and B is partitioned by Y with the same partitioning scheme such that every partition in A has a corresponding partition in B with which it can be joined. A distributed join can then be performed (in parallel) by joining each pair of partitions and concatenating the results.

Ideally, tables are co-partitioned and partitions are co-located in advance. The article describes scenarios in which the tables need to be repartitioned during the query, which can cause a lot of network traffic. However, the amount of traffic is limited in practice since filters and projections can be applied before shuffling any data.

The great thing about implementing a distributed database on top of PostgreSQL is that the local machinery already exists, and it is very good.

Post reply on HN