Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 2, 2011

PostgreSQL 9.1 Gets Synchronous

By Sean Michael Kerner

The open source PostgreSQL database is gearing up for a major new release.

PostgreSQL 9.1 is now available in beta, introducing a number of new features. Among the new features is support for synchronous replication for the database.

"The basic idea is that you have one or more asynchronous replicas that are set up using binary replication, and each of those replicas can identify themselves by a name," Josh Berkus, PostgreSQL core team member, told InternetNews.com. "By name, the master can say that things will be synchronously replicated to specific replicas in priority order."

Berkus added that in the beta there is also something called transaction-controlled synchronous replication, which provides control over what is and isn't replicated.

"What that means is the database administrator can decide on a per-transaction basis which ones need to be replicated and which ones don't," Berkus said. "The advantage of that is that there are a lot of mixed load applications which might have a small amount of essential data and a large amount of data that is not as important."

The overall advantage of having the transaction control is that it allows an enterprise to define the places where they are willing to pay the response penalty that comes with fully synchronous replication. Berkus said the response penalty for synchronous replication varies according to hardware.

PostgreSQL 9.1 has also made performance improvements that could affect replication, as well as other database operations. One of those performance improvements comes by way of the unlogged tables features. Berkus noted that unlogged tables could be thought of as in-memory tables.

"You can create tables within PostgreSQL that are not logged to the transaction logs for data that is ephemeral," Berkus said. "An example of that is updating current session information in a web application."

Berkus noted that historically, PostgreSQL users have leveraged memcached and other caching solutions in order to reduce the logging overhead.

"Unlogged tables eliminates the logging overhead, and once you eliminate that, the response time of the unlogged table becomes comparable to a non-relational data store," Berkus said.

The unlogged table feature is not intended as a high-availability caching feature. Rather, Berkus explained that use case is for the caching to provide a solution for data that has a high volume of writes, but is not very valuable.

"So in terms of providing a high-availability cache, you're still going to want an actual caching solution," Berkus said.

PostgreSQL 9.1 also adds support for SE-Postgres, which enables integration with SELinux. SELinux is a mandatory access control system, first developed by the National Security Agency and currently widely deployed on Red Hat Enterprise Linux systems. Berkus noted that prior to PostgreSQL 9.1, SE-Postgres was essentially a fork of the mainline. The SELinux capabilities are now being merged into the mainline improving the overall performance of the security implementation.

PostgreSQL 9.1 is also taking aim at extensibility with improved extensions capabilities. Extensions will now also be easier to find with the PostgreSQL extension network.

"We've added a whole packaging feature for Extensions; the idea is to have the same ease as you have with Mozilla add-ons on Firefox," Berkus said.

Sean Michael Kerner is a senior editor at InternetNews.com, the news service of Internet.com, the network for technology professionals.

PostgreSQL Archives

Latest Forum Threads
Postgresql Forum
Topic By Replies Updated
Creating a Table Where Date has to be Now OR Future Shadowayara 1 June 6th, 03:56 PM
Streaming Replication on a Single Database lbergeson 0 January 30th, 03:19 PM
Error - SQL state: 22P02 raj_db 1 December 20th, 08:30 AM
Searching in multi-dimensional array fields hdany 0 December 1st, 06:46 AM