Major features of PostgreSQL 9.6 [pdf] (opens in new tab)

> I thought a sequential scan was slow because it was limited by disk I/O

Not necessarily. Suppose you have a fairly big table that fits in the shared buffer cache and you run a query with a predicate that returns only a handful of rows. Then you would be better off scanning parts of it in parallel and then combining the results.

It is worth checking out the wiki on the details of the parallel query for how and when it works: https://wiki.postgresql.org/wiki/Parallel_Query

Parallel sequential scan is a building block that enables other work to be done in parallel. Once you've got a way to split up a sequential scan by handing chunks to different workers, those workers can then do other CPU intensive work like filtering (involving potentially expensive expressions), partial aggregation (for example summing a subtotal that will be added to other workers' subtotals at the end) and joining (say against a hash table).

Because "the disk" is the wrong abstraction here; think instead of stuffing I/O requests into a queue, letting the storage controller re-order them, then giving you a callback when the blocks are ready.

I was wondering the exact same thing -- how can CPU-parallelism improve on a task which (to me at least) seems to be disk-IO bound? I'm guessing that my assumption of the task being disk-IO bound is incorrect!

For some cases (like single HDD and low amount of RAM) it should be so, but for some cases(from "the whole table is already loaded to RAM" to "SSD" and to "a table is partitioned among multiple drives"), it must give very good improvements for analytical queries.

> why is it faster to have multiple threads waiting around for the same disk?

RAID?

> I really would like to see bi-temporal table support eventually come to PG

> Is anybody aware somebody working on that for PG?

Not that I am aware of.

Postgres is a fantastic database, and with zero licensing headaches, but when people ask why companies are still paying for commercial DBMS engines, some features that they have which Postgres does not have, and which I often miss are :

1)"temporal" / versioned tables / time consistent views of data . Oracle has a lot of "flashback" related options at the DB and table levels which can be really useful.

2) Easy, built-in partitioning for large datasets. (This is being worked on and will probably start to be released in Postgres 10).

3) Better backup options for large databases, such as incremental/differential backups.

In the Yandex migration from Oracle to Postgres I recall that this, ie incremental backups, is one of the things they mentioned it would be great to have.

4) Along with backups, the built-in recovery options in Postgres are fairly basic compared to what you can do regarding single table recovery, corruption repair etc that other DBs have.

There is https://github.com/arkhipov/temporal_tables which seems okay.

Of course, it would be nice if Postgres natively supported the full SQL:2011 standard...

What does 'doing that with POSIX' mean? I have read this many times but I do not have a clear understanding.

Where did you see those?