The PostgreSQL platform.

Site Admin
Posts: 178
Joined: Tue Sep 29, 2009 6:25 pm
Location: Moss Norway

The PostgreSQL platform.

Postby KBleivik » Mon Apr 22, 2013 3:34 pm

1. Introduction and download

PostgreSQL announces itself as

The world's most advanced open source database

and it has a lot of advantages, among them

Immunity to over-deployment

Over-deployment is what some proprietary database vendors regard as their #1 licence compliance problem. With PostgreSQL, no-one can sue you for breaking licensing agreements, as there is no associated licensing cost for the software.

This has several additional advantages:
  • More profitable business models with wide-scale deployment.
  • No possibility of being audited for license compliance at any stage.
  • Flexibility to do concept research and trial deployments without needing to include additional licensing costs.

PostgreSQL is highly Customizable:

PostgreSQL runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL. Included with its standard function library are hundreds of built-in functions that range from basic math and string operations to cryptography and Oracle compatibility. Triggers and stored procedures can be written in C and loaded into the database as a library, allowing great flexibility in extending its capabilities. Similarly, PostgreSQL includes a framework that allows developers to define and create their own custom data types along with supporting functions and operators that define their behavior.

If you prefer to use this database system and are looking for a hoster, you should know that not all hosters offer this platform.

PostgreSQL can be downloaded from different sources and as part of different packages. The PostgreSQL site is one place. One popular package is: ... pgdownload. OtherAnother popular packages are WAPP stack from BitNami: if you use Windows or LAPP stack for Linux and MAPP stack for Mac OS X. Those stacks also includes SQLite. Personally I use the
package that can be downloaded from enterprisedb. That package also includes a lot of related software, like pgAdmin III and phpPgAdmin. The EXPLAIN and EXPLAIN ANALYZE statement is important if you want to want to optimize your queries. A tool Visual Explain comes with pgAdmin III.

Visual explain
Complicated query plans can be difficult to read, with only the indentation level suggesting how nodes that fit into one another are connected. One way to help visualize complicated plans is to graph them using visual explain, a feature
available in the pgAdmin III tool:

Source: PostgreSQL 9.0 High Performance: ... mance/book page 242.

If you have enough time, you may test all three packages to see which fits your needs best.

2. Feature Matrix, documentation, and history


Manuals: ... grade.html

High Availability, Load Balancing, and Replication


3. Performance and testing

This is a huge subject that ranges from hardware, hardware configuration, database implementation, indexing and query optimization. When you are testing your installattion, you need data.

For some database administrators and application developers, query optimization is the most important part of database performance tuning.


Obtaining sample data of a significant size for benchmarking purposes is a never ending struggle, because the pace of hardware progress means yesteryear's massive test database can be today's trivial workload. A listing that's kept up-to-date with useful new sources for test databases is at and some of these are part of a PostgreSQL sample database project, which has several examples worth checking out.

Source: PostgreSQL 9.0 High Performance: ... mance/book chapter 10. So if you need test data, got to:
Pg Sample Databases The cited book explains how to use the dellstore2 database.

EXPLAIN basics

If you have a query that's running slowly, the first thing to try is running it with EXPLAIN before the statement executing. This displays what's called a query plan, the list of what's expected to happen when that query is executed. If you instead use EXPLAIN ANALYZE before the statement, you'll get both the estimation describing what the planner expected, along with what actually happened when the query ran.

That book is a must if you want to optimize your queries. Database indexing has its own chapter, and there is much more about database perfromance tuning in the book.

4. Community

5. PHP GUI Tools

Additional PostgreSQL-related software

Beyond what comes with the PostgreSQL core, the contrib modules, and software available on pgFoundry, there are plenty of other programs that will make PostgreSQL easier and more powerful. These are available at sources all over the Internet. There are actually so many available that choosing the right package
for a requirement can itself be overwhelming.

Source: ... mance/book page 16.

PostgreSQL PHP Generator

RISE PHP for PostgreSQL code generator

6. Other GUI Tools

Other PostgreSQL GUI Tools

7. Which database plattform?

For some types of web applications, you can only get acceptable performance by cutting corners on the data integrity features in ways that PostgreSQL just won't allow. These applications might be better served by a less strict database such as MySQL or even a really minimal one like SQLite. Unlike the fairly mature data warehouse market, the design of this type of application is still moving around quite a bit. Work on approaches using the key-value-based NoSQL approach, including CouchDB, MongoDB, and Cassandra, are all becoming more popular at the time of writing this. All of them can easily outperform a traditional database, if you have no need to run the sort of advanced queries that key/value stores are slower at handling.

Source: ... mance/book page 12.

8. Related links

GiST ... pgist.html



PostGIS in Action

Sprout: Object Oriented Programming via plpgsql translator

9. Litterature

Instant PostgreSQL Starter:

PostgreSQL 9.0 High Performance: ... mance/book

PostgreSQL Server Programming: ... mming/book

Instant PostgreSQL Backup and Restore How-to: ... store/book

If you go to Amazon an search for postgresql you find so many books that we don't mention any here. The sort by option on the amazon site may give you what you are looking for.//www//wiki//www//wiki

Return to “Databases and SQL”

Who is online

Users browsing this forum: No registered users and 1 guest