We have a sharding mechanism in our PHP framework and also Node as well, which can actually split shards which become too "hot" as determined by you. The whole system is online during splitting and only a small part if the system goes offline for 1 second before the final switchiver. No need to pre shard in the beginning, it actually splits according to usage later, into an unlimited number of shards.
Pg_shard – PostgreSQL extension to scale out real-time reads and writes
21–28 of 28 posts
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#22If you want more details about pg_shard, have a look to this blog post.
http://www.databasesoup.com/2014/12/whats-this-pgshard-thing...
It explains a bit more what it does and doesn't
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#23Very cool to see this open sourced.
Any advice on migration process? Transferring a high write-throughput postgres instance to a multi-pg deployment with pg_shard feels pretty daunting.
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#24Having a master node which delegates all the queries sounds like a single point of failure. Could this be avoided by having a failover master?
Hey, Jason from Citus Data here (one of pg_shard's authors). Yes: if you have high availability needs, PostgreSQL's streaming replication feature can mitigate this failure risk by providing a hot standby for your master; otherwise, regular backups of the master can suffice (pg_shard's metadata is stored in regular PostgreSQL tables). See our documentation page for more answers to questions like this: http://citusdata…
We have something similar in house, it is basically proxy that runs on our app-server and our app connects to. It will then parse incoming queries and route queries to the correct schema/server base on the WHERE clause (also supports INSERT). We would like to on open source it, but right now it specific to our needs (e.g hard coded hash function).
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#25I don't see any documentation around schema migrations. How is that normally solved on the Petabyte level?
Do you use Postgres at the Petabyte level?
Does it perform OK?
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#26At work we're in the midst of rolling out a sharded Postgres platform based on http://www.craigkerstiens.com/2012/11/30/sharding-your-datab... , with the sharding implemented at the application level. The biggest piece of complexity in that post is around designing the sharding in such a way that you can gracefully add more shards later. Having read the pg_shard readme, it's not clear to me how it addresses that issu…
In our experience, sharding at the application level is to be avoided.
We implemented application level sharding a few years ago when we had 1Tb of data. We now have over 20Tb of data and application level business logic is killing us.
Our pain is not DB speed, but application complexity. All of our applications (backup, web, maintenance, archiving etc) all need the business logic to traverse the sharding set.
We are planning to move to DB level sharding in January (as part of a larger application and infrastructure refresh).
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#27At work we're in the midst of rolling out a sharded Postgres platform based on http://www.craigkerstiens.com/2012/11/30/sharding-your-datab... , with the sharding implemented at the application level. The biggest piece of complexity in that post is around designing the sharding in such a way that you can gracefully add more shards later. Having read the pg_shard readme, it's not clear to me how it addresses that issu…
We'll update our FAQ with a detailed answer to this question. As a summary, the user specifies the shard and replication count as they are sharding their table. For example, if you have 4 nodes, you may pick 256 as the initial shard count. That way, you'll have ample room to grow as you add new nodes to your cluster. When you pick 256 shards over 4 worker nodes, pg_shard will create them in a round-robin fashion. Sha…
We have used sharding for managing time series data e.g. 1 shard per day. Is there a way this could work i.e. where the number of shards continually grows?
Re: Pg_shard – PostgreSQL extension to scale out real-time reads and writes
#28Earlier quoted context omitted.
We'll update our FAQ with a detailed answer to this question. As a summary, the user specifies the shard and replication count as they are sharding their table. For example, if you have 4 nodes, you may pick 256 as the initial shard count. That way, you'll have ample room to grow as you add new nodes to your cluster. When you pick 256 shards over 4 worker nodes, pg_shard will create them in a round-robin fashion. Sha…
We have used sharding for managing time series data e.g. 1 shard per day. Is there a way this could work i.e. where the number of shards continually grows?
That use case isn't yet handled by pg_shard: the plugin currently supports only hash partitioning and what you've described is range partitioning. This is certainly on our immediate feature list, as range and hash partitioning cover a variety of use cases.
However, CitusDB does support range partitioning and has a \stage command that will create new shards from incoming data. If you periodically load data that corresponds to a particular time range (hour, day, week), CitusDB can easily handle creation of additional shards during each load.