DEV Community

Discussion on: The Ultimate Postgres vs MySQL Blog Post

elmuerte profile image
Michiel Hendriks • Edited on

You forgot to mention partial indexes. Every index you create decreases performance a bit. But being able to create small indexes which could fit in a few memory pages adds a lot of performance when you use them.

In our case we have a huge table with order data. For some report we needed to query on a date range of an optionally filled timestamp field. The index which would only contain non-null rows was only a few kB for a table containing millions of rows. If all null rows would have been included it would have been quite a lot of megabytes instead.

PS, Besides JSON, PostgreSQL also supports XML as datatype, or simply process text to XML.