The Postgres system is setup differently than some other database systems, and while long-time Postgres users consider its layout natural, people coming from other database are often initially confused. Therefore, it is helpful to explicitly mention some of those differences.

First, Postgres allows multiple databases in a single cluster (which is controlled by a single postmaster). This often confuses Oracle users because in Oracle you can only have one database in a cluster. This also confuses MySQL users because they have multiple ”databases”, but these databases share a single system catalog and allow cross-”database” queries. Effectively, they are “schemas”, rather than databases. Microsoft SQL has a similar facility to Postgres, with multiple database in a cluster, and multiple schemas in a database.

Continue Reading »

I have already blogged about TOAST storage (and TOAST queries), but I would like to highlight one aspect that is easily overlooked by application programmers. There is an age-old debate over whether using select * is good programming practice — many feel that wildcarding all columns of a table makes applications more brittle when columns are added or removed, while others feel that specifying all columns in an application is more error-prone. If a table has only a few columns, specifying them is pretty easy, but when the number of columns is large, specification of column names can be cumbersome. Of course, having wide rows in tables has its own issues, but I don’t want to get into that here — what I do want to cover is its affect on toast‘ed values.

As I mentioned in my previous blog post, long values are not stored in the main row, but in toast tables that have additional access overhead. This is great when you only need the short values from a row, which is typical, but what happens when you use select *? Well, if you actually need all the column values, using select * or specifying the column name explicitly makes no difference, but many applications use select * when they only need some of the column values, and this is where toast becomes an issue. If you don’t need to see some columns, and those columns might be long and hence toast‘ed, it is wise to avoid selecting them in a query — this will avoid toast table access and speed up queries significantly.

Continue Reading »

You might have seen shared_preload_libraries in the postgresql.conf configuration file, but its usefulness might have been unclear — I would like to explain its purpose here.

Unlike traditional database systems, Postgres is an object-relation database system, meaning it relies heavily on plug-in objects to enable flexible behavior, and many of these objects are supplied as shared object libraries (or dynamically-loadable libraries (DDL) on Windows). Examples of external objects include the PL/pgSQL server-side language, the pgcrypto cryptographic library, and the PostGIS geographic information system. These are all implemented as shared libraries that are dynamically loaded into the database server when accessed. The process of loading a library into a running executable is called dynamic loading and is the way most modern operating systems access libraries (the non-dynamic method is called “static linking”).

Continue Reading »

There are three ways to install Postgres, and they are all listed on the Download menu on the Postgres website. While the web authors did their best to explain the differences between the various installation methods, I thought it would be helpful to more fully explain their advantaged and disadvantages. The three methods are:

• Graphical Installers (also called “one-click” or “click-through” installers)
  • Advantages: Provide an easy-to-install Postgres environment with access to many add-on modules via Stack Builder. This is ideal for first-time Postgres users. It is also possible to use the installer in non-interactive mode.
  • Disadvantages: As mentioned on the Postgres web site, the one click installers do not integrate with platform-specific packaging systems.
• Platform-Specific Packages
  • Advantages: These are better integrated with other software installed on your operating system. This is ideal for production servers that rely on operating-system-supplied tools.
  • Disadvantages: Requires work to identify which other packages are needed for a complete solution. Obtaining newer major versions of Postgres on older operating systems might also require work.
• Source Code:
  • Advantages: Allows selection of specific configure and compile options for Postgres binaries, and allows the addition of patches to enhance or fix Postgres. This is ideal for experienced users who can benefit from additional control of Postgres.
  • Disadvantages: Requires compilation experience and managing Postgres tool integration, and requires user and server start/stop configuration.

Having attended several conferences recently, I saw confirmation of my previous observation that Postgres is poised for a new wave of adoption. The last time I saw such an upturn in adoption was with the release of Postgres 8.0 in 2005, which included a native port of Postgres to Windows. You can see the increase in the volume of postings to the Postgres jobs email list. (The spike in January of 2008 was Sun buying MySQL.)

And that’s not all — Robert Haas’s recent blog post about Postgres scaling linearly to 64-cores in upcoming Postgres 9.2 means that, by the end of the year, Postgres will be a major contender on high-end hardware. We have always done well on small to medium-sized servers, but we are now poised to compete heavily on the high-end.

Continue Reading »

Most software developers agree that software patents are hopelessly broad and that violation is almost unavoidable for any non-trivial software project. I won’t even go into examples of software patents causing industry chaos — new high-profile patent attacks appear every month. The risk of software patents to the software industry has been likened to a mine field, and for good reason — it is impossible to guess when software patents will create a crisis.

The Postgres team has been circumspect about software patents — we address them when someone brings up a possible violation (as happened in 2005 with a patent on queues), and we try to avoid patented ideas in our code, as you can see from our recent discussion about compression algorithms. Several years ago, one of the Postgres support companies analyzed our source code for possible patent violations, and reported we were clean, but given the “mine field” unpredictability of patents, it is hard to trust the report’s accuracy.

Continue Reading »

We just completed PGDay Austin, and it was a great event. It was a single-track conference in a serene hotel — this gave the event a polish and cohesion that I have rarely felt at Postgres events. Everyone knew where they should be, nothing felt rushed, and the talks were all of high quality. I wouldn’t be surprised to see double the attendance at next year’s conference.

The most exciting talk for me was Will Leinweber’s Schemaless SQL (down arrow key advances slides). The talk started by explaining why developers like the document storage model, and Will explained his experienced moving from document storage databases to Postgres. Will found our hstore extension to be ideal for storing the type of unstructured data he used to store in document databases. Hstore gave him the best of both relational
and document database worlds.

Continue Reading »

Documentation quality can make or break an open source project. Don’t believe me? Ever used poor documentation? How did it make you feel about your continued use of the software?

Just as Postgres’s high code quality doesn’t happen by accident, neither does its documentation quality. Our community is regularly updating and improving our documentation — since 1998, our documentation has averaged 1.3 commits per day. Don’t believe me? See for yourself — that’s 6913 documentation improvements over 14 years.

Continue Reading »

The Postgres documentation is extensive — no question about that. In fact it is so extensive (2704 US Letter pages in PDF for Postgres 9.1.3) that it has discouraged the publication of commercial Postgres books.

The Postgres documentation is available in two popular formats, HTML and PDF. The HTML format is most prominently displayed on our web site (9.1 version). Thom Brown masterfully improved the layout of our HTML documentation to be more esthetically pleasing about 18 months ago, and we made page navigation improvements about six months ago.

Continue Reading »

Why should you care about libpq? It sounds scary — not as scary as “PostgreSQL”, but still scary.  Libpq is the C interface library for Postgres. Your reaction, “But I don’t use C, I use Python, PHP, etc., so libpq isn’t useful to me?” Hold on! Don’t overlook libpq so quickly.

Libpq provides a C interface for clients communicating with Postgres servers over a network (and via Unix domain sockets). It provides authentication handling, query submission, and query result processing, as well as other utility operations.

Continue Reading »