The Basic Concepts of PostgreSQL

As mentioned earlier, PostgreSQL is an object-relational database server. The main goal of PostgreSQL development is to be 100% ANSI SQL “compatible, and PostgreSQL is close to achieving that goal. SQL92 compliance offers many advantages over proprietary functions and features: Applications can easily be ported to or from PostgreSQL to other systems, and new users can easily use their knowledge of SQL when switching to PostgreSQL.

Relational databases are often not suitable for modeling complex scenarios. Object-relational databases, such as Oracle or PostgreSQL, offer powerful features to enable more complex software design than would be possible with relational databases.

Here are the key features of PostgreSQL's object-oriented data model:

• Classes

• Inheritance

• Function overloading (supported by PL/pgSQL)

You will take a closer look at object orientation in Chapter 3, "An Introduction to SQL."

PostgreSQL is an extremely modular piece of software; you can easily insert functions or design programming interfaces. A huge number of datatypes is supported, and you can easily design and implement your own datatypes. PostgreSQL's modularity enables users and advanced programmers to achieve a goal quickly. Programming interfaces for PostgreSQL are available for all important programming languages, such as C, Perl, Python, Tcl, Java, and PHP.

PostgreSQL not only provides external programming interfaces. With a powerful language called PL/pgSQL (which is similar to Oracle's PL/SQL), you can make packages that can be integrated directly within the database. PL/Perl, PL/Tcl, and ECPG are additional easy-to-use interfaces for PostgreSQL.

One advantage of PostgreSQL is that it can easily be handled. PostgreSQL fits perfectly well into UNIX systems, but can also be used on other platforms, such as Microsoft Windows. An increasing number of graphical user interfaces is already available, making PostgresSQL administration even more comfortable. Web interfaces especially seem to be the standard for doing administration work.

Software Architecture

Before you get deeper into PostgreSQL, let's look at a brief overview of PostgreSQL's software architecture. Knowing the basic concepts and facts makes using the database much easier and gives you a deeper insight into what is happening.

PostgreSQL uses a rather simple client/server model, with one process per user request. Unlike Apache, PostgreSQL is not a preforking server, which means that a process is started when a SQL query has to be executed. No spare servers have to stay in memory ”every process is a working process.

The system consists of three major components :

• Postmaster

• Backend

• Frontend

The Postmaster

The postmaster is the supervisor daemon and has to be up and running if someone wants to access the database. One postmaster daemon can handle various databases. If a request has to be processed , the postmaster starts a backend process. The postmaster also manages the shared memory pool of the database server.

Usually one postmaster is running per machine, but you can run multiple postmasters. In that case, every postmaster has to use its own port and data directory to work correctly.

The Backend

The backend is used to execute a SQL query. If many queries have to be executed simultaneously , many backend processes are started by the postmaster.

The maximum number of backends allowed can be defined by the database administrator.

NOTE

Currently the postmaster and the backends have to run on the same machine.

The Frontend

With the help of the frontend, users can connect to their PostgreSQL database server. Frontends and backends need not run on the same machine. You can use psql, for example, to connect to a database running on a remote machine.

When a user wants to connect to a PostgreSQL database, the connection is established by the frontend. The postmaster starts a backend process, and from then on, the frontend communicates with the backend without the help of the postmaster.

Limitations of PostgreSQL

PostgreSQL has some minor restrictions. The size of a database is actually unlimited and depends only on the amount of available HDD space and RAM the system has.

The size of a table is restricted to 64 terabytes (TB) on all operating systems; single rows can have unlimited size (since 7.1). Single fields can be up to 1 gigabyte (GB) (since 7.1). This is especially important when working with binary large objects (BLOBs).

The number of rows in a table is unlimited; a table can have up to 1,600 columns .

You can see that PostgreSQL is capable of handling large amounts of data. When building really big databases, consider that PostgreSQL's limitations are not the only ones a developer has to face. Many file systems can't be as big as a PostgreSQL table. Check out the documentation of the file system you are using when building a large database. If a table becomes very big, PostgreSQL splits the table in multiple files.

We have worked with databases larger than 30GB and PostgreSQL was stable even when performing complex queries. If 64TB per table is not enough for your application, feel free to split the table.