Re:PostgreSQL 9.4 Beta 1 Released
by gdulli on Thursday May 15, 2014 @12:32PM
(#7750492)
I have an 8TB database with 10 bn rows, each with multiple json columns where most of the real data is stored... so jsonb day is like christmas for me.
Re:PostgreSQL 9.4 Beta 1 Released
by gdulli on Thursday May 15, 2014 @02:34PM
(#7751308)
We have an organization full of analysts who know SQL very well and write complex queries. Moving them all to a mapreduce query language proprietary to mongo, riak, or cassandra (and then being locked into it) would have been much more painful. Postgres has worked out very well, and I had confidence in it from prior experience which influenced the decision. With proper partioning in the schema and a json type we have the best of both worlds (SQL+NoSQL) as far as this system needs.
Re:PostgreSQL 9.4 Beta 1 Released
by gdulli on Thursday May 15, 2014 @02:36PM
(#7751323)
To get the same flexibility you'd want from a nosql data store. Without giving up the simpler, more open access methods. The schema is partly predictable (and indexed or fielded on those parts) and partly unpredictable.
Re:PostgreSQL 9.4 Beta 1 Released
by johanneskanybal on Thursday May 15, 2014 @11:10AM
(#7749857)
After playing around in nosql land for the last 3 years I've landed at postgresql as my go-to sql alternative although the market will keep being fragmented.
edit: json support in combination with a less fanatic approach to normalization can approximate some of the benefits a nosql model can while maintaining the bulk of the tried-and-tested flows in a relational database.
Re:PostgreSQL 9.4 Beta 1 Released
by symfoniq on Thursday May 15, 2014 @11:14AM
(#7749892)
I think he probably means that certain Postgres features like hstore and JSON support allow it to be used in situations where he once would have chosen a no-SQL solution. This has been my own experience.
Re:PostgreSQL 9.4 Beta 1 Released
by vfclists on Thursday May 15, 2014 @12:48PM
(#7750619)
When is PostgreSQL going to get its own modern language which is functional/object-oriented/homoiconic/has-all-new-fangled-goodies built in, with a GUI language thrown in for good measure, so I can do everything in PostgreSQL without having to turn to another language?
Re:PostgreSQL 9.4 Beta 1 Released
by vfclists on Friday May 16, 2014 @06:05AM
(#7754687)
To be honest I have no idea what homoiconic means.
I think it means that everything is the same, ie whether a function in the case of functional languages like Haskell, or an object in languages like Smalltalk.
"In a homoiconic language the primary representation of programs is also a data structure in a primitive type of the language itself. This makes metaprogramming easier..."
For lisp, that means programs are are also lists, so can be manipulated with macros easily (because lisp macros are also lisp, which is good at working with lists).
For a relational language, that would mean the programs are also relations, which would be a little weird because relations are unordered.
Re:PostgreSQL 9.4 Beta 1 Released
by wfn on Thursday May 15, 2014 @01:05PM
(#7750738)
Oh, is it now a built-in module in 9.4beta?
I've used it in 9.1 (downloading the patch, applying it, recompiling postgres..) - have found this article re: postgres caching mechanisms (and pg_prewarm use in particular (though re: pg_prewarm, most of it is about applying the patch itself, etc.)) useful:
Re:PostgreSQL 9.4 Beta 1 Released
by bradleysmith on Thursday May 15, 2014 @07:19PM
(#7752873)
Actually, I wasn't restarting the server. The pain was reloading that file, as it would break work that other people were doing at the time. I'm looking into whether modifying the postgresql.conf file from a query line will let us do what we were having a need to do.
Re:PostgreSQL 9.4 Beta 1 Released
by gfisher on Thursday May 15, 2014 @11:54AM
(#7750163)
The refresh concurrently materialized views is huge. I am glad that it was introduced since I currently have to use tables with functions to create/drop to get the same functionality.
Getting MERGE right is one of these "pick two" things. You get to chose between "fast", "doesn't corrupt data" and "doesn't throw errors at times" but you only get to pick two.
Re:PostgreSQL 9.4 Beta 1 Released
by ajtaylor on Thursday May 15, 2014 @09:03PM
(#7753220)
Pretty much every Pg related post on depesz.com is a gold mine. You should read them all, especially where he explains upcoming features in new Pg releases. I've learned a ton simply be browsing through his site, which is very readable with great examples to put the concepts into practice.
Re:PostgreSQL 9.4 Beta 1 Released
by jeffdavis on Thursday May 15, 2014 @03:43PM
(#7751751)
A completely reasonable stance.
However, it's not so much a performance question as one of concurrency. The easiest way to get the right semantics is by locking the entire destination table against concurrent writes. Maybe that's fine for a lot of users. But it has caused some hesitation, because other users might be disappointed.
Re:PostgreSQL 9.4 Beta 1 Released
by andyl on Thursday May 15, 2014 @11:11AM
(#7749872)
With 9.4, what will be the best option for multi-master replication? Will 3rd party tools like bucardo remain the preferred method, or will there be another strategy that is more integrated with PG?
Re:PostgreSQL 9.4 Beta 1 Released
by rpedela on Thursday May 15, 2014 @11:21AM
(#7749938)
My understanding is you still need the 3rd party tools. The new replication features lay the foundation for other replication strategies like multi-master, but they are not implemented yet.
also jsonb will require a little more storage
is 8tb in 1 tablespace/db ?
edit: json support in combination with a less fanatic approach to normalization can approximate some of the benefits a nosql model can while maintaining the bulk of the tried-and-tested flows in a relational database.
It's like Kanso/CouchApp for CouchDB, and has support for the functional/object-oriented/new-fangled LiveScript language on top of the v8 engine.
Still working on the homoiconicity part, though!
Interesting thought experiment, but it sounds pretty radical.
I think it means that everything is the same, ie whether a function in the case of functional languages like Haskell, or an object in languages like Smalltalk.
"In a homoiconic language the primary representation of programs is also a data structure in a primitive type of the language itself. This makes metaprogramming easier..."
For lisp, that means programs are are also lists, so can be manipulated with macros easily (because lisp macros are also lisp, which is good at working with lists).
For a relational language, that would mean the programs are also relations, which would be a little weird because relations are unordered.
I've used it in 9.1 (downloading the patch, applying it, recompiling postgres..) - have found this article re: postgres caching mechanisms (and pg_prewarm use in particular (though re: pg_prewarm, most of it is about applying the patch itself, etc.)) useful:
http://raghavt.blogspot.com/2012/04/caching-in-postgresql.ht...
P.S. here are some more comments from the author: http://www.postgresql.org/message-id/CA+TgmoZihvzFW6n6pPwzDO... (btw, I've found the pgsql-hackers to be a great mailing list indeed!)
This is fantastic. We're always adding and removing ip addresses and restarting the server with each update was a pain.
Appreciate it!
http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.h... explains that you can just do a reload after changing pg_hba.conf.
http://www.postgresql.org/docs/9.3/static/config-setting.htm... explains more.
Getting MERGE right is one of these "pick two" things. You get to chose between "fast", "doesn't corrupt data" and "doesn't throw errors at times" but you only get to pick two.
upsert has been an embarrassment for Postgres for far too long now. Particularly obnoxious was the "upsert is hard! Roll your own" stance taken.
However, it's not so much a performance question as one of concurrency. The easiest way to get the right semantics is by locking the entire destination table against concurrent writes. Maybe that's fine for a lot of users. But it has caused some hesitation, because other users might be disappointed.
It would be fair to say "just do it" though.
EDIT: logical decoding allows you to get access to a stream of logical data changes, e.g. inserts, updates, deletes, in a robust and efficient way.