Database driven websites

As Web-based businesses begin to need more full-featured, powerful, and flexible technology systems, many are looking more closely at open-source options. From their operating systems and Web servers to their database management systems, a growing number of businesses find that open-source applications are not only ideally suited to ECommerce but also provide welcome alternatives to the exorbitant licensing fees and slow development cycles that typify proprietary applications.

PostgreSQL – An open source DB

Of the open source database options, PostgreSQL is the most robust, object-relational database management system. PostgreSQL, also known as Postgres, has been open source since its inception and evolves quickly due to the committed, active community of developers behind it. From one release to the next, Postgres adds major features within a matter of months, equivalent to upgrades that commercial software companies take years to introduce. The unprecedented rate of revision stems from hundreds of loyal and enthusiastic developers and users who brainstorm fixes and test the latest tweaks, quickly reporting results from all kinds of applications, configurations, and platforms. Postgres’ latest version, 9.6.8, is close to clearing the final hurdles to widespread adoption by enterprise-level businesses. Its next iteration, 10.3, will likely propel it into a position that seriously threatens the reigning commercial giants. The license under which the software is distributed, which hails from its roots at the University of California at Berkeley, is among the simplest and least constricting of open source licenses—furthering its implicit appeal to business users.

MySQL, the competitor

MySQL is a fundamentally different product from Postgres or InterBase, with different strengths and primary uses. It’s a basic, stripped-down database that quickly serves up data to limited numbers of users. It’s fast read performance, along with easy integration with Web scripting languages such as Perl and PHP, make it a favorite among Webmasters. MySQL is well suited to processing simple data on Web sites and a popular choice for building fairly low-traffic sites. The project recently moved from a rather constrained licensing scheme to the full GNU Public License (GPL) favored by many open-source projects and has started to add elements of other existing software to address the feature gap it faces with PostgreSQL and InterBase.

History of PostgreSQL

PostgreSQL’s earliest ancestor was Ingres, developed at the University of California at Berkeley (1977 to 1985). The Ingres code was later enhanced by Relational Technologies, which subsequently became Ingres Corporation, producer of one of the first commercially successful relational database servers. Michael Stonebraker, also at UC Berkeley, led the development of the object-relational database server called Postgres (1986 to 1994). Two Berkeley graduate students, Jolly Chen and Andrew Yu, later added SQL capabilities to Postgres. The resulting project was called Postgres95 (1994 to 1995). Users on the free program’s expanding mailing list continued to send in bug fixes and enhancements.

By the summer of 1996, in response to the demand for an open-source SQL database, a team formed to continue Postgres’ development. Mark Fournier of Toronto agreed to host the mailing list and provide a server for the source tree, and three other developers also became heavily involved in their spare time. With a thousand subscribers on the mailing list, a server was configured that gave a few developers login accounts to apply patches to the source code. Later that year, the system’s name was changed from Postgres95 to PostgreSQL to honor its Berkeley origins and the program’s SQL capabilities. Initially, the project had trouble attracting skilled developers, as the code is well structured but highly complex. Eventually, the project’s challenging nature, and the core team’s collegiality and the well-run organization began to attract experienced developers. A passionate, dedicated community of Postgres users, problem-solvers and developers was born and continues to thrive. (The project’s online headquarters is located at

What up there in PostgreSQL?

As of its latest release, PostgreSQL is fully ANSI-SQL-92 entry-level compliant, supporting most SQL constructs, including transactions, subselects, and user-defined types and functions. Standards compliance is critical for any open-source database’s acceptance—and a way in which open-source technologies can further differentiate themselves from their closed, proprietary competitors. Corporate infrastructures, often managed by several generations of IT staff, must be able to interoperate with current code and data sources. PostgreSQL supports a range of open-source and proprietary operating systems, including Linux, the various Berkeley Software Development (BSD) systems, Solaris, HP-UX, AIX, Irix, and Windows NT/2000. Already, PostgreSQL provides a powerful technical infrastructure for a wide range of companies that need Web sites, intranets, extranets, and eCommerce applications to conduct their business.The current version (When we updated this article, on April 2018) of PostgreSQL is 10.3.

Some of the features of PostgreSQL are

  • Users can add new functions and operators. User-defined functions allow database designer to encapsulate business logic in the database, rather than in the front-end.
  • Server-side user-defined functions can be written in several languages like C, SQL, PL/pgSQL (very similar to Oracle’s PL/SQL), Tcl, Perl, Python (alpha) and Ruby.
  • Date/time data types cleanup: The date/time data types now comply with the SQL standard, replacing partially implemented SQL date/time types with full-featured implementations. The default display format for date/time data has also changed to ISO (International Organization for Standardization) style.Some of the Limitations of PostgreSQL are
    • The maximum size for a database – unlimited (60GB databases exist).
    • The maximum size for a table 64 TB on all operating systems.
    • The maximum size for a row unlimited in 7.1 and later.
    • The maximum size for a field 1GB in 7.1 and later.
    • The maximum number of rows in a table unlimited.
    • The maximum number of columns in a table 1600.
    • The maximum number of indexes on a table unlimited.

PostgreSQL in the Real World

The majority of open-source database users are Web and software developers, small to mid-size e-businesses, systems integrators, and value-added resellers (VARs). These businesses integrate the databases with other open-source applications, yet many open-source databases can also run on proprietary platforms.
Wireless Developer Network (, an online portal for software developers that focuses on wireless communication technologies, runs 12 servers with PostgreSQL, Red Hat Linux, Apache Web servers, and PHP scripting. The network uses PostgreSQL 7.0 for its content management and to drive eCommerce applications such as book sales, message boards, mailing lists, and software sharing. The network and the GeoCommunity, its sister site that runs on the same platform, services 150,000 visitors each month. The sites haven’t experienced any crashes or data loss, according to the network’s managing editor Bryan Morgan.

Time for a Change

Web businesses demand increasingly complex, mission-critical applications of their databases, from supply-chain and customer relationship management (CRM) to data mining and analysis, along with a wide range of eCommerce functions. As these businesses grow and their needs expand, many find closed-source, proprietary development is neither flexible nor dynamic enough to keep pace with their needs. Open-source databases offer businesses greater control over their own software and let them adapt its features to suit their business models’ fluid demands.
So many businesses are waking up to open source’s potential that Forrester Research recently predicted open-source products will radically reshape the software industry by 2004. The Internet grew up in an open-source environment, so it’s no great surprise that the open-source development model has spawned some of the best applications and tools for Web-based businesses. Forrester Research’s analysts suggested that within a few short years, proprietary “captive” developers would no longer be able to compete against the “hordes of Internet-armed revolutionaries” that make up open-source development communities, suggesting the eventual collapse of the closed-source software development model. One of that model’s leading proponents, Oracle president Larry Ellison, recently astounded an audience by saying, “choice is not necessarily a good thing” when it comes to configuring business solutions. The world’s second-richest man concluded, “We would like every one of our customers to run the identical software configuration.” The market for open-source databases on the Web is indeed ripe.

* This article is a collection of information from various resources. All Names belong to their respective owners. PostgreSQL is an open source DB tool with GNU.

Related Resources

Stored Procedures Tips

Stored Procedures Tips

Web Databases

Here is some practice tips which you can remember and apply when writing SPs Use SET NOCOUNT ON, this will reduce unnecessary return values as

Using Stored procedure with mySQL and PHP

Using Stored procedure with mySQL and PHP

Web Databases

Writing external scripts to perform complex data handling is a tedious affair. The best way to automate tasks straightaway into the server is by using

A to Z of stored procedures for ASP and VB Programmers

A to Z of stored procedures for ASP and VB Programmers

Web Databases

The article is meant for ASP and VB Programmers, who always try to write hard core SQL statements embedded in their coding to communicate with