The article is about PostgreSQL Tutorial for Beginners and Professionals. It contains introduction, overview, history, advantages, features and other basic information about PostgreSQL. The article is written in simple language and is suitable for both beginners and professionals.
What is PostgreSQL
PostgreSQL is a general purpose, open source object-relational database system. It is known for its reliability, data integrity and correctness. It is compatible with all major operating systems like Linux, UNIX and Windows. It is based on POSTGRES 4.2 and is developed at Berkeley Computer Science Department, University of California. It is an advanced RDBMS which is capable of more than retrieving and updating data. PostgreSQL is fully ACID compliant. It supports foreign keys, joins, triggers, views and stored procedures. It includes SQL:2008 data types like NUMERIC, CHAR, INTERVAL and INTEGAR, etc. The database helps store binary large objects including audio, video and images. It features a native programming interface for C/C++, .Net, Java, Perl, Ruby, Python, etc. Its other sophisticated features include Multi-Version Concurrency Control (MVCC), tablespaces, point in time recovery, nested transactions, asynchronous replication, a sophisticated query planner, etc. It also supports multibyte character coding, Unicode and international character sets. It is highly scalable in both quantity of data to be managed and the number of concurrent users to be accommodated.
PostgreSQL needs minimum efforts as it is quite stable. So, if you develop PostgreSQL-based applications then the total cost of ownership will be low as compared to other database management systems. It is a free database. Its source code is under the license of PostgreSQL. You can modify and distribute PostgreSQL in any form.
Advantages of PostgreSQL
o Open Source DBMS: Among current Open Source DBMS only the PostgreSQL provides unlimited development possibilities. It also enables users to join communities to post or share bugs and difficulties.
o Freedom of use and modify: You can run PostgreSQL for any use. It can be connected to multiple servers, cores and users. You are also free to modify it to fulfill your needs.
o Unlimited copying and distribution: PostgreSQL allows unlimited copying and distribution.
o ACID and Transaction: PostgreSQL support Atomicity, Consistency, Isolation and Durability (ACID). o Multiple indexing techniques: Apart from the B+ tree index techniques, it also provides various other techniques like GIN (Generalized Inverted Index) and GST (Generalized Search Tree), etc. o Full-text search: It offers full-text search when searching for strings. o Diverse replication methods: It supports a variety of replication methods like cascading, Slony-I and Streaming replication. o Diverse extension functions: It is compatible with different techniques which are used to store geographic data such as Key-Value Store, PostGIS and DBLink. o Cost-effective: It is designed to have lower maintenance. o Cross-platform: It is compatible with almost all brands of Unix and with Windows via the Cygwin framework.
o Suitable for high volume environments: It is based on the multiple row data storage strategy (MVCC) which makes PostgreSQL highly responsive in high volume environments. o Complete Internet solution: It comes with everything that you need to Web-enable your company. Your website will be leveraged with PHP 4 Scripting Language, Apache Web Server and the Zone Application server with online access to the PostgreSQL datastore. o Easy migration: It comes with various tools that help migrating data from other DBMS.
A Brief History of PostgreSQL:
o PostgreSQL started its journey as Postgres. It was created by Professor Michael Stonebraker at UCB. He started Postgres as a follow-up project to Ingres in 1986. o Ingres was developed between 1977 and 1985 according to classic RDBMS theory. Later in I994, it was acquired by Computer Associates. o Postgres was developed between 1986 and 1994. Its development included the development of INGRES concepts focused on query language Quel and object orientation. Its development was not based on the code base of INGRES. It was commercialized as illustra and bought by Informix. Later in 2001, Informix was bought by IBM. o Postgres95 was developed between 1994 and 1995. Two Ph.D. students, Jolly Chen and Andrew Yu at Stonebanker’s lab replaced the POSTQUEL query language of Postgres with a subset of SQL and renamed it Postgres95. o In 1996, a group of developers outside of Berkeley science department realize the potential of the system and devoted themselves to the development of Postgres95. Over the next eight years, this group transformed the Postgres. The group created detailed regression tests for quality assurance, fixed bugs, set up a mailing list for bug reports, and brought consistency to the code base. They also filled the various gaps like documentation for users and developers. After it is transformed into new database it started a new life in open source world with various new features and it took its current name PostgreSQL.
o PostgreSQL started with version 6.0, over the next four years it moved from version 6.0 to version 7 which was loaded with major improvements and new features such as: Unique SQL features: Many new features were added like subsets, constraints, defaults, foreign keys, primary keys, quoted identifiers, type casting, binary and hexadecimal integer input. Multiversion Concurrency Control (MVCC): The Multiversion concurrency has replaced the table-level locking. It allows online backups when a database is running and enables readers to read consistent data. Better built-in types: Improved native types were added including the various date/time types and extra geometric types. Improved Speed: Speed and performance were increased by 20 to 40 percent and the backend start-up time was reduced by 80 percent. o During next four years after the release of versions 7.0 and 7.4 again a number of features were added to PostgreSQL. These features were Write-Ahead Log (WAL), prepared queries, SQL schemas, outer joins, SQL92 join syntax, complex queries, TOAST, IPv6, full-text indexing, auto-vacuum, improved SSL support, database statistics information, table functions, an optimizer overhaul, Perl/TCL procedural languages, Python, etc. Today, PostgreSQL has a large user base and it continues to improve. Version 8.0 of PostgreSQL is supposed to have features like tablespaces, point in time recovery, nestled transactions and java stored procedures. A number of organizations including government entities and companies use PostgreSQL. You can easily find it in ADO, NTT Data, CISCO, NOAA, The US Forestry Service, Research in Motion and in The American Chemical Society.
Minimum software requirements to build PostgreSQL:
The software packages required to build PostgreSQL are:
o GNU make version 3.80 or the latest version is required as other make programs or other older GNU versions are not useful. o In addition to either gzip or bzip2 you will need tar to unpack the source distribution. o The GNU Readline library which allows psql to remember each command typed by you and allows to use arrow keys to edit and recall previous commands. If you don’t want to use this option then specify the –without – readline option to configure. BSD-licensed libedit library can be used an as alternative. The libedit library is used if libreadline is not found or if—with-libedit-preferred is used as an alternative to configure. o The zlib compression library, if you don’t need it then specify the –without-zlip option to configure.
Minimum production requirements for PostgreSQL:
o 64bit CPU: 32 bit CPU doesn’t offer scalability and it won’t properly access 4Gig of memory. So if you want to grow this database or scale the database you will need 64 bit CPU. o Memory: PostgreSQL can be operated on less than 2G of memory but remember having more memory will help your database perform better. Your app doesn’t need 2G but other things running behind the scene may consume memory i.e. Processes like autovacuum take memory without being noticed by the operator. o Dual Core/CPU: With Dual Core CPU you can have a more balanced load when routine maintenance tasks run. o RAID: It should not be less than RAID 1 e.g. RAID 0 or non-raid is not acceptable. RAID 10 would be preferred than RAID 1.
Development Environments for PostgreSQL:
Modern IDEs are used in the areas of visual development and programming. They enable you to move programming blocks to develop applications quickly. IDE is also used to compile and execute code. A number of IDE are available for PostgreSQL. Some of the popular IDE used for PostgreSQL are described below: PGADMIN III: pgAdmin comes with Window installer so when you use window installer to install PostgreSQL the IDE is installed by default. pgAdmin III is an open source management tool which features full Unicode support, fast multithreaded query and data editing. It is one of the most popular IDE for PostgreSQL and the best thing is that it supports all datatypes of PostgreSQL. pgAdmin III is available on GNU/Linux, MS Windows, MAC OS X, FreeBSD, OpenBSD and Solaris platform. It is offered in more than thirty languages and considered a powerful tool to write SQL Queries and administrator complex PostgreSQL tasks.
PHPPGADMIN: phpPgAdmin is a very popular IDE in the open source development environment. To use this IDE PHP and Apache should be installed on your system. It is a web-based administration utility which supports both basic and advanced features of PostgreSQL. It is available in 27 different languages and is compatible with master-slave replication engine. It also runs via browser like phpMyadmin. SQUIRREL: Squirrel SQL Client is available as open source software. It is also a database administration tool which allows application to connect with database via the JDBC driver. Apart from PostgreSQL it is also used for over 60 different databases. It can be used for any platform which supports JVM. It also enables you to perform some of the difficult tasks like listing all keywords reserved in the database and copying database table from one database to another database. It is a powerful tool which integrates with all datatypes of PostgreSQL.