PostgreSQL is an open source relational database management system that began as a University of California, Berkeley project. It was originally under the BSD license, but is now called the PostgreSQL License (TPL). For all intents and purposes, it’s BSD licensed. It has a long history, almost dating back to the beginning of relational databases.
It has enterprise class features such as SQL windowing functions, the ability to create aggregate functions and also utilize them in window constructs, common table and recursive common table expressions, and streaming replication. These features are rarely found in other open source database platforms, but commonly found in newer versions of the proprietary databases such as Oracle, SQL Server, and IBM DB2. What sets it apart from other databases, including the proprietary ones we just mentioned, is the ease with which you can extend it without changing the underlying base—and in many cases, without any code compilation. Not only does it have advanced features, but it performs them quickly. It can outperform many other databases, including proprietary ones for many types of database workloads.
In this book, we’ll expose you to the advanced ANSI-SQL features that PostgreSQL offers. and the unique features PostgreSQL has that you won’t find in other databases. If you’re an existing PostgreSQL user or have some familiarity with PostgreSQL, we hope to show you some gems you may have missed along the way; or features found in newer PostgreSQL versions that are not in the version you’re using. If you have used another relational database and are new to PostgreSQL, we’ll show you some parallels with how PostgreSQL handles tasks compared to other common databases, and demonstrate feats you can achieve with PostgreSQL that are difficult or impossible to do in other databases. If you’re completely new to databases, you’ll still learn a lot about what PostgreSQL has to offer and how to use it; however, we won’t try to teach you SQL or relational theory. You should read other books on these topics to take the greatest advantage of what this book has to offer.
This book focuses on PostgreSQL versions 9.0 to 9.2, but we will cover some unique and advanced features that are also present in prior versions of PostgreSQL.
PostgreSQL is special because it’s not just a database: it’s also an application platform—and an impressive one at that.
PostgreSQL allows you to write stored procedures and functions in several programming languages, and the architecture allows you the flexibility to support more languages. Example languages that you can write stored functions in are SQL (built-in), PL/pgSQL (built-in), PL/Perl, PL/Python, PL/Java, and PL/R, to name a few, most of which are packaged with many distributions. This support for a wide variety of languages allows you to solve problems best addressed with a domain or more procedural language; for example, using R statistics functions and R succinct domain idioms to solve statistics problems; calling a web service via Python; or writing map reduce constructs and then using these functions within an SQL statement.
You can even write aggregate functions in any of these languages that makes the combination more powerful than you can achieve in any one, straight language environment. In addition to these languages, you can write functions in C and make them callable, just like any other stored function. You can have functions written in several different languages participating in one query. You can even define aggregate functions with nothing but SQL. Unlike MySQL and SQL Server, no compilation is required to build an aggregate function in PostgreSQL. So, in short, you can use the right tool for the job even if each sub-part of a job requires a different tool; you can use plain SQL in areas where most other databases won’t let you. You can create fairly sophisticated functions without having to compile anything.
The custom type support of PostgreSQL is sophisticated and very easy to use, rivaling and often outperforming most other relational databases. The closest competitor in terms of custom type support is Oracle. You can define new data types in PostgreSQL that can then be used as a table column. Every data type has a companion array type so that you can store an array of a type in a data column or use it in an SQL statement. In addition to the ability of defining new types, you can also define operators, functions, and index bindings to work with these. Many third-party extensions for PostgreSQL take advantage of these fairly unique features to achieve performance speeds, provide domain specific constructs to allow shorter and more maintainable code, and accomplish tasks you can only fantasize about in other databases.
If building your own types and functions is not your thing, you have a wide variety of
extensions to choose from, many of which are packaged with PostgreSQL distros. PostgreSQL 9.1
introduced a new SQL construct, CREATE EXTENSION, which allows you to install the
many available extensions with a single SQL statement for each in a specific database. With
CREATE EXTENSION, you can install in your database any of the aforementioned PL
languages and popular types with their companion functions and operators, like
hstore,
ltree, postgis, and countless others. For example, to install the popular
PostgreSQL key-value store type and its companion functions and operators, you would type:
CREATE EXTENSION hstore;
In addition, there is an SQL command you can run—see Extensions and Contribs—to see the list of available and installed extensions.
Many of the extensions we mentioned, and perhaps even the languages we discussed, may seem like arbitrary terms to you. You may recognize them and think, “Meh, I’ve seen Python, and I’ve seen Perl... So what?” As we delve further, we hope you experience the same “WOW” moments we have come to appreciate with our many years of using PostgreSQL. Each update treats us to new features, eases usability, brings improvements in speed, and pushes the envelope of what is possible with a database. In the end, you will wonder why you ever used any other relational database, when PostgreSQL does everything you could hope for—and does it for free. No more reading the licensing cost fine print of those other databases to figure out how many dollars you need to spend if you have 8 cores on your server and you need X,Y, Z functionality, and how much it will cost you when you get 16 cores.
On top of this, PostgreSQL works fairly consistently across all supported platforms. So if you’re developing an app you need to resell to customers who are running Linux, Mac OS X, or Windows, you have no need to worry, because it will work on all of them. There are binaries available for all if you’re not in the mood to compile your own.
PostgreSQL was designed from the ground up to be a server-side database. Many people do use it on the desktop similarly to how they use SQL Server Express or Oracle Express, but just like those it cares about security management and doesn’t leave this up to the application connecting to it. As such, it’s not ideal as an embeddable database, like SQLite or Firebird.
Sadly, many shared-hosts don’t have it pre-installed, or have a fairly antiquated version of it. So, if you’re using shared-hosting, you’re probably better off with MySQL. This may change in the future. Keep in mind that virtual, dedicated hosting and cloud server hosting is reasonably affordable and getting more competitively priced as more ISPs are beginning to provide them. The cost is not that much more expensive than shared hosting, and you can install any software you want on them. Because of these options, these are more suitable for PostgreSQL.
PostgreSQL does a lot and a lot can be daunting. It’s not a dumb data store; it’s a smart elephant. If all you need is a key value store or you expect your database to just sit there and hold stuff, it’s probably overkill for your needs.
This book is geared at demonstrating the unique features of PostgreSQL that make it stand apart from other databases, as well as how to use these features to solve real world problems. You’ll learn how to do things you never knew were possible with a database. Aside from the cool “Eureka!” stuff, we will also demonstrate bread-and-butter tasks, such as how to manage your database, how to set up security, troubleshoot performance, improve performance, and how to connect to it with various desktop, command-line, and development tools.
PostgreSQL has a rich set of online documentation for each version. We won’t endeavor to repeat this information, but encourage you to explore what is available. There are over 2,250 pages in the manuals available in both HTML and PDF formats. In addition, fairly recent versions of these online manuals are available for hard-copy purchase if you prefer paper form. Since the manual is so large and rich in content, it’s usually split into a 3-4 volume book set when packaged in hard-copy form.
Below is a list of other PostgreSQL resources:
Planet PostgreSQL is a blog aggregator of PostgreSQL bloggers. You’ll find PostgreSQL core developers and general users show-casing new features all the time and demonstrating how to use existing ones.
PostgreSQL Wiki provides lots of tips and tricks for managing various facets of the database and migrating from other databases.
PostgreSQL Books is a list of books that have been written about PostgreSQL.
PostGIS in Action Book is the website for the book we wrote on PostGIS, the spatial extender for PostgreSQL.
The following typographical conventions are used in this book:
- Italic
Indicates new terms, URLs, email addresses, filenames, and file extensions.
Constant widthUsed for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.
Constant width boldShows commands or other text that should be typed literally by the user.
Constant width italicShows text that should be replaced with user-supplied values or by values determined by context.
Tip
This icon signifies a tip, suggestion, or general note.
Caution
This icon indicates a warning or caution.
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “PostgreSQL: Up and Running by Regina Obe and Leo Hsu (O’Reilly). Copyright 2012 Regina Obe and Leo Hsu, 978-1-449-32633-3.”
If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [email protected].
Note
Safari Books Online (www.safaribooksonline.com) is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.
Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.
Safari Books Online offers a range of product mixes and pricing programs for organizations, government agencies, and individuals. Subscribers have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and dozens more. For more information about Safari Books Online, please visit us online.
Please address comments and questions concerning this book to the publisher:
| O’Reilly Media, Inc. |
| 1005 Gravenstein Highway North |
| Sebastopol, CA 95472 |
| 800-998-9938 (in the United States or Canada) |
| 707-829-0515 (international or local) |
| 707-829-0104 (fax) |
We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:
| http://bit.ly/PostgreSQL_UR |
To comment or ask technical questions about this book, send email to:
| [email protected] |
For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.
Find us on Facebook: http://facebook.com/oreilly
Follow us on Twitter: http://twitter.com/oreillymedia
Watch us on YouTube: http://www.youtube.com/oreillymedia