Re: Streaming replication and sharding

Tiemo Kieft wrote:
> We are developing an application that uses various web analytics packages (like Google Analytics) to
> run analyses on. We are currently in closed beta stadium where we don't have a lot of data in the
> database, but at some point it will grow considerably.
>
> We basically have two different sets of data, on the one hand we have raw metrics from the datasource,
> and on the other hand we have account and meta information. The former can be re-downloaded at any
> time, and will grow to quite large sizes. The latter set is the one that we really care about, and
> don't want to risk losing.
>
> Currently we plan on using streaming replication to replicate all data to at least one slave, for the
> near future this will do, since we can run some of the large (read-only) aggregation queries on the
> slave database. In the future the dataset might grow to the point where we need to start thinking
> about sharding. The analytics data can be sharded on a per-customer basis, and doesn't have to be
> replicated.
>
> Since Postgres doesn't support per-table streaming replication (as far as I can tell), the only
> solution would be to run two separate instances of postgres per server. One instance is replicated to
> all servers, and will contain account and other important information. The other instance is used to
> store analytics data. Is this a viable way of solving this problem, or are we overlooking something?
>
> The problem is not really immediate, as the dataset is currently small enough to fit on one machine
> (and replicated to a second), just want to be future proof, and get this solved before the problems
> start.

The problems I see with distributing your data across
several PostgreSQL clusters is that they become disconnected.

It will become much more difficult to keep them consistent:

You cannot have referential integrity, and if a database restore
is needed, you have to make extra provisions that you can restore
your system to a consistent state across all clusters.

You also lose the ability to join between tables that are
distributed across different databases. This can be a
perdormance problem, particularly in an OLAP scenario.

That's all I can think of at the moment.

Yours,
Laurenz Albe