Re: moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

Hi Yuri,

I will try answer your questions one by one.

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 ?
>
​No, but statistical views in 9.2+ provides pretty good overview about
what's going on the database (especially useful could be
pg_stat_statements:
http://www.postgresql.org/docs/9.4/interactive/pgstatstatements.html )

> 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.​

Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.
​com​
/ <http://www.postgresql-consulting.com/>
​Melbourne, Australia​

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com
МойКруг: http://mboguk.moikrug.ru/

"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."