DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > A Few Reasons to Switch to PostgreSQL 9.0

A Few Reasons to Switch to PostgreSQL 9.0

Mitch Pronschinske user avatar by
Mitch Pronschinske
·
May. 06, 10 · Database Zone · Interview
Like (0)
Save
Tweet
18.82K Views

Join the DZone community and get the full member experience.

Join For Free
postgresql is widely considered to be the main open source competitor to mysql.  it's been five years since the release of postgresql 8.0, but this week, postgresql developers showed that their project has evolved significantly in that time.  the beta release of postgresql 9.0 has burst onto the scene with handsome new features such as hot standby and streaming replication, which are making it even more lucrative to switch from mysql to postgresql.

1. hot standby

this feature allows connection to a server that is in archive recovery mode. users with a server in recovery mode can still process read-only queries and move to normal operations without disconnecting.  there are only a few usage and administrative differences when a server is running queries in recovery mode.  hot standby greatly improves tasks such as log shipping replication and precise restoration of a backup state.

2. streaming replication

streaming replication allows a standby server to connect to the primary server and receive a stream of wal (write ahead log) records as they are generated, instead of having to wait for those records to be written to disk and picked up later.  this allows the standby to be more up to date than it is with file-based log shipping.  the streaming replication is asynchronous, so there's a slight delay between committing a transaction in the primary and seeing it in the standby, but it's much shorter than with file-based shipping.  archive-timeout is no longer required to reduce the data loss window thanks to streaming replication.

3. a few other features

the listen/notify events in postgresql 9.0 have been moved from a system table to a memory queue for better performance.  notify is now able to pass an optional string to listeners.  postgresql 9.0 also includes sql compliant per-column triggers and anonymous functions using the do statement.  server side language support has been enhanced as well.
exclude can now be used in the create table statement as a non-traditional constraint. in the documentation you can find an example of how exclude can ensure that no two records contain overlapping circles .

4. more in store

where a project is going is just as important (if not more important) than where it currently is, if you're considering migration to that project.  a blog from robert haas indicates that the postgresql project will continue adding features that distinguish it from competitors.  there are several big patches planned for versions 9.1, 9.2, and beyond.  knngist will allow the use of indices to accelerate order by queries.  partitioning support will be improved with built-in syntax to reduce the amount of manual setup required.  another feature will be index-only scans, which reduce i/o for index scans by intelligently skipping or delaying tuple visibility checks.
___
the postgresql developers originate from several major open source companies including red hat, f-secure, and enterprisedb (a commercialization of postgresql). postgresql is released under the postgresql license, which is a liberal license that's similar to the bsd or mit licenses.
PostgreSQL Database

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Fintech and AI: Ways Artificial Intelligence Is Used in Finance
  • How To Integrate Event Streaming Into Your Applications
  • What Is ERP Testing? - A Brief Guide
  • OLAs vs. SLAs vs. UCs: What They Mean and How They're Different

Comments

Database Partner Resources

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo