My experience with MongoDB

For starters, while a schema­less solu­tion makes early tin­ker­ing more fric­tion­less, some­times you want the checks and data pro­tec­tion that a schema can provide. Typed columns also allows rela­tional data­bases to make opti­miza­tions that Mon­goDB can’t.

.. The docs sug­gest run­ning it on at least three ded­i­cated servers with ample resources each. This was a bit much for me so I ran it on a sin­gle server which it shared with the appli­ca­tion. As a result my appli­ca­tion was slow and the server crashed peri­od­i­cal­ly. Now you could crit­i­cize me for not fol­low­ing the rec­om­mended pro­ce­dure, and you’d be right, but under­stand that when I switched to Post­greSQL, with­out increas­ing the hard­ware capac­ity at all, all of my per­for­mance and sta­bil­ity prob­lems went away. Mon­goDB demanded too much for less per­for­mance and essen­tially the same queries

.. Besides, unless your sys­tem is par­tic­u­larly write heavy rela­tional data­bases can use repli­ca­tion to scale out any­how. Mon­goD­B’s model really isn’t an advan­tage unless you are solv­ing a write-heavy, Big Data problem.1 Until you reach that scale, it’s actu­ally slower than the alter­na­tive.

Indiscriminate use of CTEs considered harmful

However, there is one aspect of the current implementation of CTEs that should make you pause. Currently CTEs are in effect materialized before they can be used. That is, Postgres runs the query and stashes the data in a temporary store before it can be used in the larger query. There are a number of consequences of this.

..  After some analysis and testing, the simple act of inlining two CTEs in the query in question resulted in the query running in 4% of the time it had previously taken. Indiscriminate use of CTEs had made the performance of this query 25 times worse.

Creating Pivot Tables in PostgreSQL Using the Crosstab Function

SELECT *
FROM crosstab( 'select extract(month from period)::text, subject.name,
             trunc(avg(evaluation_result),2)
     from evaluation, subject 
     where evaluation.subject_id = subject.subject_id and student_id = 1
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);