4 ms·

I have a curiosity: Some new SQL technologies, such as NuoDB, or MemSQL, chose to implement horizontal scaling by separating the data storage and transaction pr

by Nican 9y ago

I have a curiosity: Some new SQL technologies, such as NuoDB, or MemSQL, chose to implement horizontal scaling by separating the data storage and transaction processing machines. I find this awesome because the data storage can scale independently from the heavy lifting of maintaining transactions and running big queries. Additional machines can be added or removed from processing queries without having to scale the storage, and not have to worry about the complexities of data sharding, and data rebalancing. Does anyone have experience with this kind of paradigm? Can PostgreSQL do something similar?

pvh 9y ago
Postgres doesn't have anything built in today, though a single node will comfortably scale to ... let's say somewhere between 100GB and 10TB depending on your use-case. Beyond that you can do read scaling with replicas, implement something with Foreign Data Wrappers, or hand-roll a sharding solution and stay on vanilla. Alternatives to the DIY approach include open-source things like Postgres-BDR, CitusData, TimeScaleDB or closed-source single-vendor things Amazon Aurora, and RedShift.
manigandham 9y ago
MemSQL doesn't quite do that. It has 2 tiers, aggregators and leaves. Leaf nodes store the data but they also do computation of local node data while aggregators split up the original query and assemble results with final ordering and filtering. PostgreSQL can do something similar by using FDW (foreign data wrappers) to child databases, which can be other postgres nodes or different datastores entirely. It's still relatively naive with access but possible, and I'm not aware of any polished product that will do it all for you. The closest is systems like Citus, PipelineDB, TimescaleDB that work as extensions but the nodes do both compute and storage. However there are now several SQL execution engines that you can use like Apache Spark (SQL), Apache Drill, Presto, Dremio, and others that will run SQL queries and joins over several different data sources, so you can scale each layer independently. It works especially well for data lake/warehouse needs where you can run an elastically scaling group of execution nodes against files in a cloud storage bucket. Not as fast as a focused system but cheap and effective.

by Nican 9y ago

pvh 9y ago

Postgres doesn't have anything built in today, though a single node will comfortably scale to ... let's say somewhere between 100GB and 10TB depending on your use-case. Beyond that you can do read scaling with replicas, implement something with Foreign Data Wrappers, or hand-roll a sharding solution and stay on vanilla. Alternatives to the DIY approach include open-source things like Postgres-BDR, CitusData, TimeScaleDB or closed-source single-vendor things Amazon Aurora, and RedShift.

manigandham 9y ago

MemSQL doesn't quite do that. It has 2 tiers, aggregators and leaves. Leaf nodes store the data but they also do computation of local node data while aggregators split up the original query and assemble results with final ordering and filtering. PostgreSQL can do something similar by using FDW (foreign data wrappers) to child databases, which can be other postgres nodes or different datastores entirely. It's still relatively naive with access but possible, and I'm not aware of any polished product that will do it all for you. The closest is systems like Citus, PipelineDB, TimescaleDB that work as extensions but the nodes do both compute and storage. However there are now several SQL execution engines that you can use like Apache Spark (SQL), Apache Drill, Presto, Dremio, and others that will run SQL queries and joins over several different data sources, so you can scale each layer independently. It works especially well for data lake/warehouse needs where you can run an elastically scaling group of execution nodes against files in a cloud storage bucket. Not as fast as a focused system but cheap and effective.