PostgreSQL 9.6 Beta 1 Released
postgresql.org
postgresql.org
The classic example is a sequential scan, say for a COUNT(*) or SUM(foo). A single process would scan through all the blocks in order. A parallel scan would split the blocks into N buckets, scan them via N separate processes, and then combine the results.
Restricting every query to a single core places an artificial CPU cap on a system that would otherwise be IO capped.
Disks/storage is also getting faster (http://www.geek.com/chips/new-intel-storage-is-1000-times-fa...).
As analytics keeps gaining popularity, the need/desire to move more and more CPU bound processing to the data storage layer is increasing.
And finally, PostgreSQL's advanced query planning/partitioning support would immensely benefit from being able to split partition queries into multiple process buckets.
>The age-old assumption that I/O is slow and computation is fast is no longer true: this invalidates decades of design decisions that are deeply embedded in today’s systems
But this is just a start and there are lots more opportunities to improve as mentioned here: https://wiki.postgresql.org/wiki/Parallel_Query_Execution
Major enhancements in PostgreSQL 9.6 include:
Parallel sequential scans, joins and aggregates
Elimination of repetitive scanning of old data by autovacuum
Synchronous replication now allows multiple standby servers for increased reliability
Full-text search for phrases
Support for remote joins, sorts, and updates in postgres_fdw
Substantial performance improvements, especially in the area of improving scalability on many-CPU servers
* [ "asynchronous and vectorized execution" ]
http://www.postgresql.org/message-id/CA+Tgmobx8su_bYtAa3Dgrq...
..and the thing that's a killer feature for many larger databases, the freeze map. Not having to FREEZE things all the time is a godsend.
To avoid having to vacuum whole relations all the time there's the "visibility map", which keeps track of which blocks have no dirty rows; so they can be skipped during vacuum.
To understand which versions are visible postgres stores transaction-ids in the row headers; these are four byte wide. On systems with significant throughput, those don't last very long. So every now and then there's anti-wraparound vacuums; which replaces xids which are about to become "too old" with a special marker ("frozen").
The problem < 9.6 is that these anti-wraparound vacuums have to scan the whole table, thereby are a lot more expensive. These happen every 200 million xids by default (write transactions and/or savepoints), configurable up to ~2 billion. With the freeze map, that doesn't have to happen, only non-frozen pages have to be re-scanned.
If there is one feature I wish PG had, it is recognizing old format db and offering one command that can in place upgrade damn db, without me googling it every time.
So your having to Google for five minutes when doing a major version upgrade is the biggest flaw in modern postgres? Oh come on now.
P.S. pg_upgradecluster in Debian would probably do this for you without googling
That being said, if upgrade is the biggest problem, that's great news!
I am sorry you got downvoted, but as you can see, it is a genuine pain that software should do for us. On other hand, PG is fantastic DB and it is great I have this as a problem.
Database statistics are not preserved across pg_upgrade so you'll have to run ANALYZE on the database after the upgrade which may take a while as it usually needs to read all or almost all of your data.
I just recently upgraded a ~1 TB database, the actual upgrade process took less than a minute but analyzing it took much longer.