Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes).

Since I'm somewhat experienced with vanilla PostgreSQL (but with 10GB base), I was considering trying the 9.1 new synchronous replication to achieve load balancing. The objective is to get Tableau be very responsive. Thus, I could avoid costly MPP GreenPlum, AsterData and others.

However, I was wondering if : - going straight to MPP Databases, GreenPlum for example, was a good idea ? (for 2-3 TB, is Greenplum really needed?) - once Synchronous Replication is up and running, how shall I configure my applications ? Is the load balancing done by the master or is another solution need (Pgpool for example) ?

Thank you for you insights !

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Hi. Frankly speaking, going into the MS direction is not something I'm comfortable with. Do you mean that SQL Server can span a single request accross multiple cores ? Nevertheless, using PostgreSQL and a suitable RAID controller and partitions and tablespaces located on several disks could do it right ? I'm really new on disk arrays and RAID controllers actually... –  SCO Nov 20 '11 at 18:40
    
@SCO - Yes, it can spread a single request across multiple cores - it supports query parallelism, which is still WIP on PostgreSQL. I don't think that PostgreSQL does query parallelism even across partitions. All the documentation points to this being some way off in the future. –  ConcernedOfTunbridgeWells Nov 21 '11 at 12:18
    
@SCO - having said that, there are one or two third party sharding systems for PostgreSQL (e.g. plproxy) that might work for you if you want to try setting up a cluster. I don't know how well they would work for a data warehouse system in practice. –  ConcernedOfTunbridgeWells Nov 21 '11 at 12:27
    
@SCO - FWIW, you will need parallelism to get decent performance over a 1TB data set, and you will need a fast disk subsystem. If you're not familiar with setting up and tuning disk subsystems for speed you might want to do your homework - maybe ask another question here. –  ConcernedOfTunbridgeWells Nov 21 '11 at 14:29
1  
@SCO - Note that you will need Enterprise Edition to get the VLDB goodies with SQL Server. –  ConcernedOfTunbridgeWells Nov 21 '11 at 22:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.