6 ms·

Major features of PostgreSQL 9.6 [pdf]

126 points by ioltas 10y ago

thristian 10y ago
> Parallel execution of sequential scans, joins and aggregates This confuses me—I thought a sequential scan was slow because it was limited by disk I/O; why is it faster to hawe multiple threads waiting around for the same disk? (I guessed maybe that it was running different sequential scans in the same query in parallel, but no, pages 3 and 4 of the PDF show parallel workers being used for a single scan of a single table)
- _pmf_ 10y ago
  > why is it faster to have multiple threads waiting around for the same disk? RAID?
- dunkelheit 10y ago
  > 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.
- katnegermis 10y ago
  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!
  - jaziek 10y ago
    It's only disk IO bound if you're accessing the disk. With modern servers packing multiple TB of memory, it is likely you won't be accessing the disk at all.
- mianos 10y ago
  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 https://wiki.postgresql.org/wiki/Parallel_Query
  - amitlan 10y ago
    or read this chapter (the wiki page's content made into the official documentation): https://www.postgresql.org/docs/devel/static/parallel-query.html https://www.postgresql.org/docs/devel/static/parallel-query....
- macdice 10y ago
  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).
- gaius 10y ago
  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.
- postila 10y ago
  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.
jhoechtl 10y ago
I really would like to see bi-temporal table support eventually come to PG https://wiki.postgresql.org/images/6/64/Fosdem20150130PostgresqlTemporal.pdf https://wiki.postgresql.org/images/6/64/Fosdem20150130Postgr... Is anybody aware somebody working on that for PG?
- clg2 10y ago
  > 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.
  - technion 10y ago
    The corollary to the backup solution is that if Postgresql ever ended up operating in a similar fashion to Oracle's rman, particularly on its encouraged ASM, Postgresql would very quickly start seeing "Postgresql requires a full time DBA, just use MariaDB" type blogs. I can see how the middle ground is a hard problem.
    - clg2 10y ago
      I agree, Postgres having its own storage subsystem would be wrong. Fortunately the PG philosophy is to leave what the OS does best to the OS, so its unlikely that situation would arise. However, a middle ground of being able to backup just the changes in a multi-terabyte database that have been made since the last full backup, wouldn't be controversial and is a common use case. I've read of a few attempts on the PG dev mailing list over the last couple of years, so hopefully one of them will be completed sometime soon and merged into core. Some of the work I have come across : https://github.com/ossc-db/pg_rman https://github.com/ossc-db/pg_rman https://github.com/2ndquadrant-it/barman/issues/21 https://github.com/2ndquadrant-it/barman/issues/21
merb 10y ago
Wow the Version 10 feature's are exciting. Really.
- brightball 10y ago
  Where did you see those?
  - travisby 10y ago
    the (second) last slide were 'possible 10 features'
dsavinkov 10y ago
I am really glad to see "searching for phrases" feature for Full-Text Search, as doing that with POSIX was really painful operation for long text area fields.
- dragon_king 10y ago
  What does 'doing that with POSIX' mean? I have read this many times but I do not have a clear understanding.
  - trungaczne 10y ago
    I think what he means is "doing it using Unix command line utilities", like grep.
  - dsavinkov 10y ago
    I meant using something like t.long_text_field ~~ '\mexact phrase\M' in actual sql statement - https://www.postgresql.org/docs/9.5/static/functions-matching.html https://www.postgresql.org/docs/9.5/static/functions-matchin...

126 points by ioltas 10y ago

> Parallel execution of sequential scans, joins and aggregates This confuses me—I thought a sequential scan was slow because it was limited by disk I/O; why is it faster to hawe multiple threads waiting around for the same disk? (I guessed maybe that it was running different sequential scans in the same query in parallel, but no, pages 3 and 4 of the PDF show parallel workers being used for a single scan of a single table)

_pmf_ 10y ago
> why is it faster to have multiple threads waiting around for the same disk? RAID?
dunkelheit 10y ago
> 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.
katnegermis 10y ago
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!
- jaziek 10y ago
  It's only disk IO bound if you're accessing the disk. With modern servers packing multiple TB of memory, it is likely you won't be accessing the disk at all.
mianos 10y ago
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 https://wiki.postgresql.org/wiki/Parallel_Query
- amitlan 10y ago
  or read this chapter (the wiki page's content made into the official documentation): https://www.postgresql.org/docs/devel/static/parallel-query.html https://www.postgresql.org/docs/devel/static/parallel-query....
macdice 10y ago
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).
gaius 10y ago
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.
postila 10y ago
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.

jhoechtl 10y ago

I really would like to see bi-temporal table support eventually come to PG https://wiki.postgresql.org/images/6/64/Fosdem20150130PostgresqlTemporal.pdf https://wiki.postgresql.org/images/6/64/Fosdem20150130Postgr... Is anybody aware somebody working on that for PG?

clg2 10y ago
> 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.
- technion 10y ago
  The corollary to the backup solution is that if Postgresql ever ended up operating in a similar fashion to Oracle's rman, particularly on its encouraged ASM, Postgresql would very quickly start seeing "Postgresql requires a full time DBA, just use MariaDB" type blogs. I can see how the middle ground is a hard problem.
  - clg2 10y ago
    I agree, Postgres having its own storage subsystem would be wrong. Fortunately the PG philosophy is to leave what the OS does best to the OS, so its unlikely that situation would arise. However, a middle ground of being able to backup just the changes in a multi-terabyte database that have been made since the last full backup, wouldn't be controversial and is a common use case. I've read of a few attempts on the PG dev mailing list over the last couple of years, so hopefully one of them will be completed sometime soon and merged into core. Some of the work I have come across : https://github.com/ossc-db/pg_rman https://github.com/ossc-db/pg_rman https://github.com/2ndquadrant-it/barman/issues/21 https://github.com/2ndquadrant-it/barman/issues/21

merb 10y ago

Wow the Version 10 feature's are exciting. Really.

brightball 10y ago
Where did you see those?
- travisby 10y ago
  the (second) last slide were 'possible 10 features'

dsavinkov 10y ago

I am really glad to see "searching for phrases" feature for Full-Text Search, as doing that with POSIX was really painful operation for long text area fields.

dragon_king 10y ago
What does 'doing that with POSIX' mean? I have read this many times but I do not have a clear understanding.
- trungaczne 10y ago
  I think what he means is "doing it using Unix command line utilities", like grep.
- dsavinkov 10y ago
  I meant using something like t.long_text_field ~~ '\mexact phrase\M' in actual sql statement - https://www.postgresql.org/docs/9.5/static/functions-matching.html https://www.postgresql.org/docs/9.5/static/functions-matchin...