
Major features of PostgreSQL 9.6 [pdf] - ioltas
http://momjian.us/main/writings/pgsql/features.pdf
======
jhoechtl
I really would like to see bi-temporal table support eventually come to PG

[https://wiki.postgresql.org/images/6/64/Fosdem20150130Postgr...](https://wiki.postgresql.org/images/6/64/Fosdem20150130PostgresqlTemporal.pdf)

Is anybody aware somebody working on that for PG?

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

~~~
elmigranto
> incremental/differential backups

What's wrong with WAL backup? Postgres can run your script and pass it path to
newly created WAL chunk, that you can ship off anywhere.

Or run `pg_start_backup`, rsync data directory, `pg_stop_backup`.

~~~
dgregd
> What's wrong with WAL backup?

I use PG for 10 years and I never needed incremental backups. But if someone
asked me about it then I would respond that the PG has no incremental backups.

So WAL backup is not well known or documented in my opinion. An average db
admin needs a tool which will generate incremental backup as a single file
named like dbname_20160926.sql

~~~
SEJeff
Barman[1] gives you something better than incremental backups actually. It
sets up the backup server as a database slave via PG's built-in replication.
It gives you point in time recovery with the nicest CLI of any backup tool for
any DB I've ever used.

If you think backup tools are a minus for PG, I guess you've never used
barman. Easy as pie and has saved my bacon several times when CASCADE DELETE
wrecked a few dbs for me due to coworkers futzing around.

[1] [http://www.pgbarman.org/](http://www.pgbarman.org/)

~~~
dgregd
> If you think backup tools are a minus for PG, I guess you've never used
> barman

You are right, I've never used barman. But I have streaming replication
configured manually. Generally I am quite happy with PG backup tools.

However this discussion made me to think about pg_dump deficiencies. WAL
backup is for whole db cluster. But how do I incrementally update one db? E.g.
one prod db to my dev environment.

~~~
SEJeff
Indeed, that is something that WAL files don't work for. There aren't a lot of
great answers for that. I generally will restore a point in time snapshot
somewhere and then dump out that entire db to restore via sql or whatnot.

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

~~~
mianos
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
or read this chapter (the wiki page's content made into the official
documentation): [https://www.postgresql.org/docs/devel/static/parallel-
query....](https://www.postgresql.org/docs/devel/static/parallel-query.html)

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

~~~
brightball
Where did you see those?

~~~
travisby
the (second) last slide were 'possible 10 features'

------
dsavinkov
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
What does 'doing that with POSIX' mean? I have read this many times but I do
not have a clear understanding.

~~~
dsavinkov
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-
matchin...](https://www.postgresql.org/docs/9.5/static/functions-
matching.html)

