PostgreSQL
Databases are indispensable when it comes to contemporary web applications. True - there are many personal static web pages on the Internet working without databases, but in today's dynamic information stream static pages are archaic. Dynamic content is starting to replace static content even on personal pages. When it comes to corporate business sites the presence of databases is a mandatory element.
Building a website which uses databases has many advantages. The whole content is structured, easily accessible and editable, and can be transferred or exported to another web application or a newer version of the latter. And while most of the amateur, non-professional, or open source projects use the light, quick and free MySQL databases, the professional web environments count on expensive powerful databases offering a higher level of security such as Oracle, MsSQL, Sybase. And as a free alternative to powerful corporate applications comes PostgreSQL.
Content:
PostgreSQL's history
PostgreSQL's origins lead us back to the 1970s. Back then, at the University of Berkeley, California, a new database software was created - Ingres. This software became the foundation of other popular database solutions, such as Sybase, MsSQL and NonStop SQL. However, in 1985, the leader of the Ingres project started a new, post-Ingres project, with which he aimed to address the database problems at the time. The new project was based on the ideas used in Ingres, but not on its source code. In the following years the project was completed and several versions of the Postgre database were released. Back then, it was not based on SQL, but on the QUEL query language. The last version using the QUEL interpreter was released in 1993.
In 1994, two Berkeley graduates converted Postgre to use the SQL interpreter instead of the QUEL one and labeled their version Postgre95. Since then, the SQL version gained huge popularity and in 1996 the project was officially renamed PostgreSQL.
PostgreSQL was licensed under the BSD license, which allows for the product to be modified and then distributed commercially. Due to this fact, there are a lot of companies, which offer commercial distributions of the PostgreSQL software, alongside with the free version.
PostgreSQL features
-
Objects defined by the user
The user can create new types of almost all the objects inside the database:
- Conversions
- Data types
- Domains
- Functions, including aggregate functions
- Indexes
- Operators
-
MVCC
MVCC stands for Multi-Version Concurrency Control - it allows the user to make changes to the database, which will remain invisible to others until the transaction is commuted. This way, the need for read locks is eliminated. Usually, the reader of the database must wait for the writer to finish and vice versa, since most database software applications rely on table-level, page-level, column-level, or row-level locking.
In PostgreSQL, each row in a table has two transaction IDs - a creation and an expiration transaction ID for the transactions that created and expired the row. When there is an UPDATE, PostgreSQL creates a new row and expires the old one. The new row is the same as the old one, just a different version.
MVCC also gives another edge to PostgreSQL - the ability to create live backups of the database, i.e. it can create backups for tables which are used at that moment - no matter if data is inserted, deleted or updated at the moment of the backup. With a database software application lacking MVCC, the table will have to be locked before a backup is created.
-
Database triggers
Database triggers are events, executed when a specific command is entered in a table or a database. They are most often used to verify input data or to restrict access. There are several classes of triggers - "BEFORE" and "AFTER" triggers, depending on the time of the trigger execution. There is also the "INSTEAD OF" trigger, which will execute in place of the triggering statement.
Mostly, triggers are used with the UPDATE, DELETE or INSERT statements.
An example of How to Create Table in PostgreSQL
CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));An example of how to Insert data into PostgreSQL table
INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America'); (inserts data into phonebook table)An example of how to Select data from PostgreSQL table (1)
SELECT * FROM phonebook ORDER BY lastname; (returns all data for lastname)An example of how to Select data from PostgreSQL table (2)
SELECT * FROM phonebook WHERE lastname = 'Doe'; (returns all data for Doe as a family name)An example of how to Select data from PostgreSQL table (3)
SELECT * FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe'; (returns all data for the first and the last name)An example of how to Update data in PostgreSQL database
UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe'; (updates the address and the telephone number of all persons named John Doe)An example of how to Delete data from PostgreSQL database
DELETE FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe'; (deletes all data for John Doe) -
Indexes
In PostgreSQL, the developers can enjoy the built-in support for several types of indexes: B+, hish, GiST and GiN. Additionally, everybody can create their own indexing method. In PostgreSQL, the indexes support several additional features:
- backward index scanning
- PostgreSQL can go through multiple indexes at the same time to satisfy a query
- the user can create partial indexes using the WHERE statement when creating a new table. This significantly reduces the index size.
- PostgreSQL allows the creation of expression indexes with an index of the result of an expression or function. The creation of expression indexes is not limited to the value of the columns.
-
Data types
PostgreSQL supports several native data types:
- arrays
- variable length text
- IPv4 and IPv6 addresses
- XML with support for Xpath queries
Besides the native data types, PostgreSQL allows the users to create their own data types, which are later indexed using the GiST indexing type.
PosgreSQL Data types Name Aliases Description bigint int8 signed eight-byte integer bigserial serial8 autoincrementing eight-byte integer bit [ (n) ] fixed-length bit string bit varying [ (n) ] varbit variable-length bit string boolean bool logical Boolean (true/false) box rectangular box in the plane bytea binary data (“byte array”) character varying [ (n) ] varchar [ (n) ] variable-length character string character [ (n) ] char [ (n) ] fixed-length character string cidr IPv4 or IPv6 network address circle circle in the plane date calendar date (year, month, day) double precision float8 double precision floating-point number inet IPv4 or IPv6 host address integer int, int4 signed four-byte integer interval [ (p) ] time span line infinite line in the plane lseg line segment in the plane macaddr MAC address money currency amount numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision path geometric path in the plane point geometric point in the plane polygon closed geometric path in the plane real float4 single precision floating-point number smallint int2 signed two-byte integer serial serial4 autoincrementing four-byte integer text variable-length character string time [ (p) ] [ without time zone ] time of day time [ (p) ] with time zone timetz time of day, including time zone timestamp [ (p) ] [ without time zone ] date and time timestamp [ (p) ] with time zone timestamptz date and time, including time zone -
Cross platform
Another great advantage of the PostgreSQL platform is that it is available on almost any operating system in use. It has a native support for all UNIX environments and in the latest version native support for Microsoft's Windows has been included. This allows for an easy migration between systems, no matter how they are set up.
-
Extensible
PostgreSQL is a completely free, open source-based database. Its license allows everyone to make modifications to the core functionality, adding new or improving the existing features. Thanks to this, PostgreSQL has a very big community of users, who modify it on a daily basis. PostgreSQL also allows commercial versions to be released, and there are several companies that have made a modified commercial version of the PostgreSQL software.
Enterprises using PostgreSQL
Among the Internet community, PostgreSQL is known as the most stable and powerful open source database software available. This is why several big enterprises have adopted PostgreSQL as their database software of choice. Among them are Yahoo, Whitepages.com, the U.S. State and Labor Departments, Greenpeace, IMDB (the Internet Movie Data Base) and Skype.
PostgreSQL with NTC Hosting
NTC Hosting offers its business clients and those whose sites serve many visitors a professional PgSQL Hosting solution. Our PgSQL hosting plans allow you to create between 1 and 5 databases and to use an up to 160GB database quota.
Postgre SQL is included in our Value, Plus and Exclusive plans and as an optional feature in the rest of our plans. If, at any point, you decide that you need additional PgSQL databases, you can always purchase more from the Add Services/Upgrades section.
Resources: