Why does a Mongodb collection have more size from PostgreSQL table size

Working with Data
16

I want to add my 2 cents.

Your data model is very important.

If you simply copy the SQL normalized way into your MongoDB data model, you deny yourself to have a better more efficient model. I don’t know how to explain it better that with an example of an accounting system.

SQL way

  1. One table for a transaction (date, description, customer, …)
  2. Another table for all the accounts and amounts that are credited and debited by the transaction. At least 2, for example bank account debited and account payable credited. Here in Quebec, a sale transaction touches 4 accounts, sales, account receivable, federal tax and provincial tax.

Mongo way

  1. One collection for the transaction, as above
  2. Put the debited/credited accounts and amounts in an array within the same collection.

Here, you save the space of the primary key (and its index) of the transaction that you would need in the second table to relate the credited/debited account of the transaction.

Processing wise, with SQL you need a transaction to update 2 tables and update 2 indexes (or 3 indexes if you want to quickly find transactions for a given account). In Mongo, all is done within a single document and only one index (or 2 indexes if you want to quickly find transactions for a given account).

Since I upgraded to MongoDB from SQL, I might miss some of the latest improvement made by SQL. My comment is based on the SQL schema of one of the most popular accounting system for small business.

1 Like