The PostgreSQL project has just released a beta of its next major version,
9.2. As usual with its annual release, this version includes many new
features, most of which are targeted at improving database performance. The
developers have been hard at work improving response times, increasing multicore
scalability, and providing for more efficient queries on large data. They also found time to include some other major features, so let's explore a few of the things 9.2 beta has to offer.
If the new non-relational databases (or "NoSQL") have proved anything, it's that many application developers want to store JSON objects in a database. With version 9.2, that database can be PostgreSQL.
The JSON support in PostgreSQL 9.2 isn't complete JSON database functionality, but it goes a long way toward that. First, there's a validating JSON data type, so that you can create tables with a specific JSON field:
Column | Type | Modifiers
user_id | integer | not null
user_name | text |
user_profile | json |
Better is that there are multiple JSON conversion functions, including row_to_json() and array_to_json(), which allow you to get the results of a query in JSON format.
from ( select schemaname, relname from pg_stat_user_tables )
This means that applications can now send queries to PostgreSQL, get back
results in JSON format, and immediately act on those results without
further conversion. Unfortunately, it is not yet possible to send your
To make the JSON support really useful, though, you need two optional
components, or "extensions" to PostgreSQL: hstore and
PL/v8. Hstore is a data
type that stores indexed
The PostgreSQL project chose to implement its own JSON formatting rather than utilizing any external library, reducing external dependencies and improving portability.
Anyone who's ever written a calendaring application can tell you that there's no such thing as a "point in time". Time comes in blocks, and pretty much everything you want to do with times and dates involves spanning minutes, hours, or days of time. For a long time, the only way relational databases had to represent spans of time was as two endpoints in different fields, an unsatisfactory and error-prone method.
In 9.2, contributor Jeff Davis introduces "range types" which allow the representation of any one-dimensional linear range, including time, real numbers, alphabetical indexes, or even points on a line. Such ranges can be compared, checked for overlap, and even included in unique indexes to prevent conflicts. PostgreSQL is the first major relational database system to have this concept.
To give you a concrete example, imagine you're writing a conference scheduling application. You want to make sure that no room can be scheduled for two speakers at the same time. Your table might look something like this:
CREATE TABLE room_reservations (
room_no TEXT NOT NULL,
speaker TEXT NOT NULL,
talk TEXT NOT NULL,
EXCLUDE USING gist (room_no WITH =, booking_time WITH &&)
That odd "EXCLUDE USING gist" clause says not to let anyone insert a record
for the same room at overlapping times. It utilizes two existing
PostgreSQL features, GiST
indexes and exclusion constraints. This substitutes for dozens of lines of application code in order to enforce the same constraint. Then you can insert records like this:
INSERT INTO room_reservations
VALUES ( 'F104', 'Jeff Davis', 'Range Types Revisited',
'[ 2012-09-16 10:00:00, 2012-09-16 11:00:00 )' );
As you can see, PostgreSQL's range types support mathematical closed and open bracket notation, helping you define ranges which do or don't overlap with adjacent ranges.
Scalability to 64 cores
Thanks to its Multiversion Concurrency Control (MVCC) architecture, PostgreSQL does not need to hold any locks for reading data, just for writing data. This should, in theory, allow for near-infinite multicore scalability of a read-only workload. But in reality, PostgreSQL 9.1 only scaled to around 24 cores before throughput per core fell off sharply. This really irritated PostgreSQL contributors Noah Misch and Robert Haas, so they decided to do something about it.
The main reason was that PostgreSQL was actually holding locks for each read. The biggest of these was a unitary lock on the table to make sure that it didn't get dropped while the read query was still running. When you have a lot of very short queries doing reads against the same table, contention on this table lock becomes a major bottleneck. Through a combination of repartitioning the lock memory space, and reducing the time required to get a lock, they largely eliminated that bottleneck.
Other contributors, such as lead developer Tom Lane, made other
optimizations to the read-only workload, by, for example, reducing memory copying for cached query plans. The University of California at Berkeley donated the use of a high-memory 64-core server for testing. The results of all of these optimizations are gratifying and dramatic.
PostgreSQL now scales smoothly to 64 cores and over 350,000 queries per second, compared to topping out at 24 cores and 75,000 queries per second on PostgreSQL 9.1. Throughput is better even at low numbers of cores. Note that this is on an extreme workload: all primary-key lookups on a few large tables which fit in memory. While it may seem obscure, that workload describes many common web applications (such as Rails applications), as well as the kind of workload many of the new key-value databases are designed to handle.
One of the features other database systems have, which PostgreSQL has lacked, is the ability to look up data only in an index without checking the underlying table. In the databases which support it (such as MySQL and Oracle) this is a tremendously useful performance optimization, sometimes called "covering indexes".
The reason why it's useful is that for very large tables an index on one or
two columns could be 1/100th the size of the table, and is often cached in
memory even when the table is not. Thus if you can touch only the index, you can avoid IO, making your query return twenty times faster. It's even more useful if the table in question is only going to be used to join two other tables on their primary keys.
However, the same MVCC which makes read queries scale so well on PostgreSQL made index-only access difficult. Even if the data you wanted was in the index, you had to check the base table for concurrency information. But then contributor Heikki Linnakangas created a highly cacheable bitmap called the Visibility Map, so that the query executor only has to check the base table for data pages which have been recently updated.
This means that, in 9.2, you'll be able to get your query answered just by the index in many or most cases, speeding up queries against large tables. Yes, this also means an end to most "COUNT(*) is slow on PostgreSQL" issues.
The caveat with this feature is that the table or tables in question need to be fairly up-to-date in database maintenance ("VACUUM"), which limits the ability to use the optimization on tables with a lot of "churn". Regardless, for many common use cases and for data warehouses, index-only access will be an order-of-magnitude performance improvement.
Of course, these days horizontal scalability is a lot more popular than vertical scalability. The PostgreSQL developers, particularly Jun Ishiduka, Fujii Masao, and Simon Riggs, have continued to improve the binary replication introduced in PostgreSQL 9.0. Version 9.2 now contains support for cascading replication, which I will explain by example:
Imagine that you have three load-balancing PostgreSQL servers in Amazon US East, and another cluster of three replicated PostgreSQL servers in Amazon US West for failover in case of another AWS region-wide outage. If you want to use streaming replication, each server in US West needs to replicate directly from the master in US East, driving your transfer costs through the roof.
What you really want is the ability to replicate to database server 1 in US West, and have the two other servers in US West replicate from that server. With PostgreSQL 9.2, you can.
Configuration is fairly simple if you already have PostgreSQL 9.1 replication set up. Simply tell the cascading replica to accept replication connections by setting the wal_senders parameter. Then connect to it from the downstream replicas.
This isn't everything in the PostgreSQL 9.2 beta. There's performance
enhancements for writes such as group commit, a new class of index called
SP-GiST, reductions in CPU power consumption, multiple enhancements to
modifying database schema at runtime, and even several new database
monitoring commands. You can read about the new features in the
PostgreSQL 9.2 beta release
notes and the beta documentation.
Some features, planned for 9.2, didn't make it into this release due to issues found during development and testing. These include checksums on data pages to detect faulty storage hardware, federated databases, regular expression indexing, and "command triggers" which can launch an action based on arbitrary database events. Hopefully we'll see all of these in PostgreSQL 9.3 next year.
The PostgreSQL project hopes you'll download and test the beta to help
identify and fix bugs in version 9.2. If the project holds true to
its timeline for the last couple of years, the final release of version 9.2
should be some time in September. In the meantime, you can download and test the beta version.
to post comments)