[Fixed]-Greenplum vs PostgreSQL

Greenplum is an MPP adaption of PostgreSQL. It’s optimized for warehousing and/or analytics on large sets of data and would not perform that well in a transactional environment. If you need a large DW environment, look at Greenplum. If you need OLTP or smaller DB sizes (under 10TB) then look at PostgreSQL.

Greenplum is an MPP analytical (OLAP) DBMS. PostgreSQL is an OLTP DBMS. And in general, there is not a single solution on the market that can be good at both OLAP and OLTP at the same time, you can find my thoughts on it here

The WebApp backend will always create OLTP workload. Greenplum has a big overhead for transaction processing as it is a distributed system, so don’t expect this to deliver you more than 500-600 TPS. Postgres in contrast can go to hundreds of thousands of TPS with the right tuning.

In contrast, when you need a OLAP workload, Postgres can offer you only a single host processing, no partitioning with dynamic partition elimination, no compression, no columnar store. While Greenplum would be able to crunch your data in parallel on the cluster.

So the solution you are looking for is a typical data warehouse case – use OLTP solution for high transactional workload, extract the data to the DWH with ETL/ELT, and then run complex data crunching queries on it

At the moment both PostgreSQL and Greenplum are open source products, so you are free to chose any of them, but of cause PostgreSQL community is bigger ATM

Since Greenplum utilizes parallel processing, there will be overhead with running lots of tiny read queries as the master node needs to communicate with the underlying data nodes to retrieve an answers to all these queries. For a query taking milliseconds, expect an order of magnitude slower performance for Greenplum.

If you are looking for a PostgreSQL-based data warehousing solution, I would also look at GridSQL. It is a parallelization layer over multiple PostgreSQL instances, and is free and open source.

Like mentioned in other comments, it will not perform well for many small millisecond queries, but will help you greatly for long running queries. GridSQL also will not include DW optimizations like columnar storage that Greenplum has, but you can take advantage of constraint exclusion partitioning (ex: subtables by date range) combined with parallelism to get your query results faster.

You can also even use it on a single multi-core server, as PostgreSQL will only use a single core when processing a query.

I think Greenplum takes better advantage of parallel processing. It’s based on PostgreSQL, though.

Greenplum has a free community edition. You can always download and test in your own environment.

If any data crunching takes longer than an hour, you’ll get linear performance boosts for every core you add. It’s not really worth the effort for anything that takes less time to crunch through.