Disadvantages of PostgreSQL
- Performance:
For simple read-heavy operations, PostgreSQL can be an over-kill and might appear less performant than the counterparts, such as MySQL.
..
When Not To Use PostgreSQL
- Speed:
If all you require is fast read operations, PostgreSQL is not the tool to go for
..
- Replication:
Unless you are willing to spend the time, energy and resources, achieving replication with MySQL might be simpler for those who lack the database and system administration experience.
PL/V8 Language in Postgres
The V8 JavaScript engine from Google is available as an embedded language.
Not part of core or contrib; needs to be built and installed separately.
PostgreSQL Select Filters
But what happens when Jeff from Accounting wants to know how many people ordered five specific products as a column list? In the old days, we might do something like this:
SELECT sum(CASE WHEN product_id = 1 THEN item_count ELSE 0 END) AS horse_mask_count, sum(CASE WHEN product_id = 7 THEN item_count ELSE 0 END) AS eyeball_count, sum(CASE WHEN product_id = 13 THEN item_count ELSE 0 END) AS badger_count, sum(CASE WHEN product_id = 29 THEN item_count ELSE 0 END) AS orb_count, sum(CASE WHEN product_id = 73 THEN item_count ELSE 0 END) AS memebox_count FROM sys_order; horse_mask_count | eyeball_count | badger_count | orb_count | memebox_count ------------------+---------------+--------------+-----------+--------------- 59870 | 59951 | 59601 | 59887 | 60189
Gross.
As a DBA, I’ve seen more of these than I can reasonably stand, and hate them every single time. It’s not the use of the
CASE
statement that is so irksome, but the micromanaging methodology necessary to reduce the count to zero for unwanted items. WithFILTER
however, this query changes quite a bit:SELECT sum(item_count) FILTER (WHERE product_id = 1) AS horse_mask_count, sum(item_count) FILTER (WHERE product_id = 7) AS eyeball_count, sum(item_count) FILTER (WHERE product_id = 13) AS badger_count, sum(item_count) FILTER (WHERE product_id = 29) AS orb_count, sum(item_count) FILTER (WHERE product_id = 73) AS memebox_count FROM sys_order; horse_mask_count | eyeball_count | badger_count | orb_count | memebox_count ------------------+---------------+--------------+-----------+--------------- 59870 | 59951 | 59601 | 59887 | 60189