SQL

PostgreSQL 9.1 Released

/*
Among the many new features, here's a snippet from the News page:
*/

Advancing the State of the Art
Our community of contributors innovates with cutting-edge features. Version 9.1 includes several which are new to the database industry, such as:

K-Nearest-Neighbor Indexing: index on "distance" for faster location and text-search queries

Serializable Snapshot Isolation: keeps concurrent transactions consistent without blocking, using "true serializability"

Writeable Common Table Expressions: execute complex multi-stage data updates in a single query

Security-Enhanced Postgres: deploy military-grade security and Mandatory Access Control

/*
The SE-Postgres looks to be particularly interesting. It allows you to use SELinux Mandatory Access Controls on the PostgreSQL users and data. Neat. Particularly the SECURITY LABEL.
*/

/*
Beware, however, that there are some incompatibilities with previous releases!

From the 9.1 Release Notes:
*/

Change the default value of standard_conforming_strings to on (Robert Haas)

By default, backslashes are now ordinary characters in string literals, not escape characters. This change removes a long-standing incompatibility with the SQL standard. escape_string_warning has produced warnings about this usage for years. E'' strings are the proper way to embed backslash escapes in strings and are unaffected by this change.

/*
Also from the Release Notes, it mentions the addition of synchronous replication.
*/

PostgreSQL streaming replication is asynchronous by default. If the primary server crashes then some transactions that were committed may not have been replicated to the standby server, causing data loss. The amount of data loss is proportional to the replication delay at the time of failover.

Synchronous replication offers the ability to confirm that all changes made by a transaction have been transferred to one synchronous standby server. This extends the standard level of durability offered by a transaction commit. This level of protection is referred to as 2-safe replication in computer science theory.

When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. The only possibility that data can be lost is if both the primary and the standby suffer crashes at the same time. [...] ...it also necessarily increases the response time for the requesting transaction. The minimum wait time is the roundtrip time between primary to standby.

/*
There's also a What's New in PostgreSQL 9.1 wiki page that explains much of these new features in detail.
*/

PostgreSQL 8.3 Cheat Sheet

/*
A very handy cheat-sheet of common PostgreSQL programming features.
*/

pl/pgSQL Programming Guide

With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.

Extra round trips between client and server are eliminated

Intermediate results that the client does not need do not have to be marshaled or transferred between server and client

Multiple rounds of query parsing can be avoided

This can result in a considerable performance increase as compared to an application that does not use stored functions.

Also, with PL/pgSQL you can use all the data types, operators and functions of SQL.

/*
This is a link to the PostgreSQL 8.3 documentation for the pl/pgSQL procedural programming language. You can greatly speed up application performance by moving much of the decision-making to the database.
*/

Greenplum Database

Greenplum Database is the first open source powered database software that can scale to support multi-terabyte data warehousing demands. Greenplum Database allows organizations to analyze vast amounts of business data 10 to 100 times faster than traditional data warehouse solutions at a fraction of the cost.

Key Features of Greenplum Database

• Faster Reporting and Analytics
  
• Dramatic Cost Reduction
  
• Centralized Cluster Management and Administration
  
• Linear Scalability and Flexibility
  
• Modular Performance and Capacity Scale-Out Capability

Greenplum Database's fundamental breakthrough is its ability to store and process terabytes of data using clusters of low-cost servers. Greenplum Database moves processing power as close as possible to the data, so processing always occurs in parallel, delivering a dramatic boost in query and load performance. In addition, Greenplum Database's Dynamic Provisioning technology makes it easy to add incremental data warehouse capacity when needed, avoiding costly appliance upgrades.

/*
It looks like there might finally be a decent option for PostgreSQL scalability. I have not personally used this software, yet, but I will be installing it shortly. I intend to do some benchmarks versus a stand-alone PostgreSQL database and see how the numbers match up.

If this solution is half as good as the website touts it to be, then Oracle may end up losing some serious ground in the SMB (Small-to-Medium Business) market.
*/

High-Level Architecture: A database in Greenplum is actually an array of individual databases, usually running on different servers or hosts, all working together to present a single database image. The Greenplum master is the primary entry point to the Greenplum Database System. It is the database instance where users connect to the database and execute SQL statements. The master coordinates the work amongst the other database instances in the system-the Greenplum segments, which is where the user data resides.

Mirroring and Fault Tolerance: When you deploy your Greenplum Database system, you have the option to configure mirror segments. Mirror segments allow database queries to fail over to a backup segment if the primary segment is unavailable.

Greenplum Database is able to detect when a host is unavailable or when a segment database server process is down. When this occurs the master will mark the primary segments on that host as out-of-service and immediately switch over to the mirror segments so that the operation can continue.

...

Why is Greenplum Database better-suited to business intelligence and data warehousing than databases like Oracle?
Greenplum Database’s “shared-nothing” architecture is optimal for fast queries and loads because it places processors as close as possible to the data itself, and performs queries and other operations with the maximum degree of parallelism possible. “OLTP” architectures like Oracle’s were designed and built with an entirely different purpose and are not capable of the kinds of parallelism, or performance, that Greenplum Database delivers.

How is that Greenplum can deliver a product like Greenplum Database, but no other company or organization has to date?
When the founders of Greenplum converged in 2003, they set out to change the game. They saw that enterprise software, and particularly database software, was far too expensive and performed badly. Our uniquely capable team includes some of the best minds in the industry, with experts from Oracle, Teradata, Sybase, Informix, Netezza, PostgreSQL, HPTi, CalTech, MIT, Stanford University, and other leading companies, organizations and institutions. Greenplum Database is the result of the confluence of Greenplum’s unique vision and vast experience in the midst of undeniable industry trends.