Szymon Guz writes about JSON support in PostgreSQL:

So, I’ve shown you how you can use PostgreSQL as a simple NoSQL database storing JSON blobs of text. The great advantage over the simple NoSQL databases storing blobs is that you can constrain the blobs, so they are always correct and you shouldn’t have any problems with parsing and getting them from the database.

You can also query the database very easily, with huge speed. The ad-hoc queries are really simple, much simpler than the map-reduce queries which are needed in many NoSQL databases.

Since before NoSQL was called NoSQL, I’ve always thought that there’s a market, and more important, there are use cases for using single, unitary platforms for handling data. But there’s also a market, and the corresponding uses cases, for using different platforms for handling data. And there’s also the federated database systems and the logical data warehouses.

✚ I have this dream about how the databases will look in the future, but I never get around to putting together all the pieces, crossing the t’s and dotting the i’s.

Original title and link: PostgreSQL as NoSQL with Data Validation (NoSQL database©myNoSQL)

via: http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-data-validation.html

This means carying about your users’ data:

What we intend to do is shut off updates from the master git repo to the anonymous-git mirror, and to github, from Monday afternoon until Thursday morning. Commit-log emails to pgsql-committers will also be held for this period. This will prevent the commits that fix and document the bug from becoming visible to anyone except Postgres committers. Updates will resume as soon as the release announcement is made.

Original title and link: Extra Security Measures for Database Projects (NoSQL database©myNoSQL)

via: http://www.postgresql.org/message-id/14040.1364490185@sss.pgh.pa.us

A post by Brain Aker about the state of MySQL, Postgres and NoSQL databases.

I had a couple of comments and these evolved into a long rant.

MySQL became less interesting once it was acquired […]

I’ve never been very sure what metric is used to measure how interesting the product is. As opposed to some suggestions I’m reading, I haven’t seen stories of people moving away from MySQL because Oracle acquired it. Except Fedora and OpenSUSE replacing MySQL with MariaDB and this due to very specific issues (no security infos, no access to regression tests).

the number of Postgres deployments is greater then what all of the NoSQL market combined adds up to

Comparing 15 years of PosgreSQL with 3 years of NoSQL isn’t going to give meaningful results (for a similar unbalanced comparisons try Oracle vs PostgreSQL). I’m not aware of any database that captured a significant market share in the first 3 years of its existance. Except MySQL. Not Postgres.

Would a document model really matter if schemas could be altered online?

Yes, it would definitely remain relevant. Schema flexibility is not only about updating it, but also about the types allowed. PostgreSQL has indeed added support for arrays and JSON. I see this as a confirmation of what’s happening in the NoSQL space and also about the future of storage engines.

no new language has emerged from the NoSQL market that has any size-able adoption

MongoDB’s query language and the aggregation framework are used by a lot of people. It’s probably not the ideal query language and it comes in two different flavors, but it’s there and it’ll most probably evolve. Biasedly, I could also point to RethinkDB’s data manipulation language for an example of something that is probably on par with SQL and without the hidden unknown corner cases of SQL. Indeed none of these can come close the the adoption acquired by SQL in its 30 years of existance.

Bottom line is that I expect bridges to be built between relational databases and NoSQL databases and each side adopting those features that are useful to their users. I also expect that slowly this relational databases are crap vs NoSQL databases are crap debate will go away, people realizing that the data space is not a zero sum game. Vendors will be the last to give up this fight, but customers have a lot of power in making this happen.

Original title and link: Cage Match: MySQL vs NoSQL vs Postgres (NoSQL database©myNoSQL)

via: http://blog.krow.net/2013/03/mysql-vs-nosql-vs-postgres-vs-sql.html

As we’ve scaled Instagram to an ever-growing number of active users, Postgres has continued to be our solid foundation and the canonical data storage for most of the data created by our users. While less than a year ago, we blogged about how we “stored a lot of data” at Instagram at 90 likes per second, we’re now pushing over 10,000 likes per second at peak—and our fundamental storage technology hasn’t changed.

I only knew about the fifth one and I think the 2 tips about partial and functional indexes being extremely useful in general.

Original title and link: Handling Growth With Postgres: 5 Tips From Instagram (NoSQL database©myNoSQL)

via: http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from-instagram

Triggered by the improvements for PostgreSQL coming with ActiveRecord in Rails 4, today I’ve learned about PostgreSQL GiST and GIT index types:

In choosing which index type to use, GiST or GIN, consider these performance differences:

