Date: Mon, 19 Nov 2018 12:26:09 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Fabio Pardi <f.pardi@portavita.eu>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL VS MongoDB: a use case comparison
Message-ID: <20181119172609.GR3415@tamriel.snowman.net>
References: <d43fd220-09f7-af4c-2623-44f094b15115@portavita.eu>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="6eUvXotnMb6+obQB"
Content-Disposition: inline
In-Reply-To: <d43fd220-09f7-af4c-2623-44f094b15115@portavita.eu>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--6eUvXotnMb6+obQB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* Fabio Pardi (f.pardi@portavita.eu) wrote:
> We are open to any kind of feedback and we hope you enjoy the reading.

Looks like a lot of the difference being seen and the comments made
about one being faster than the other are because one system is
compressing *everything*, while PG (quite intentionally...) only
compresses the data sometimes- once it hits the TOAST limit.  That
likely also contributes to why you're seeing the on-disk size
differences that you are.

Of course, if you want to see where PG will really shine, you'd stop
thinking of data as just blobs of JSON and actually define individual
fields in PG instead of just one 'jsonb' column, especially when you
know that field will always exist (which is obviously the case if you're
building an index on it, such as your MarriageDate) and then remove
those fields from the jsonb and just reconstruct the JSON when you
query.  Doing that you'll get the size down dramatically.

And that's without even going to that next-level stuff of actual
normalization where you pull out duplicate data from across the JSON
and have just one instance of that data in another, smaller, table and
use a JOIN to bring it all back together.  Even better is when you
realize that then you only have to update one row in this other table
when something changes in that subset of data, unlike when you
repeatedly store that data in individual JSON entries all across the
system and such a change requires rewriting every single JSON object in
the entire system...

Lastly, as with any performance benchmark, please include full details-
all scripts used, all commands run, all data used, so that others can
reproduce your results.  I'm sure it'd be fun to take your json data and
create actual tables out of it and see what it'd be like then.

Thanks!

Stephen

--6eUvXotnMb6+obQB
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJb8vIxAAoJEO1sijiDR2RVMPQP/0MfC3lwhFE+3YeVszl/R1LN
yi1b6efTI8wI2DA3jdfW8YbUmH6OkpK6bjovqlZCJqDfWUSygDHGykjVEa9hY5no
zKMVNZSp4+Rk/fgh0iBM72gpAhgIa6lbkaJePMNBZ4DjT9okoNmcGZ7ZDYE3yJVU
gfdsfK/s8BZGPByuZDKTr7hyJ9Ed1p1Qat1IDjjgmLW2MixUl4ZxHr0RsZlB10//
ww9zJv86N/f6lmbuVyh9zyqeUEds7Or9gZhOdpEzdBjUbAfYxrX3+8eDp+pjr6qP
2rRniteIvm2f3hrCWv1sOLvrILc0VL+tOaef8YLXD+eHF0ogKZAwrf/0Ym8hSB8q
BVqpFxyaL/p8zw4SF2x3Av0gU79kRWuWVa48iViVkgUCROXRPUZPrYTljzI5tnIH
KC2Ph5mo/NPB5tiRj5Zd+fLXylwH2VcrIk+xcYcorVzVhqPxy3eHqopga+sctJik
Wd80TJ1N1B/8dqcmkO1scMoUBRz0X0vOB1Z6N6mIRLsjqy8OlCsmqmIhJqWq9F4z
sfkJAy8apZGcSdKvPpXLwIKDD/t2pbe4k7cmVOh8Uv6MEqupWWoVTLiixWRGk5Jh
u9dpLBaqTbXu1XrKCavUD2+58lwRLeHqJGLYlyvvVdzAqYV9FBG/hZjNHyeI3GO2
bKCIuHk7/zqY/vwFnq6x
=Eu8l
-----END PGP SIGNATURE-----

--6eUvXotnMb6+obQB--