In the meantime, I have scanned the manual for PostgreSQL 9.4 and there are a few things I was not able to find in the manual, my apologies if I missed it:
1. does PostgreSQL have parallel query capability like MS-SQL 2008+ and Oracle 11g+ ? Or does a single query only run on 1 CPU?
The latest stable version will execute a single query on single CPU. Next version (9.5) will have some limited parallel query execution facilities.
2. does PostgreSQL have ability to apply query optimizer hints to individual queries - such as use a particular index, join type, join order, plan guides, etc ?
No it's not available. In most (but not all) cases PostgreSQL query optimizer will select reasonable good plan.
3. does PostgreSQL have Column-Store capability?
In community version - no, but there are some external addons available which add column storage (however a bit limited).
4. does PostgreSQL have anything resembling Microsoft SQL Server Profiler Trace or Extended Events ?
5. does PostgreSQL have a database backup capability such that not a single transaction is lost in case of hardware failure? Some of our target databases are several TeraBytes in size with several hundred concurrent connections and transactions are financial in their nature. So we need extremely robust backup/restore capability, 100% on-line.
Yep PostgreSQL could use built-in synchronous replication with zero committed transaction lost after failover.
6. does PostgreSQL support NUMA on Intel based X64 servers and does it support Hyper-Threading ?
No NUMA support. Yes PostgreSQL will work on HT enabled servers (will it be efficient - depend on workload and CPU type).
7. does PostgreSQL support in-memory store (similar to Oracle 12c in-memory and SQL Server 2014 in-memory OLTP) ?
No.
8. does PostgreSQL have temporary tables support?
Yes full temporary tables support since beginning.
"People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."