• GIN index lookups are about three times faster than GiST
• GIN indexes take about three times longer to build than GiST
• GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled
• GIN indexes are two-to-three times larger than GiST indexes

As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data, GiST indexes are faster to update. Specifically, GiST indexes are very good for dynamic data and fast if the number of unique words (lexemes) is under 100,000, while GIN indexes will handle 100,000+ lexemes better but are slower to update.

Original title and link: PostgreSQL GiST and GIN Index Types (NoSQL database©myNoSQL)

via: http://www.postgresql.org/docs/9.1/static/textsearch-indexes.html

Kevin Faustino:

Out of all the supported databases available in Active Record, PostgreSQL received the most amount of attention during the development of Rails 4. In today’s countdown post, we are going to look at the various additions made to the PostgreSQL database adapter.

Teaser:

1. hstore and hstore indexes support
2. arrays
3. uuid
4. network address data types
5. int4range, int8range
6. json

Original title and link: Improvements in Rails 4 ActiveRecord for PostgreSQL (NoSQL database©myNoSQL)

via: http://blog.remarkablelabs.com/2012/12/a-love-affair-with-postgresql-rails-4-countdown-to-2013

PuppetDB is replacing CouchDB for managing Puppet configurations and is a service layer written in Clojure with a PostgreSQL back-end. Not a graph database:

PuppetDB is a key component of the Puppet Data Library, and brings that to bear in its query API. Resources, facts, nodes, and metrics can all be queried over HTTP. For resources and nodes, there is a simple query language which can be used to form arbitrarily complex requests. The public API is the same one that Puppet uses to make storeconfigs queries (using the «||» operator) of PuppetDB, but provides a superset of the functionality provided by storeconfigs.

PuppetDB is faster, smarter, and has more complete data than ever before. […] PuppetDB offers great power over and insight into your infrastructure, and it’s only going to get bigger and better.

Original title and link: PuppetDB: Configuration Management Database for Puppet (NoSQL database©myNoSQL)

via: http://puppetlabs.com/blog/introducing-puppetdb-put-your-data-to-work/

Jeff Dickey enumerates some of the new features available in PostgreSQL—schema-less data, array columns, queuing, full-text searching, geo-spatial indexing—concluding that PosgreSQL has now everything an application needs:

Postgres has taken the features out of all of these tools and integrate it right inside the platform. Now you don’t need to spin up a mongo cluster for non-rel data, rabbitmq cluster for queueing, solr box for searching. You can just have a single postgres server. That saves a huge ops headache since each of those clusters/boxes have to be durable, replicated, and scalable.

Sounds a bit too optimistic? As we’ve learned from the NoSQL space there are no silver bullets:

Now obviously, there’s a glaring downside with this approach: you get one box. Maybe a read slave or something, but really, you can’t scale it.

As you can imagine I disagree with most of the points, the only exception being that it is great to see so many useful features packaged with PostgreSQL—these are definitely going to make like easier for some of the developers.

But when talking about MySQL and NoSQL being done:

1. MySQL is done, except it has a huge community, there are tons of developers very familiar with it, and last but not least MySQL powers massive deployments. This last part matters a lot.
2. NoSQL is done, except many NoSQL solutions tackle different problem spaces providing optimal solutions for these by staying focused. Neither Oracle, nor MongoDB, nor PosgreSQL will be able to solve all problems. The wider range of problems they are covering, the less optimal solutions they are providing for corner case or extreme scenarios.

Original title and link: MySQL Is Done. NoSQL Is Done. It’s the Postgres Age (NoSQL database©myNoSQL)

via: http://dickey.xxx/mysql-is-done-it-s-the-postgres-age

One of the stories in which the deciding factor for using Cassandra was primarily the data model and not its scalability characteristics:

We started working with relational databases, and began building things primarily with PostgreSQL at first.  But dealing with the kind of data that we do, the data model just wasn’t appropriate. We started with Cassandra in the beginning to solve one problem: we needed to persist large vector data that was updated frequently from many different sources. RDBMS’s just don’t do that very well, and the performance is really terrible for fast read operations. By contrast, Cassandra stores that type of data exceptionally well and the performance is fantastic. We went on from there and just decided to store everything in Cassandra.

Original title and link: Cassandra at Workware Systems: Data Model FTW (NoSQL database©myNoSQL)

via: http://www.datastax.com/2012/04/the-five-minute-interview-workware-systems