Postgres Outperforms MongoDB and Ushers in New Developer Reality

According to EnterpriseDB’s recent benchmark, Postgres Outperforms MongoDB and Ushers in New Developer Reality

Potgres would outperform MongoDB performance but also the MongoDB data size requirement would be outperformed by by approx. 25%

EDB found that Postgres outperforms MongoDB in selecting, loading and inserting complex document data in key workloads involving 50 million records:

  • Ingestion of high volumes of data was approximately 2.1 times faster in Postgres
  • MongoDB consumed 33% more the disk space
  • Data inserts took almost 3 times longer in MongoDB
  • Data selection took more than 2.5 times longer in MongoDB than in Postgres

Find the full article here

The benchmark tools is available on GitHub: https://github.com/EnterpriseDB/pg_nosql_benchmark

Postgres 9.2 available at heroku.com

Effective immediately, Heroku is moving Postgres 9.2 into GA, which will become the new default shortly after. Postgres 9.2 is full of simplifications and new features that will make your life better, including expressive new datatypes, new tools for getting deep insights into your database’s performance, and even some simple user interface improvements. Oh, and it’s much, much faster for the most common kind of write performance pattern we see in our fleet.

You can request a version 9.2 database from the command line like this:

heroku addons:add heroku-postgresql:dev --version=9.2

Let’s dig in a bit further with the new features this version brings.

Visibility

Visibility into your data has long been a problem for many application developers.

  • How often a query is run
  • How much time is spent running the query
  • How much data is returned

You can turn on the tracking of pg_stat_statements with CREATE EXTENSION pg_stat_statements; Then run the query below and you’ll receive all of your top run queries:

SELECT count(*), query FROM pg_stat_statements GROUP BY 2 ORDER BY 1 DESC LIMIT 10;

JSON Support

Developers are always looking for more extensibility and power when working with and storing their data. Earlier this year we announced our support for hstore, a powerful key/value store within Postgres, which you can easily use within RailsDjango, and Java Spring.

With Postgres 9.2 there’s even more robust support for NoSQL within your SQL database, thanks to Andrew Dunstan, in the form of JSON. By using the JSON datatype your JSON is validated that it’s proper JSON before it’s allowed to be committed.

Range Type Support

The range datatype, thanks to Jeff Davis, is another example of powerful data flexibility. The range datatype is a single column consisting of a to and from value. Your range can exist as a range of timestamps, alpha-numeric, or numeric range and can even have constraints placed on it to enforce common range conditions.

For example, this schema ensures that in creating a class schedule we can’t have two classes at the same time:

CREATE TABLE schedule (class int, during tsrange); ALTER TABLE schedule ADD EXCLUDE USING gist (during WITH &&);

Then attempting to add data we would receive an error:

INSERT INTO schedule VALUES (3, '[2012-09-24 13:00, 2012-09-24 13:50)'); INSERT INTO schedule VALUES (1108, '[2012-09-24 13:30, 2012-09-24 14:00)'); ERROR: conflicting key value violates exclusion constraint "schedule_during_excl"

Performance

Of course, any new release of a database wouldn’t be complete without some focus on performance. Postgres 9.2, as expected, has delivered here in a big way including up to 4X improvements in speed on read queries and up to 20X improvements on data warehousing queries. In particular index-only scans can offer much faster queries because they no longer need to access disk to ensure correct results.

Nine Databases in 45 Minutes

From http://www.datanami.com/datanami/2012-12-04/nine_databases_in_45_minutes.html

 

The following video provides a crash course in nine key databases:  Postgres, CouchDB, MarkLogic, Riak, VoltDB, MongoDB, Neo4j, HBase and Redis. All in just 45 minutes.

Miles Pomeroy, Chad Maughan, and Jonathan Geddes run through each database in five minutes each, thus the title, “9 Databases in 45 minutes.” The video proceeds in efficient and occasionally amusing fashion, where a countdown clock and a gong keep the presentations terse.