As it turns out, PostgreSQL has a number of ways of storing loosely structured data/documents in a column on a table.

• hstore is a data type available as a contrib package that allows you to store key/value structures just like a dictionary or hash.
• You can store data in JSON format on a text field, and then use PLV8 to JSON.parse() it right in the database.
• There is a native xml data type, along with a few interesting query functions that allow you to extract and operate on data that sits deep in an XML structure.

I concur. Not knowing your database *must not* be the reason for adopting a NoSQL database.

Original title and link: The Durable Document Store You Didn’t Know You Had, but Did (NoSQL database©myNoSQL)

via: http://robots.thoughtbot.com/post/13829210385/the-durable-document-store-you-didnt-know-you-had-but

Ars Technica’s Sean Gallagher summarizes a presentation given at Surge conference covering the evolution of Etsy’s architecture from a centralized PostgreSQL stored procedures based solution to a sharded MySQL and going through a failed service oriented-like architecture:

And the team started to shift feature by feature away from a semi-monolithic Postgres back-end to sharded MySQL databases. “It’s a battle-tested approach,” Snyder said. “Flickr is using it on an enormous scale. It scales horizontally, basically, to near infinity, and there’s no single point of failure—it’s all master to master replication.”

Original title and link: The Story of Etsy’s Architecture (NoSQL database©myNoSQL)

via: http://arstechnica.com/business/news/2011/10/when-clever-goes-wrong-how-etsy-overcame-poor-architectural-choices.ars

Patrick Schless describes the pros of using RRDTool for collect write-once data over time, and graph the results.

The projects collect very different data, but this task was painful enough in postgres that I ended up switching to a temporal database for the second go, and it made the data collection & querying much easier. What follows are a brief discussion of the problems I faced with postgres, and how moving to RRD solved them.

Check also the Hacker news thread for a couple of other tricks for RRDTool.

In the NoSQL space, this sort of quick analytics use case was associated with MongoDB:

• Scalable event analytics with MongoDB & Ruby on Rails
• Fast, asynchronous analytics with MongoDB
• Social analytics on MongoDB
• MongoDB and Site Analytics
• Tracking page views with MongoDB
• MongoDB use case: site analytics, a reoccuring scenario

Other larger platforms have developed their own solutions:

• StumbleUpon: OpenTSDB: a distributed, scalable monitoring system on top of HBase and OpenTSDB: a HBase scalable time series database
• Facebook builds HBase-based real time analytics

But using a specialized solution has its own benefits… where did we hear that before?

Original title and link: Beyond NoSQL: Using RRD to Store Temporal Data (NoSQL database©myNoSQL)

via: http://www.plainlystated.com/2011/07/beyond-nosql-using-rrd-to-store-temporal-data/

The architecture of a fault-tolerant ad network built on top of HAProxy, Apache with mod_wsgi and Python, Redis, a bit of PostgreSQL and ActiveMQ deployed on AWS:

The real workhorse of our ad targeting platform was Redis. Each box slaved from a master Redis, and on failure of the master (which happened once), a couple “slaveof” calls got us back on track after the creation of a new master. A combination of set unions/intersections with algorithmically updated targeting parameters (this is where experimentation in our setup was useful) gave us a 1 round-trip ad targeting call for arbitrary targeting parameters. The 1 round-trip thing may not seem important, but our internal latency was dominated by network round-trips in EC2. The targeting was similar in concept to the search engine example I described last year, but had quite a bit more thought regarding ad targeting. It relied on the fact that you can write to Redis slaves without affecting the master or other slaves. Cute and effective. On the Python side of things, I optimized the redis-py client we were using for a 2-3x speedup in network IO for the ad targeting results.

Original title and link: Building an Ad Network Ready for Failure (NoSQL database©myNoSQL)

via: http://dr-josiah.blogspot.com/2011/06/building-ad-network-ready-for-failure.html

I’m still distilling what happened at Reddit the other days when failures of EBS in a single availability zone took Reddit down for many hours:

Unfortunately, EBS also has reliability issues. Even before the serious outage last night, we suffered random disks degrading multiple times a week. While we do have protections in place to mitigate latency on a small set of disks by using raid-0 stripes, the frequency of degradation has become highly unpalatable.

[…] we have been working to completely move Cassandra off of EBS and onto the local storage which is directly attached to the EC2 instances. […] While the local storage has much less functionality than EBS, the reliability of local storage outweighs the benefits of EBS.

After the outage today, we are going to be investigating doing the same for our Postgres clusters.

One mistake we made was using a single EBS disk to back some of our older master databases

Maybe these will sound as truisms to those working on high available systems, but not for everybody else:

• when talking high availability, running your application from a single Amazon availability zone is not enough

• even if EBS promises “highly available, highly reliable storage volumes”, a solution relying on it will have to account for: 1) failures; 2) unreliable performance.

  An ex-Reddit engineer posted details about the serious issues Reddit noticed while using Amazon EBS.

• Dynamo-style NoSQL databases — where all nodes in a cluster are equal — are able to tolerate failures easier than traditional RDBMS.

  Reddit is working on moving Cassandra off the EBS and onto the local ephemeral EC2 storage.

• A master/slave replication model combined with the out-of-order commits issue makes me think that the cloud and RDBMS are not yet perfect together.

  Data which had been committed to the slaves was not committed to the masters. In a normal replication scenario, this should never, ever happen. The master commits the data, then tells the slave it is safe to commit the same data.

• One mistake we made was using a single EBS disk to back some of our older master databases

• remember the Amazon EBS vs SSD: Price, Performance, QoS?

What else can we learn from Reddit’s experience?

Original title and link: Reddit’s Story of Running Cassandra & PostgreSQL on Amazon EBS (NoSQL databases © myNoSQL)

via: http://blog.reddit.com/2011/03/why-reddit-was-down-for-6-of-last-24.html

I usually cross the line and post about RDBMS when there are interesting things that can be learned. Robert Hass explains some PostgreSQL knobs that can be turned to make things faster. But more interesting is what these knobs are doing: some are disabling fsync, others are disabling the write-ahead-log, others are making the commits asynchronous. Even more interesting is that all these knobs are in the end trading off durability for speed.

I think that, in the future, we may be able to provide more options to allow people to relax the data integrity guarantees that PostgreSQL provides in controlled ways. For example, I can imagine a “dirty read” table, where transactions are not used; instead, rows become visible as soon as they’re inserted, and disappear as soon as they’re deleted. Such a table would be unsuitable for many business applications, but if your application only does single-row operations indexed by primary key, it might work just fine; and it would open up a number of interesting optimization opportunities that aren’t available for ordinary tables. Or, you might have a “no snapshot” table, where rows don’t become visible until the inserting transaction commits, but we make no attempt to guarantee serializability: rows appear pop into existence the instant they’re committed, and disappear out from under you if a deleting transaction commits.

Original title and link: PostgresSQL: How to Make it Faster (NoSQL databases © myNoSQL)

via: http://rhaas.blogspot.com/2010/11/when-your-data-isnt-made-of-gold.html

Cheap:

Oracle has shut down servers Sun Microsystems was contributing to the build farm for open source database software, PostgreSQL, forcing enthusiasts to scramble to find new hosts to test updates to their software on the Solaris operating system.

Keep in mind that these were 3 (three) servers. Not 300, not even 30.

The fact that I cover NoSQL databases doesn’t mean that I don’t care about relational databases or that we will not need them. Having a healthy open source relational database ecosystem is essential. And please don’t say or even think that this will help in any ways the NoSQL community!

via: http://www.itnews.com.au/News/221051,oracle-shuts-down-open-source-test-servers.aspx