Blog

My thoughts and experiments.

© 2020. Dmitry Dolgov All rights reserved.

Jsonb: few more stories about the performance

As such, there’s really no “standard” benchmark that will inform you about the best technology to use for your application. Only your requirements, your data, and your infrastructure can tell you what you need to know.

For already some time I can’t stop doing interesting/useful/weird (one at the time) benchmarks to reveal some details on how to apply document-oriented approach in the world of relational databases. Finally, I decided that I have a critical mass of those details to share in the form of blog post. So welcome to The Benchmark Club, where we’re going to discuss what it takes to create a fair performance comparison of different databases. As you may guess, the first rule of The Benchmark Club is to never share a reproducible benchmarks. But we identify ourselves as a badass engineers, so we’re going to break this rule today.

How to convert your data to jsonb?

“How to start” is always a difficult question, and jsonb isn’t an exception. Here are few notes about converting different types of data into jsonb, that someone can find useful.

Basically there are three possible cases of data conversion:

  • Convert data from inside PostgreSQL
  • Convert data from other database
  • Convert plain data outside database

The Knife

I really don’t know how I lived before without pandoc. It’s an amazing tool, that saved me from a terrible pain of latex -> MS Word convertion. If you’re writing many scientific documents in latex format, and faced with the demand to convert it into MS Word, you can understand me. It’s like a tiresome point-and-click game, which can consume unbelievable amount of time.

Pandoc can do it, and I’m not kidding. All you need is to create a source file in markdown format and copy all latex formulas. In this post you can find several details.

Compare incomparable: PostgreSQL vs Mysql vs Mongodb

As such, there’s really no “standard” benchmark that will inform you about the best technology to use for your application. Only your requirements, your data, and your infrastructure can tell you what you need to know.

NoSql is everywhere and we can’t escape from it (although I can’t say we want to escape). Let’s leave the question about reasons outside this text, and just note one thing - this trend isn’t related only to new or existing NoSql solutions. It has another side, namely the schema-less data support in traditional relational databases. It’s amazing how many possibilities hiding at the edge of the relational model and everything else. But of course there is a balance that you should find for your specific data. It can’t be easy, first of all because it’s required to compare incomparable things, e.g. performance of a NoSql solution and traditional database. Here in this post I’ll make such attempt and show the comparison of jsonb in PostgreSQL, json in Mysql and bson in Mongodb.