Postgresql vs mongodb

http://blog.pingoured.fr/index.php?post/2012/05/20/PostgreSQL-vs-MongoDB

by ibotty • 14 years ago

55 50 14 years ago

blog.pingoured.fr

thibaut_barrere• 14 years ago

One thing that I've never seen compared so far is the RAM/disk consumption for the same dataset.

Did anyone make a comparison of this kind, out of curiosity?

buster2• 14 years ago

I must say, those boxcharts are a horrible way to represent the data.

adeelk2• 14 years ago

Why do you say that?

buster2• 14 years ago

don't know where to start..

  1. it's confusing (atleast for me)
  2. a lot of his charts don't start with 0, this leaves the impression that e.g. mongodb is 3 times faster then PG, when in reality the difference was like 5%
  3. one scollable page with multiple pictures would have been much better, imo
  4. About the page itself: i don't see how many times the benchmarks were run.

The conclusion shows that even the writer is mislead in interpreting the charts... "PostgreSQL performs worse than MongoDB in the retrieval queries but the time it takes is negligible (we are way under 1 second)" What does it matter that it's one second?! That's stupid. His charts show for example for get_email 0.001 seconds versus 0.005 seconds. That's 5 times faster! Saying "that's so small, i don't care" sounds bad. Unless the author meant some other charts...

Also, when i'm at it, i am not sure if this is a good benchmark at all. 0.001 seconds is an extremely small value to measure. I doubt it is usefull for comparison. But it may be ok, if this is would be some "real-world" example where queries don't take long. But the benchmark is not "real-world". At all.

I would have liked to see a more realistic one (the dataset is ok), with multiple concurrent queries and also with measurement of CPU and memory consumption..

moe• 14 years ago

If you insist on using a general purpose database instead of a dedicated search engine (SOLR, sphinx) then you should at least read the manual; http://www.postgresql.org/docs/9.1/static/textsearch.html

llimllib• 14 years ago

