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

Yuri, Maxim,

A few clarifications-

* Maxim Boguk (maxim(dot)boguk(at)gmail(dot)com) wrote:
> > 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.

Right, we're working on it, but what's coming in 9.5 won't be anywhere
close to the parallel query you get with the commercial RDBMS's.

That said, PostgreSQL is *quite* good at parallel *operation*, including
things like having multiple processes which are reading from the same
table all follow each other and avoid adding i/o and without any locking
issues. I've built very large OLAP systems on PostgreSQL with only a
pretty minimal external utility that managed the multiple connections to
PostgreSQL to get parallel processing across partitioned tables.

One system I built did all of that with nearly arbitrary ad-hoc SQL
written by analysts and just shell scripts and psql. :)

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

It's not a goal of PG to provide hints. If the optimizer isn't doing
its job then come talk to us and we'll do our best to fix it.

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

Right, there's an extension for PostgreSQL which adds columnar
capability. It's also possible to get quite close to columnar if you
use arrays in core PG- I was able to squeeze ~1800 4-byte integers into
a PG 8k page using that technique. Columnar might get you a bit more,
but 1800 out of the 2048 max ain't bad for a relational DB.

> > 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 )

pg_stat_statements is pretty awesome.. Definitely worth looking at for
this requirement. There is also pgBadger, which is a fantastic tool for
looking at what queries are taking the most time.

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

You don't necessairly need to go to sync rep to get that- if you have a
properly redundany storage subsystem which won't lie about fsync, ever,
then you can simply do direct failover from the write-ahead-log to a new
system in case of failure of the primary. sync rep adds another level
of latency as PG does both the fsync and waits for the remote server to
acknowledge the write.

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

PostgreSQL doesn't need to directly support NUMA- the Linux kernel does.
As for if we do anything special when running in a NUMA environment, no,
not at this time. Ditto with hyper-threading.

> 7. does PostgreSQL support in-memory store (similar to Oracle 12c in-memory
> > and SQL Server 2014 in-memory OLTP) ?
> >
> No.

Temporary tables will be in memory unless they overflow work_mem and we
do support unlogged tables and tablespaces which you could stick out on
a ramdisk if you want.

> 8. does PostgreSQL have temporary tables support?
> >
> Yes full temporary tables support since beginning.

Yup.

Thanks!

Stephen