Postgresql – PGPool for Postgres load balancing, where to sit it

In principle, 2-3TB should be possible to do without resorting to a shared-nothing architecture, but Vanilla PostgreSQL still does not have good parallel query functionality. All you will achieve by replication is the ability to apportion queries out to individual nodes. I don't believe that PostgreSQL supports federated queries out of the box and I would be very surprised to find that Tableau has direct support for sharding at the client. I'd guess that PostgreSQL won't perform very well on a data set this big.

There is work in progress on a parallel query facility for PostgreSQL, but AFAIK it's not included in 9.1. My gut instinct is that some release in the next few years will include this feature but it's not quite there yet. I don't see evidence of much effort being put into a federated query facility.

Another option: SQL Server

Unless you're married to Postgres you might find that SQL Server provides a cost-effective option to Greenplum for a 2-3TB data set. It is licenced by the socket rather than the core, so a loaded 2-4 socket Xeon or Opteron box gives good bang for buck as a platform. For a smaller user base I believe you can still licence Enterprise Edition by CAL as well.

A couple of 24/25 disk arrays on a high-end RAID controller will do sequential reads fast enough to saturate a PCIe-x8 slot (2GB/sec). A simple table scan query with SQL Server will handle data at this rate without using much CPU (obviously depending on the actual computation), so you have some headroom to add controllers and arrays if you want faster I/O.

SQL Server also comes with a passably good set of B.I. tooling, including an OLAP server. Tableau is not cheap, around £1,800/seat last time I looked. Depending on the number of users you may find that the 'good-enough' tooling that comes with SQL Server could offset the costs of the DB licence anyway. Most third party reporting tools also play nicely with SQL Server.

Disclaimer: I'm not any sort of die-hard Microsoftie but I have done a lot of B.I. work with SQL Server and Oracle over the past decade or so. SQL Server is actually quite a good B.I. platform.

You're increasing num_init_children, but there's no sign you've set the PostgreSQL server backend's max_connections high enough to allow that number of connections from PgPool.

Please see the PgPool-II documentation to learn more about configuring PgPool-II. You might find this article useful.

You really don't want thousands of parallel queries to the backend database. What you need is for PgPool to queue them up and send them a few hundred at a time to PostgreSQL. Otherwise you'll tend to see terrible performance; see number of database connections in the Pg wiki. So you need to limit the number of back-end connections PgPool-II creates, while allowing lots of front-end connections. That means you want a low num_init_children. Personally I'd use something like:

num_init_children = 100                        
max_pool = 1

because, per the documentation:

If more than num_init_children clients try to connect to pgpool-II, they are blocked (not rejected) until a connection to any pgpool-II process is closed.

This is what you want for good performance.

That said, I use PgBouncer for this. I find it much simpler to work with, and it tends to perform very well. It also offers transaction-pooling mode, which can be really useful when you have applications that don't close connections, instead leaving them idle for ages. If you're not using PgPool's cluster features I strongly recommend just using PgBouncer, it's way simpler.