News for hackers. Stuff that matters.
Re:PostgreSQL 9.6 Beta 1 Released by koolba on Thursday May 12, 2016 @10:36AM (#11683687)
Parallel query will probably win for sexiest new feature (rightfully so!) but remote apply is very useful in the real world. It will greatly simplify reading newly written data off replicas: http://michael.otacoo.com/postgresql-2/postgres-9-6-feature-...
Re:PostgreSQL 9.6 Beta 1 Released by dtech on Thursday May 12, 2016 @12:11PM (#11684469)
Could you elaborate on exactly what it is? Will it mean the named elements of queries will automatically be parallelized?
Re:PostgreSQL 9.6 Beta 1 Released by koolba on Thursday May 12, 2016 @12:27PM (#11684635)
Postgres currently uses a single process per connection. That means at most one CPU core is used per query executing a connection. Parallel query allows breaking up query plans to allow multiple processes (and by extension cpu cores) to process a part of a query plan.

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.

Re:PostgreSQL 9.6 Beta 1 Released by Someone1234 on Thursday May 12, 2016 @01:14PM (#11685081)
There's nothing to complain about with this. But aren't most large database queries ultimately IO capped, rather than CPU capped? Wouldn't this just put more load on the IO and it would just bottleneck there?
Re:PostgreSQL 9.6 Beta 1 Released by gshulegaard on Thursday May 12, 2016 @01:29PM (#11685203)
Yes and no. Take the following with grain of salt as it is just my personal opinion/understanding. Only 1 or 2 points are actually from challenges I have run into.

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.

Re:PostgreSQL 9.6 Beta 1 Released by TylerE on Thursday May 12, 2016 @01:45PM (#11685316)
And also RAM keeps getting cheaper. 64GB is absolutely nothing these days and can fit a LOT of data.
Re:PostgreSQL 9.6 Beta 1 Released by snuxoll on Thursday May 12, 2016 @01:56PM (#11685393)
All depends on the workload, aggregates are more often CPU capped than IO cap in workloads we use in-house. We have a PostgreSQL server with 16GB of memory (the database total is over 80GB, but the table that is hit the most is under 3GB, the rest is just BLOB's) - more than enough to keep the relevant data in memory. Most of the time running some aggregate queries that hit the database server pretty hard are spent in USER, not IOWAIT. It's pretty annoying, because I frequently see my CPU usage at 50% (one of two cores maxed) when I know these queries could be parallelized and take advantage of the second core (or more).
Re:PostgreSQL 9.6 Beta 1 Released by snaky on Sunday May 22, 2016 @09:33AM (#11748510)
Not anymore.

>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

https://blog.acolyer.org/2016/01/15/non-volatile-storage/

Re:PostgreSQL 9.6 Beta 1 Released by stubish on Friday May 13, 2016 @04:35AM (#11689315)
Now queries can be parallelized, combining the results of several subqueries in different processes. This is a stepping stone to being able to run several subqueries on different machines. And if those different machines contain different shards of data, well, PostgreSQL has just evolved into a scale out 'Big Data' database without throwing out any relational features. Two phase commit, synchronous replication, logical replication - all the pieces are falling into place, and you can see the remaining pieces forming in extensions like BDR and Citrus Data and others.
Re:PostgreSQL 9.6 Beta 1 Released by pkaye on Thursday May 12, 2016 @12:32PM (#11684680)
Multiple worker thread will work together to do sequential scans, joins and aggregation. Some mentions here: http://rhaas.blogspot.com/2016/03/parallel-query-is-getting-...

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

Re:PostgreSQL 9.6 Beta 1 Released by melor on Thursday May 12, 2016 @10:28AM (#11683614)
From the Release Notes:

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

Re:PostgreSQL 9.6 Beta 1 Released by utternerd on Thursday May 12, 2016 @11:48AM (#11684262)
This is excellent, the read-balancing consistency is a huge feature, and one we've been waiting for as trying to do read balancing without it is jumping through way too many hoops (unless data consistency just isn't your thing).
Re:PostgreSQL 9.6 Beta 1 Released by pella on Thursday May 12, 2016 @10:58AM (#11683846)
and 9.7 have started planning

* [ "asynchronous and vectorized execution" ]

http://www.postgresql.org/message-id/CA+Tgmobx8su_bYtAa3Dgrq...

Re:PostgreSQL 9.6 Beta 1 Released by ormod_ on Thursday May 12, 2016 @12:28PM (#11684646)
The features that I'll personally probably enjoy the most are really the FDW join pushdown work, parallel query. (though it's still early days for the parallel stuff)

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

Re:PostgreSQL 9.6 Beta 1 Released by mistermann on Thursday May 12, 2016 @01:04PM (#11685001)
Could you possibly quickly explain what a freeze map is?
Re:PostgreSQL 9.6 Beta 1 Released by anarazel on Thursday May 12, 2016 @01:17PM (#11685108)
Postgres uses a specific brand of MVCC (https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...) where multiple versions of a row are stored in the heap. That's why there's vacuuming to remove those.

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.

Re:PostgreSQL 9.6 Beta 1 Released by desireco42 on Thursday May 12, 2016 @11:51AM (#11684284)
I love new features in Postgres, but I hate upgrading. Transition, especially for dev environment is always way too complex and not obvious at all.

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.

Re:PostgreSQL 9.6 Beta 1 Released by rpedela on Thursday May 12, 2016 @12:15PM (#11684495)
Re:PostgreSQL 9.6 Beta 1 Released by snuxoll on Thursday May 12, 2016 @12:26PM (#11684624)
pg_upgrade is a pretty reasonable compromise, but it would be nice if an upgrade didn't require your entire database be taken offline for the duration. I can install a new MS SQL Server instance, attach a database from the old instance and be going in under a minute of downtime - this is literally the only pain point I have with Pq, but considering I don't typically upgrade EVERY release (I still have 9.4 instances that have no reason to be upgraded to 9.5 since the applications aren't in-house) it's not typically a huge deal.
Re:PostgreSQL 9.6 Beta 1 Released by oskari on Thursday May 12, 2016 @12:52PM (#11684876)
pg_upgrade in link mode typically takes less than a minute to run unless you have a lot of tables; the runtime is dependant on the number of database objects, not rows or size of the database.

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.

Re:PostgreSQL 9.6 Beta 1 Released by nunwuo on Thursday May 12, 2016 @11:57AM (#11684328)
> 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

Re:PostgreSQL 9.6 Beta 1 Released by jeffdavis on Thursday May 12, 2016 @12:00PM (#11684358)
It's not an unreasonable request, and certainly worth some effort to improve.

That being said, if upgrade is the biggest problem, that's great news!

Re:PostgreSQL 9.6 Beta 1 Released by desireco42 on Thursday May 12, 2016 @04:30PM (#11686528)
Hey, I know it sounds a little spoiled, but when I am focused on something else, especially in development context, I could use some more automated way.

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.

Re:PostgreSQL 9.6 Beta 1 Released by agopaul on Thursday May 12, 2016 @12:13PM (#11684483)
'Full-text search for phrases': what's the difference between the current full text search implementation and the one for phrases?
Re:PostgreSQL 9.6 Beta 1 Released by elchief on Thursday May 12, 2016 @12:37PM (#11684740)
currently searching for 'new york' would match 'york new', as the parser doesn't understand phrases, but new version would let you match '"new york"' to 'new york' but not 'york new'
Re:PostgreSQL 9.6 Beta 1 Released by agopaul on Thursday May 12, 2016 @02:08PM (#11685483)
That is a great addition then
Re:PostgreSQL 9.6 Beta 1 Released by pella on Thursday May 12, 2016 @10:46AM (#11683765)
Re:PostgreSQL 9.6 Beta 1 Released by rpedela on Thursday May 12, 2016 @12:26PM (#11684627)
Does anyone know if parallel query execution just happens by default or do you have to tell PG that you want it?
Re:PostgreSQL 9.6 Beta 1 Released by jeltz on Thursday May 12, 2016 @12:41PM (#11684774)
You have to enable it by configuring the maximum number of workers per query, and then the query planner tries to figure out if the query would benefit from a parallel execution. This is the first release with parallelism so I would not be surprised if the query planner gets this wrong in some cases.
Re:PostgreSQL 9.6 Beta 1 Released by qaq on Thursday May 12, 2016 @11:33AM (#11684137)
UPSERT from previous release plus Parallel sequential scans, joins and aggregates and Full text search for phrases once 9.6 is ready will make PG almost perfect :)
Re:PostgreSQL 9.6 Beta 1 Released by kbenson on Thursday May 12, 2016 @01:02PM (#11684970)
Is there something specifically interesting in the 9.6 release that makes it more noteworthy than other releases? I enjoy reading PostgreSQL release announcements, as I myself haven't used it but have plans to do some testing with is when some time free up, but I'm a bit confused as to why a beta release announcement is getting so much attention.
Re:PostgreSQL 9.6 Beta 1 Released by anarazel on Thursday May 12, 2016 @01:06PM (#11685019)
I guess because beta usually determines which features are going to be in the final release. So if you're looking what release to choose for a new version of your software, it might be more worthwhile to look at beta than the final release.
Re:PostgreSQL 9.6 Beta 1 Released by elchief on Thursday May 12, 2016 @01:10PM (#11685047)
Any Postgres news is good news here. Same with OpenBSD. Just embrace it.
Re:PostgreSQL 9.6 Beta 1 Released by oskari on Thursday May 12, 2016 @10:38AM (#11683699)
PG 9.6 Beta is available for testing at our Aiven.io Cloud Database service: http://blog.aiven.io/2016/05/help-test-postgresql-96-via-aiv...
Re:PostgreSQL 9.6 Beta 1 Released by forkandwait on Thursday May 12, 2016 @01:36PM (#11685250)
I think that great parallel infrastructure would be worthy of a Postgres 10.0.
Re:PostgreSQL 9.6 Beta 1 Released by elchief on Thursday May 12, 2016 @11:04AM (#11683883)
Isn't that like 8 months ahead of schedule?
Re:PostgreSQL 9.6 Beta 1 Released by anarazel on Thursday May 12, 2016 @12:12PM (#11684474)
Usually the target for the releases was September, not December/January like it ended up being for the last two. We're trying to get back to that.
Re:PostgreSQL 9.6 Beta 1 Released by pilif on Thursday May 12, 2016 @11:19AM (#11684008)
The Postgres beta periods are usually really, really, really long. Mid/End may is traditionally when the first beta comes out for a release in late autumn.
Re:PostgreSQL 9.6 Beta 1 Released by makmanalp on Thursday May 12, 2016 @11:51AM (#11684290)
As they should be! Enough time to know whether stuff breaks in real life or not, and to deal with fixing any issues that come up.
Re:PostgreSQL 9.6 Beta 1 Released by snuxoll on Thursday May 12, 2016 @12:02PM (#11684380)
Especially for software so mission critical as a RDBMS. If your web framework has a bug users get errors or things act wonky, if your database has a bug you can get data corruption and other horrible, horrible things.