(Which existed in 8.4 as well: http://www.postgresql.org/docs/8.4/static/textsearch.html . Not sure why he's not using version 9)

jacques_chester• 14 years ago

Doesn't MongoDB automatically index columns? Wouldn't that account for a lot of the difference?

adeelk2• 14 years ago

No, it doesn’t.

http://www.mongodb.org/display/DOCS/Indexes#Indexes-Basics

ibotty3• 14 years ago

of course that post is not best practice for a sql-database search and not optimized at all. what it shows is that even then, on a single host, a sql database is within limits to nosql databases.

zomby• 14 years ago

Breaking news: an old version of Postgres used badly is slow.

ibotty3• 14 years ago

breaking news: even an old version of postgres used badly is not that slow.

gbog3• 14 years ago

> a NoSQL database has a much more flexible structure

In some cases you really have too much diversity of data structures and then it a NoSQL solution can be helpful. An example can come to mind: a notification engine with tens of diverse notification format, each with their variable data and their localized display templates.

But if your data is well structured, like in this example, it is not true that SQL is not flexible. I have used PostgreSQL to manipulate on the fly the relatively big UNIHAN list (50 000 Chinese characters with their attributes) and could rearrange data in the way I wished, creating, updating columns, creating grouped tables, and I got a working database for an online dictionary without any pain.

If I would have to run scripts for each SQL command I ran, it would have taken much longer, and I would have the easy begin, modify, check, commit or rollback loop.

I sometime wonder if enough people who use SQL use it through the command line interface.

mickeyp• 14 years ago

> In some cases you really have too much diversity of data structures and then it a NoSQL solution can be helpful. An example can come to mind: a notification engine with tens of diverse notification format, each with their variable data and their localized display templates.

Actually the relational database solution to that problem -- an arbitrary mix of various data types -- is to use a table with a variety of columns of different data types. For instance: Str1, Str2, Str3, Str4, Int1, Int2, Int3, etc. -- for as many as you need to store what you want to store. Obviously they should all be nullable and you would need a PK (and FK) to whatever it is you are storing it with. Next, you build views on top of this for each different type of data stored in this mix table, and in this view you then name each column sensibly. Using triggers on the view (or without: Oracle can figure out most inserts if the SELECT query is trivial) you can insert data into the mix table as well.

damncabbage• 14 years ago

I take it you don't use foreign key constraints, then.

gbog3• 14 years ago

You mean materialized view, right?

Anyway you just demonstrated my point: in this case NoSQL may be a more flexible (simple) option.

Another relational solution is to use a single entity-attribute-value table and drop the strong typing.

clu3• 14 years ago

i think the dataset is way too small, only 166K rows? That's not really interesting. if you want to compare, make it at least a few millions

salimmadjd• 14 years ago

166k is kids play. At this amount we will not see any measurable advantages among serious contenders. The key data is how these perform on multiple servers, with sharding and master slave configurations, etc.

sciurus• 14 years ago

The author is developing a web interface to mailing list archives [0]. In that context 166,672 emails sounds pretty substantial.

[0] https://fedorahosted.org/hyperkitty/

sasha-dv• 14 years ago

What exactly is the author's conclusion? To ask more questions?

It seams to me that in a post named X-vs-Y you should arrive at some sort of conclusion. Say, X is the way to go or something like that.

I'm not a db geek and I'm not pro-NOSQL or pro-RDBMS. Having no horse in the race, and a very modest knowledge of DBMS, I wonder what would be the best choice in this specific use case (building mailman archive)?

lysol• 14 years ago

@charlie, I did not change anything to the configuration of my PostgreSQL, so I use the defaults provided by RHEL.

He didn't even tune his database server. That automatically invalidates this comparison.

tferris• 14 years ago

As much as I like Mongo and comparisons to other DBs I doubt that your comparison does really help anyone:

=> I found it hard to get your message by just skimming through the blog post: the first results lack measuring units, is it request per second or total request? The graphs were even more confusing—they do not give the reader quickly the relevant information and the conclusion isn't clear.

=> I missed information if you set up respective indexes for querying Mongo (correct me if I am wrong). And I don't know if you used the new hstore feature on Postgres.

=> This is still a micro benchmark with limited expressiveness. The overall use case, data model and queries are directd rather towards the NoSQL and general full text search capabilities. Take a use case with lots of joins/links, random reads and writes and we will have a totally different picture (thus, your title is misleading).

So if one wants to make a comparison between Mongo and Postgres I would like to see how Postgres' hstore and the new JSON datatype feel in terms of daily usage and performance compared to Mongo (because these two new features compete directly with NoSQL DBs and the latter one with Mongo in particular).

antirez3• 14 years ago

The first law of DB comparisons says: in order to compare two database systems a deep understanding of both systems is required, however the more expert an user is the less likely is to compare two systems (he will just pick what seems more suitable for the task) thus almost all the comparisons attempts are garbage.

sigil• 14 years ago

Hey, at least he published his benchmark code on github, where experts in either system are free to fork and improve them. That's already better than most of the benchmark comparisons I see.

He really should include his mongo and pg config files as well though.

55pts• 14 years ago

Antirez, I have a great deal of respect for your work, but calling somebody else's work "garbage" is not cool.

Constructive criticism in order to make the benchmark more relevant would've been more helpful.

antirez3• 14 years ago

Hi 55pts, don't take me wrong I understand what you mean, but it is like to say that because I happen to contribute to some software that is popular I don't have the right to express freely my thoughts.

garbage here means: not very useful. More politically correct people are going to use a different word, but the concept is the same. DBs comparison / benchmarking is hard and I read many blog posts about people that apparently don't even have a solid understanding / experience with one of the systems (sometimes with both the compared ones), so this articles are really not going to help but to confuse other users.

pbh• 14 years ago

Actually, I thought his comment was quite delicate.

I used to think that DB comparisons were bad because of vested interests, like Oracle refusing to allow anyone to benchmark them. But I now think that Antirez's view is much closer to the truth: poorly informed people can't set up benchmarks correctly because they don't understand the underlying DB model and possible optimizations, and well informed people can't set up benchmarks because they can't think of a fair benchmark that doesn't explicitly handicap one model or set of optimizations versus the other.

(Of course, in this case, the comparison is pretty bad. Other commenters have noted that an inverted index is more sensible here and that Postgres' text search support should have been used. But the big issue to me is that the graphs are incredibly misleading, with totally different units on the y axis, so that a 0.004 second win for Mongo looks like as big a win as a 3 second win for PG.)

SkyMarshal• 14 years ago

An expert engineer like antirez (or Linus, etc) calling your work garbage actually is constructive criticism, if you take it that way and act on it, instead of feeling entitled to some additional work or time contribution from them.

shubber• 14 years ago

Which does suggest an interesting activity: a DB shootout. Presented is a set of data and specified queries. Build solutions against a database you're fond of and show us how fast is can be.

Would have to be something like a public github repo, because valid solutions would need to at least include DB configs and possibly post-query data massage.

Something like jsperf.com but for persistence.

daemon13• 14 years ago

Why didn't you use PG 9.1 / 9.2 with new kernel?

Also, why old Mongo?

Would love to see benchmarks of most recent stable.

ibotty3• 14 years ago

(not very informative, but..) i am not the one who did the blog post. i just found it interesting (reasons stated somewhere else). so i cannot answer any questions about it.

masklinn3• 14 years ago

> MongoDB being a NoSQL database using dictionary has a much more flexible structure.

Not really, Postgres has hstore[0] which provides query-able schemaless key:value storage.

Furthermore, testing is done with 8.4, current version of Postgres is 9.2 and has further performance improvements over 8.4 (and more flexibility for "unsafe" data storage in the form of unlogged tables).

Finally, no note on the configuration of Postgres and whether it's been tuned correctly, only leaving the inference that he's using the default configuration... which isn't exactly performant.

[0] http://www.postgresql.org/docs/9.0/static/hstore.html

lobster_johnson2• 14 years ago

He was using the default config, says so in the comments section.

He also says in the comments that he did not know you could index case-insensitively in Postgres.

This article is completely worthless.

fdr3• 14 years ago

Not completely worthless: it does say something about the ease of getting good performance without having to know much about the ecosystem. But it doesn't speak to even the first "easy" 80% of what one would do to optimize performance, though.

I appreciate this work with numbers, and the code to run it. I'm sure it can be adapted to take on these two suggestions.

masklinn3• 14 years ago

> This article is completely worthless.

Well it basically says that a worst-case situation Postgres still manages to keep up with MongoDB.

lusr• 14 years ago

Performance with concurrent requests is also not addressed, which seems crucially important for a web application and the area I'd expect to see the most interesting differences.

scorpioxy• 14 years ago

Do you happen to know the performance characteristics of hstore?

I use mongo for some analytics but it doesn't perform well on big datasets(not mongo's fault, memory on the machine is limited). I was wondering if hstore can replace it.

I read somewhere on the mailing list that hstore doesn't perform well on big datasets( > 500,000 i think). Is that still true?

If there are no benchmarks, I am going to do them myself.

fdr3• 14 years ago

As is always the case, disk I/O rather sucks for performance. On the other hand, Postgres's I/O scheduling -- while far from state of the art compared to proprietary databases -- is probably better than leaving it to the operating system entirely, particularly for writing.

500,000 small hstores should perform just fine. We have tables with millions or quite possibly billions of not-entirely trivially sized hstores sitting around on a pretty unremarkable machine with a few indexes.

Hstore's storage format is probably not tuned as carefully (it's basically text) as bson, and yet its indexing can be richer (GIN/GiST). Binary formats can be overrated, though, especially when the amount of meta-information is quite or people use them with strings. However, it's an interesting area of optimization that I think will happen because document storage in Postgres has become something people are actually using now.

amalag• 14 years ago

Hstore's query is only string matching, you cannot do the rich querying which mongodb allows.

masklinn3• 14 years ago

Not sure what you're trying to say, but hstore's querying most definitely isn't "only string matching" no. hstore values, written in queries, use string-serialization for embedding in SQL. Which is very different. And this serialization is only needed when adding content to an hstore or performing set-operations on hstores, you can perform queries on hstore fields pretty much directly via the `->` operator:

    SELECT  id
    FROM    Table
    WHERE  (hs->'size')::INT >= 42
    AND    (hs->'name') IS NOT NULL

Luyt• 14 years ago

Somewhat more general and with a humorous undertone:

http://www.mongodb-is-web-scale.com/

Not only the video, but a transcript too.

dr_rezzy• 14 years ago

Why did mongodb drop 30 docs from its index? Did PG drop any? Does this not matter?

arthurprs• 14 years ago

I know this a specific test, but some things to consider:

The test dataset is negligible for such a powerful machine.

Test ran with a recent MongDB and an outdated Postgres.

MongoDB uses all available memory, Postgress need to be manually tunned in the config files.

taligent• 14 years ago

What are you talking about ?

He is using an outdated version of MongoDB as well.

Postgresql vs mongodb

Related Stories