A Reply to Tony Marston's advocacy of unintelligent databases

Tony Marston has published an interesting critique of my posting about why intelligent database are helpful.  The response is thought-provoking and I suggest my readers read it.  Nonetheless I believe that the use cases where he is correct are becoming more narrow over time, and so I will explain my thoughts here.

A couple preliminary points must be made though.  There is a case to be made for intelligent databases, for NoSQL, and for all sorts of approaches.  These are not as close to a magic bullet as some proponents would like to think and there will be cases where each approach wins out, because design is the art of endlessly making tradeoffs.  My approach has nothing to do with what is proper and has to do instead with preserving the ability to use the data in a other ways for other applications, as I see this as the core strength of relational database management systems.  Moreover I used to agree with Tony but I have since changed my mind primarily because I have begun working with data environments where the stack assumption for application design doesn't work well.  ERP is a great example and I will go into why below.

Of course in some cases data doesn't need to be reused, and an RDBMS may still be useful.  This is usually the case where ad-hoc reporting is a larger requirement than scalability and rapid development.  In other cases where data reuse is not an issue an RDBMS really brings nothing to the table and NoSQL solutions of various sorts may be better.

My own belief is that a large number of database systems out there operate according to the model of one database to many applications.  I also think that the more that this model is understood the more that even single-application-database designers can design with this in mind or at least ask if this is the direction they want to go.

And so with the above in mind, on to the responses to specific points:

Well, isn't that what it is supposed to be? The database has always been a dumb data store, with all the business logic held separately within the application. This is an old idea which is now reinforced by the single responsibility principle. It is the application code which is responsible for the application logic while the database is responsible for the storing and retrieval of data. Modern databases also have the ability to enforce data integrity, manage concurrency control, backup, recovery and replication while also controlling data access and maintaining database security. Just because it is possible for it to do other things as well is no reason to say that it should do other things.

The above opinion works quite well in a narrow use case, namely where one application and only one application uses the database.  In this case, the database tables can be modelled more or less directly after the application's object model and then the only thing the RDBMS brings to the table is some level of ad hoc reporting, at the cost of added development time and complexity compared to some NoSQL solutions.  (NoSQL solutions are inherently single application databases and therefore are not usable where the considerations below exist.)

The problem is that when you start moving from single applications into enterprise systems, the calculation becomes very different.  It is not uncommon to have several applications sharing a single database, and the databases often must be designed to make it quick and easy to add new applications on top of the same database.

However as soon as you make this step something awful happens when you try to use the same approach used for single application databases:  because the database is based on the first application's object model, all subsequent applications must have intimate knowledge of the first application's object model, which is something of an anti-pattern.

A second major problem also appears at the same time, and that is that while a single application can probably be trusted to enforce meaningful data constraints, applications can't and shouldn't trust eachother to input meaningful data.  The chance of oversights where many applications are each responsible for checking all inputs for sanity and ensuring that only meaningful data is stored is very high, and the consequences can be quite severe.  Therefore things like check constraints become suddenly irreplaceable when one makes the jump from one data entry application to two against the same database.

Just because a database has more features does not mean that you should bend over backwards to use them. The English language contains a huge number of words that I have never used, but does that make my communication with others unintelligible? I have used many programming languages, but I have rarely used every function that has ever been listed in the manual, but does that mean that my programs don't work? With a relational database it is possible to use views, stored procedures, triggers or other advanced functionality, but their use is not obligatory. If I find it easier to do something in my application code, then that is where I will do it.

Indeed.  However, if you aren't thinking in terms of what the RDBMS can do, but rather just in terms of it as a dumb storage layer, you will miss out on these features when they are of benefit.  Therefore it is important to be aware of the costs of this when asking "should my database be usable by more than one application?"

That question becomes surprisingly useful to ask when asking questions like "should data be able to be fed into the database by third party tools?"

Where did this idea come from? The application has to access the database at some point in time anyway, so why do people like you keep insisting that it should be done indirectly through as many intermediate layers as possible? I have been writing database applications for several decades, and the idea that when you want to read from or write to the database you should go indirectly through an intermediate component instead of directly with an SQL query just strikes me as sheer lunacy. This is a continuation of the old idea that programmers must be shielded completely from the workings of the database, mainly because SQL is not objected oriented and therefore too complicated for their closed minds. If modern programmers who write for the web are supposed to know HTML, CSS and Javascript as well as their server-side language, then why is it unreasonable for a programmer who uses a database to know the standard language for getting data in and out of a database?

The idea comes from the necessities of  ensuring that the database is useful from more than one application.  Once that happens, then the issue is one of details of too much intimate knowledge of data structures between components.

However one thing I am not saying is that SQL-type interfaces are categorically out.  It would be a perfectly reasonable approach to encapsulation to build updatable views matching the object models of each application, and indeed reporting frameworks perhaps should be built this way to the extent possible, at least where multiple applications share a database.  The problem this solves is accommodating changes to the schema required by one application but not required by a second application.  One could even use an ORM at that point.

If your programmers have to spend large amounts of time in writing boilerplate code for basic read/write/update/delete operations for your database access then you are clearly not using the right tools. A proper RAD (Rapid Application Development) toolkit should take care of the basics for you, and should even ease the pain of dealing with changes to the database structure. Using an advanced toolkit it should possible to create working read/write/update/delete transactions for a database table without having to write a single line of SQL. I have done this with my RADICORE toolkit, so why can't you? Simple JOINs can be handled automatically as well, but more complex queries need to be specified within the table class itself. I have implemented different classes for different database servers, so customers of my software can choose between MySQL, PostgreSQL, Oracle and SQL Server.

How does this work though when you have multiple applications piping heavy read/write workloads through the same database, but where each application has unique data structure requirements and hence its own object model?

The very idea that your software data structure should be used to generate your database schema clearly shows that you consider your software structure, as produced from your implementation of Object Oriented Design (OOD) to be far superior to that of the database.....

It seems I was misunderstood.  I was saying that the generation of database structures from Rails is a bad thing.  I am in complete agreement with Tony on that part I think.

You may think that it slows down development, but I do not. You still have to analyse an application's data requirements before you can go through the design process, but instead of going through Object Oriented design as well as database design I prefer to ignore OOD completely and spend my time in getting the database right. Once this has been done I can use my RAD toolkit to generate all my software classes, so effectively I have killed two birds with one stone. It also means that I don't have to waste my time with mock objects while I'm waiting for the database to be built as it is already there, so I can access the real thing instead of an approximation. As for using an ORM, I never have the problem that they were designed to solve, so I have no need of their solution.

This is an interesting perspective.   I was comparing it though to initial stages of design and development in agile methodologies.

Here's the basic thing.  Agile developers like to start out when virtually nothing is known about requirements with a prototype designed to flesh out the requirements and refine that prototype until you get something that arguably works.

I am saying that in the beginning at least, asking the question of what the data relating to topics being entered is and how to model it neutral of business rules has a cost in terms of developer time spent in the beginning.  Sometimes, if a requirement to collect, say, addresses is dropped, that time will be entirely lost, so it is not really possible to say that such engineering effort is always a clear win.

I would agree though that the time spent there on looking at the data, modelling issues, and normalization is usually recouped later, so I suppose we are in agreement.

This is another area where OO people, in my humble opinion, make a fundamental mistake. If you build your classes around the database structure and have one class per table, and you understand how a database works, you should realise that there are only four basic operations that can be performed on a database table - Create, Read, Update and Delete (which is where the CRUD acronym comes from). In my framework this means that every class has an insertRecord(), getData(), updateRecord() and deleteRecord() method by default, which in turn means that I do not have to waste my time inventing unique method names which are tied to a particular class. Where others have a createCustomer(), createProduct(), createOrder() and createWhatever() method I have the ubiquitous insertRecord() method which can be applied to any object within my application. This makes use of the OO concept of polymorphism, so it should be familiar to every OO programmer. Because of this I have a single page controller for each of the methods, or combination of methods, which can be performed on an object, and I can reuse the same page controller on any object within my application. I have yet to see the same level of reuse in other application frameworks, but isn't a high level of code reuse what OOP is supposed to be about?
I am often told "but that is not the way it is done!" which is another way of saying "it is not the way I was taught". This tells me that either the critic's education was deficient, or he has a closed mind which is not open to other techniques, other methods or other possibilities.

The problem occurs when CRUD operations are not really as simple as this sounds, and where they must have complex constraints enforced from multiple data entry applications.  A good example here is posting GL transactions, where each transaction must be balanced.  This is far simpler to do in a stored procedure than anything else, because the set has certain emergent constraints that apply beyond the scope of a single record.  Also if abstracting the application interface from the low-level storage, then this may be necessary as part of the mapping of views to relations.

I can even make changes to a table's structure, such as adding or deleting a column, or changing a column's size or type, without having to perform major surgery on my table class. I simply change the database, import the new structure into my data dictionary, and then export the changes to my application. I don't have to change any code unless the structural changes affect any business rules. My ERP application started off with 20 database tables but this has grown to over 200 over the years, so I am speaking from direct experience.

So if you are doing an ERP application and at the same time putting all business logic in your application, you do not expect any third party applications to hit your  database, right?  So really you get the same basic considerations I am arguing for by making the database into the abstraction layer rather than encapsulating the db inside an abstraction layer.

If we think about it this way, it depends on what the platform is that you are developing on.  We like to have the database be that platform, and that means treating interfaces to it as an API.  Evidently you prefer to force all access to go through your application.  I think we get a number of benefits from this approach including language neutrality for third party components, better attention to performance on complex write operations, and more.  That there is a cost cannot be argued with however.

I would conclude by saying that we agree on a fair bit of principles of database design.  We agree normalization is important, and I would add that this leads to application-neutral data storage.  Where we disagree is where the API/third party tool boundary should be.  I would prefer to put in the database whatever seems to belong to the general task of data integrity, storage, retrieval, and structural presentation while leaving what we do with this data to the multiple third party applications which utilize the same database.

The Selection and Design of Templating in LedgerSMB 1.3

One of the key features of LedgerSMB 1.3 has been the move from the custom templating solution of LedgerSMB 1.2 (inherited from SQL-Ledger) to Template Toolkit.  This gives us more flexibility, better cross-language support since TT has been implemented for other languages as well, and the like.  However in practice in order to preserve these and avoid too close of a merging between business and presentation logic, we avoid some functions of TT, such as embedded Perl.  We also make have some specific approaches to how we invoke TT in order to avoid problems and help ensure standards compliance to the best of our ability.

The big problem that one runs into is the use of percents, which denote comments in LaTeX.  Leaving the percent signs in leads to parsing problems when testing templates.  So it became clear that since we wanted to re-use the same template logic for both UI and printed invoices the percent signs would have to go.

We settled on opening the tag with <?lsmb and closing it with ?>  The reason here is that this re-uses the SGML-standard "program instruction" tag syntax to ensure that validation can be run on the templates themselves.

A second thing we did was to add some template-specific functions.  These handle internationalization and number formatting.  Each of these has two forms:  a general form specific to the language or preferences of the template and a non-specific form, where the localization settings must be specified in the template.  This provides a consistent templating API for presentation of data, without mixing object behavior with the template layer itself.

The reason for these two forms is that there may be cases where number formatting must be specified in the template (such as when a check is printed), but there are others where user-specific settings should be used.  Similarly we want to specify a primary language with a template, but might want to translate strings into a second language (for example for bilingual receipts).

This gives us a general presentation API largely based on TT, but with differences which enable us to do whatever we need to do.  We had to rip out the old pagebreak handling and go instead to LaTeX longtables instead, which is probably a win anyway.

However one surprising use has come up, and that has been the ability to re-use the templating system in preparing database upgrade scripts which are then passed along to psql.

MYGOSSCON Observations, and Notes on Open Source in South-East Asia

This week, I attended the Malaysian Government Open Source Software Convention (MYGOSSCON), promoting PostgreSQL and LedgerSMB to both public and private sector entities.  We had a simple booth and had approx 100 people stop by over the course of the two days.  We also talked with a wide range of other exhibitors and came away with a sense that we can expect great things in South-East Asia regarding Open Source.

It was immediately clear that PostgreSQL was a star technology at the conference, with a strong showing by EnterpriseDB and EnterpriseDB resellers.  We were the only project specializing in the "community edition" as EnterpriseDB calls it.  We had a lot of people stop by to talk about PostgreSQL and share success stories, answering questions about the differences between Oracle and PostgreSQL, between PostgreSQL and MySQL, and between EnterpriseDB and the official release version of PostgreSQL (I refuse to call it the "community version").  I was told that there would be few who knew databases, but while there were only a few database experts at the conference (which was to be expected) there were a lot of people who had a solid working knowledge of database systems generally and had lots of well-thought-out and valid questions.

Indeed people would see the other signs at our booth and keep walking... until they saw the big yellow banner with the blue, white, and black PostgreSQL logo on it and then walk right over.  We got questions that I knew the answer to, questions where my answers were slightly out of date (areas of Pg vs Oracle), and questions which I had not really prepared for at all (asking about Arabic support for PostgreSQL).  Unfortunately with the internet connections overloaded at the conference, I had to do the best I could, and promise follow-up as necessary.

One typically goes to trade shows expecting to build awareness for one's products and services with the idea that this will grow the market later.  In this case, however, we walked away with companies interested in hosting LedgerSMB, possible maintainers for other language translations, and a couple of actual, actionable sales leads.  We were extremely happy with the way things went.

However in addition to talking with civil servants and educators, an important undercurrent emerged in my discussions with purely private sector individuals (often other exhibitors):  open source is rapidly growing here in South-East Asia, particularly in the public sector.  The major limiting factor appears to be supply if individuals with relevant expertise and experience.  This is not to say that such people aren't available, but rather that demand significantly exceeds supply which is common in growing markets.  This isn't just the sorts of things we experienced first hand, caused by entities trying to reduce the amount that they spend primarily to Oracle, but instead part of a much larger trend, with governments in the region.

As I typically try to point out when advocating open source, the decision to prioritize open source isn't just about money, but rather the differences in dynamics surrounding money lead to a number of important benefits including a larger percentage of the money to launch a project going into the local economy, and the added flexibility that occurs when one does not need to worry about acquisition of software licenses in order to put data to a new use.  This leads to local power both economically and in terms of decision-making.  According to other consultants, this is not limited to the Malaysian government and other governments in the region are increasing their commitment to open source software.

All around South-East Asia, my sense is that there is a sense that open source will be a major economic asset to the region and something to be cultivated.  It is a good time to be in open source over here, but there has never been a better time (and probably never will be a better time) to be in open source databases.  PostgreSQL is, in particular, poised to start penetrating Oracle's home market, with projects like Gridsql and Postgres-xc reaching maturity.  I believe we are to the point where no corner of the market is safe any more, and where the adoption of an open source lifestyle for businesses and governments will become the norm rather than the exception.

I think that last sentence follows quite directly from open source database systems coming of age.  The large, multi-application database systems (DB2, Oracle, MS SQL, Terradata) represent the last major piece of IT infrastructure for which open source solutions were inadequate.  I say multi-application to differentiate from the "app-stack" approaches familiar to LAMP developers.  Typically here we are talking about heavily engineered databases designed to have a significant number of applications solving different issues, manipulating their data.  However, as this piece of infrastructure begins to fall to open source, then all the back-end software can be open source or in-house.  At this point, one has to ask whether one should simply bite the bullet and start migrating the front-end (desktop/laptop) devices as well.  Pushing Linux-based desktops in government and business would almost certainly have a cascading effect to consumer systems as well.

These are exciting times indeed.

LedgerSMB (and PostgreSQL) at MYGOSSCON

Metatron Technology Consulting has rented a booth at the Malaysia Government Open Source Software Convention (MYGOSSCON), 2011.  This booth will be used to advocate PostgreSQL and LedgerSMB both to private and public sectors.

Anyone going to this convention should look for us!

The Case for Intelligent Databases

LedgerSMB, as an ERP and accounting system, has over time begun to move towards an intelligent database design based on PostgreSQL.  We believe that this best matches our long-term goals for the project, especially because interoperability is a very important requirement for any ERP system.  Read more to see our general case in this area and see what other projects may be able to learn from our experience.

A Short Developer's Definition of Intelligent Databases

An intelligent database is a database encapsulated behind a defined API.

A Longer Description

While most programmers primarily use the database  primarily as a place to simply store data, this reduces a database to basically an ACID-compliant data store with some reporting capability.  This approach saves the programmer from worrying about advanced database functionality, but it also prevents the database system from being used to do a number of useful tasks that databases can really be leveraged to do.

This post will explore why LedgerSMB is going this direction, and the sorts of functionality (particularly regarding PostgreSQL) that is a part of this plan, what sorts of tradeoffs we see in it, and where we expect to benefit.  This is not an article that discusses the details of implementation (see previous posts for that), but rather at a high-level how and why.


The Toolbox

The fundamental idea of encapsulating a database inside a defined API is that usually the application should hit the API and not the underlying relations where the data is stored if at all possible.,  The  API acts as an interface between actual storage/retrieval and the application.

The major aspects of an API can include views and/or stored procedures.  We use stored procedures for most things but may include views for some sorts of objects as time goes on.

What do Intelligent Databases Cost?

Many current development environments seek to save the programmer from spending heavy engineering time on a database, particularly in the early stages of a project where requirements can change.  The database is relegated to a simple data store with ACID compliance.  Frameworks like Rails will even generate a database schema from programming data structures.

If one goes with an intelligent design for the database, one cannot use agile programming methodologies to design the database.  Instead the database requires some real engineering in its own right. This takes some time and effort in its own right and can slow down development compared to ORM-based code generators such as we see in Rails.

Such engineering requires taking a clear look at what data is expected to be collected, what internal structure it has, designing the database storage first and foremost around that analysis,and then building an API based on how it will be used.  For simple schema development this doesn't take a long time but the complexity does grow with the data collected.

In the LedgerSMB project this is not the end of the world since we are re-engineering the database incrementally and so each major branch includes at least a few areas where old db structures have been pulled out and new, better engineered ones added.  These areas then have an API and the application generally hits the API not the relations.  Old code areas may hit the API or the relations.

Other projects may find that this adds significant development time beyond what is reasonable, but these must still be weighed against the benefits below.


What benefits do Intelligent Databases Provide?

Intelligent databases offer a number of specific benefits that LedgerSMB tries to capitalize on.  The fact that these designs better match database theory is not enough.  tangible benefits were required for us to reach this decision.

Some of these benefits speed up time troubleshooting and debugging code, partly offsetting the additional investment mentioned above.  Others provide compelling features to third party developers who may want to integrate our software with theirs.

The single most important benefit is that of clarity.  First, with very few exceptions, LedgerSMB tries to keep to one language per file.  This means that database queries are in .sql files, and Perl is in .pl and .pm files.  The modules which do query mapping are the only Perl modules that contain both Perl and SQL.  In this way if I want to know what a query does, I can go to the relevant SQL file and read it.  Using features like named inputs in PL/PGSQL, even 100 line queries can be remarkably readable.  The API is also well defined, allowing us to troubleshoot issues with the query writer.

Clarity also means that performance problems can more easily be reviewed and addressed without getting into messy questions of ORMs and how they generate queries.  The queries that produce a specified result set from an API call can be defined and the actual function written out in SQL.

Along with clarity, we get the ability to do as much as possible with declarative constraints.  This provides, we believe, greater robustness in the application and therefore better software in the long run.

The second important benefit is in the area of integration.  Because the API is defined, there are specified integration points that other applications can use to integrate without worrying as much about ensuring that corner cases are properly covered.  This makes integration more robust.  Moreover important areas like LISTEN and NOTIFY allow other applications to integrate in ways approximating message queues, and the like.

In fact, the integration benefit was the driving force behind the decision to move to an intelligent database design.  We wanted to open up the application to add-ons written in other languages (Python at the time, but others could supported without too much effort).

Addressing Separation of Concerns

We have all heard horror stories of what happens when you put everything in the database.  We have also heard of developers afraid to put anything in the database because of fear of going down the roads of these other horror stories.  The secret to success here is in fact to separate concerns and to separate them well.  Many lines are ultimately arbitrary in detail but bright in outlines and so they require a team dedicated to making decisions carefully rather than drawing bold lines and mindlessly following them.

Of course that's a long way of saying you can't get away with sloppy engineering and have to actually put some thought into things.


Other Options

There are other options for application development. However, they all presume that the application has intimate knowledge of the internals of the database.  I am somewhat puzzled why programmers who are so careful to avoid modules understanding the internals of eachother are so careless about encapsulation in the database.

The main other options are use of an ORM, which typically involves designing the database around the ORM and the data model for the application, and thus the RDBMS becomes basically an extension of the object model.  This limits interoperability, and reduces the role that the database has relative to interoperability with other applications.  This being said there isn't necessarily a problem with using VIEWs and ORMs together to provide this sort of abstraction.

A second possibility is to use a dedicated store, like many NoSQL engines offer where the database really is a store for objects, and where the "schema" (in scare quotes because NoSQL usually doesn't really have a schema concept) is just a direct extension of the object model itself.

The tradeoff with NoSQL is similar to using an ORM and building your database around that ORM, but more extreme.  Typically you see even more rapid development early on, combined possibly with additional scalability in some cases, but with the tradeoff of interoperability is more severe, and ad hoc reporting is generally not practical outside the relational model.

Because of our desire to focus on interoperability and a reliance on good, flexible reporting, these options were not options for our project.  They might be for others though.

Conclusions

LedgerSMB has benefited greatly benefited from the decision to move towards an intelligent database layout.  Reasons include our focus on interoperability, and our willingness to do engineering up front.  Other projects may find their mileage varying.

Elegance and Simplicity

When it comes to software engineering, I always think back to my first year Calculus teacher, Mike Lavender, and his admonishment that "in mathematics, power tools are consider inelegant where hand tools will do." Words to live by in programming and engineering.


So I'd go so far as to redefine elegance in software programming and engineering as "simplicity perfected." The simple fact is that where requirements change, a well engineered simple solution will usually turn out to be more agile than a heavily engineered, complex one (whether programmed with "agile" methodologies or not).

When one takes the idea of trying to perfect simplicity in the art of programming (and software engineering), it becomes easier to recognize foot-guns and inherent problems, and tradeoffs often become more conscious and controlled.

10 Underrated Features of PostgreSQL

Here are a list of features which I think are generally underrated in PostgreSQL and why.  Many though not all of these are being used in the development version of LedgerSMB, and more of these will likely be used as time goes on either by the main software or by addons.

These are functions which I greatly miss when I set down and work with many other RDBMS's, or which are fairly new and often underutilized.

10:  Language-Specific Full Text Searches
What it is:  Language-specific searches on related words.

Why I like it:  This function allows for a full text search to query related words in a post.  These can be indexed using functional indexes below as long as the system knows which language to search under.  So imagine if you put a text field that says "I am going to test this for the customer next week" but when you go back and search you search for "testing for customer" the result will still show up, because PostgreSQL knows that in English, "test" and "testing" can be two forms of the same word.

How I'd use it:  Any application which requires full text searches can take advantage of this to provide a much richer experience.

The LedgerSMB budget module currently uses full text searches for the descriptions of budgets.  Currently this is locale specific.  Future versions will probably allow notes to be in various languages and searched via a full text search per language.

9:   Pgcrypto
What it is:  Cryptographic extensions to PostgreSQL

Why I like it:   With pgcrypto, one can design systems which provide strong protection of data in the database, making the encryption transparent to the application.

How I'd use it:  If someone wanted to store, say, credit card data in the database, this could be installed using suitable forms of managed keys such that the user could unlock a key and retrieve the data.  This could then be handled such that the application itself would not need to handle encryption or decryption of the data, thus allowing other applications to handle key management and other important tasks.



8:    Functional Indexes


What it is:  Indexes on the outputs of immutable functions

Why I like it:  Functional indexes allow us to store information functionally dependent on information actually stored in the database.  Consequently just because we need to search on information.  This is available in Oracle and to a far lesser extent in MS SQL and DB2.  It is not available in MySQL.

How I'd use it:  Functional indexes are behind language-dependent full-text indexing (and hence can make searches there a lot faster).  It also has a large number of other uses.

For example, suppose I am storing a large bytea column and I want to search quickly on results.  I could index md5(my_bytea) and then search on input = my_bytea and md5(input) = md5(my_bytea).  This would allow an index scan to pull out matching md5's and then checking the full result against matching records.  For many other databases, one would have to store both the original data and the md5 checksum.

Another use would be to ensure uniqueness based on function output.  So for example, we could:

CREATE UNIQUE INDEX u_functional_index ON my_table (lower(my_col));

Then values would be rejected if they are non-unique to a case insensitive search.

7:    Partial Indexes


What it is:  Indexes on a subset of rows.

Why I like it:  Partial indexes allow the development of relatively complex data constraints.  For example, one can require a unique constraint on a subset of rows matching a certain criteria, since uniqueness is implemented on the index.  Of the other major RDBMS's, only Microsoft SQL Server implements anything like this.

How I'd use it:  Partial indexes can be used in two fundamentally different ways:  speeding up common search criteria and enforcing partial unique constraints.  Many are familiar with speeding up searches in this way, but partial unique indexes are where the real power lies. 

So suppose ar and ap invoices share the same physical table, but ar invoices must have unique invoice numbers while ap invoices must be unique per vendor.  We can use partial unique indexes to support both these constraints independently.

CREATE UNIQUE INDEX u_ar_invnumber ON invoices (invnumber) WHERE type = 'ar';

A second important application is adding forward-only unique constraints.  These are helpful where a new unique constraint needs to be added immediately but not all data in the table conforms to that constraint.  As a stop-gap measure (while data is being cleaned up), one might use a partial unique constraint to encourage uniqueness going forward.  Something like:

CREATE UNIQUE INDEX u_ar_invnumber ON invoices (invnumber) WHERE type = 'ar' AND id > 12345;

Data can be cleaned up (if possible), then the index can be dropped and recreated with a different where clause.

6:    Third Party Solutions


What it is:           An extensive set of third party add-ons for PostgreSQL

Why I like it:        There's so much to choose from!

A few of my favorites:   PGTAP is a test harness for stored procedures, building on other test harness frameworks.  It allows you to write production-safe test cases and run them without risking your data.  Very very good.

Slony-I is a very good well-developed single-master, asynchronous replication system for PostgreSQL.  it was developed to help ensure the .org domain registrar would scale appropriately.  Slony-I supports, among other things, replicating between different versions of PostgreSQL, something the native solutions do not.

Speaking of replication, Bucardo allows you to do multi-master and master-slave asynchronous replication as well.

PostGIS provides an excellent basis for GIS applications, and there are many, many other third party, free software add-ons for PostgreSQL  Uses are largely limited by the imagination.

5:    Language Handles


What it is:  Define your own stored procedure languages!

Why I like it:  Makes migration often more feasible than it would be otherwise, and exposes much advanced functionality to stored procedure interfaces.

How I'd use it:  There are three major uses of stored procedure handles (i.e. to create stored procedures in languages other than C, SQL, and PL/PGSQL).

The first of these is migration.  If one has an Oracle database which uses a lot of Java stored procedures you can often port these to PostgreSQL and PL/J with a minimum of headaches.  Similarly if you have an MS SQL database with .Net stored procedures you may be able to make these work with PL/Mono.

The second is to make advanced features of a language available to your stored procedures.  If you are doing a lot of text processing, PL/Perl may be helpful, but if you are doing a lot of statistical calculations, PL/R may be very helpful.

Finally (and here is the dangerous one), language handles can make it possible to do very un-database-like things from within stored procedures.  So for example, you could use PL/SH to execute shell scripts from the stored procedures.  However in general for these areas, external scripts and LISTEN/NOTIFY (see below) are better tools.

4:    Complex Types


What it is:        Tuple-based types, often used as function output types.

Why I like it:     These provide an opportunity to create a very rich API within the db.  PostgreSQL supports not only pure tuples but tuples which contain arrays of other tuples as their members.

How I'd use it:   In LegerSMB, we use complex types to define much of our interface for the application and this dependency will increase over time.  These types allow one to represent in SQL form any well-structured representation of data in any other application.

These can then be used to define inputs and outputs of stored procedures.

3:    Listen/Notify


What it is:  Notification of specified database events.

Why I like it:  this provides a notification service within the database.  Applications listen for a specified notification handle, and other functions can raise those notifications.  This allows one to create message queues (similar to Oracle's Database Notification Services), but that's not all.

Because the notifications are decoupled from low-level database operations, one can raise the notifications based on arbitrary criteria.  Suppose we want to raise a notification anytime the postgres user deletes data from the audit trail?  We can do that. No problem.  Similarly we could raise a notification anytime

How I'd use it:  You can use LISTEN/NOTIFY to send a message to a script that information has been dropped in a table and is ready to email out to a decision-maker.  For example you could use this to provide real-time emailing when, for example, a part's on-hand value drops below the reorder point.  In this way all manner of message queues can be created using the database as a center.

On top of that one can send notifications out around things that do not modify the database. I don't have a specific use case for this yet, but the fact that it is there means that many other things suddenly become possible.

2:    Windowing Functions


What it is:  The ability to have an aggregate function that aggregates incrementally over a set or portion of a set.  Although most RDBMS's these days do support windowing functions (MySQL, DB2, Oracle, and MS SQL all do), this is a feature which is often overlooked.

Why I like it:  This allows you to specify aggregates which are run incrementally across a set.  This set can be partitioned and ordered independent of any ordering on the final set.

How I'd use it:  There are huge numbers of uses for this sort of thing.  For example, windowing functions make it possible to select the highest ranked rows within a set by certain criteria.  They also allow one to add running totals to financial reports, or add running totals within specific transactions for financial reports.

No doubt there are many many more uses I just haven't thought of or come across.  However if you think about being able to use windowing functions the sorts of queries you can write are well expanded.

1:     PL/PGSQL


What it is:   A very clear, concise, well organized language for stored procedures.  Borrowing heavily in approach from Oracle's PL/SQL, this language is a mixture of Ada, PL/I, and SQL.  The result, while different from what most programmers are used to, highly readable, easy to follow, and extremely powerful.

Why I like it: PL/PGSQL adds almost everything you could want that vanilla SQL does not offer in a procedural language.  Conditionals, loops, and named variables all come to mind.  But what it has going for it is conciseness.  Often times I hear people say they are "just" doing stored procedures in PL/PGSQL.  There is no "just" about it though.  This language is very well designed for what it does.

How I'd use it:  LedgerSMB currently contains most SQL in the newer code inside stored procedures written in PL/PGSQL.  We use it for most procedures with large numbers of arguments or where the control structures are at all helpful.  In essence we use it a LOT.

The above list is largely arbitrary.  Another author might put in the fact that data definition language calls are usually transaction-safe (meaning you can roll back a transaction that dropped a table), incremental backups, or any number of other features.  However the features that I have included above are generally those which allow PostgreSQL to be used as a very extensible database server.  It is my view that these features, which are at their best when creating intelligent databases, are frequently under-used especially by small developers because they think of PostgreSQL as merely a data store instead of the very powerful system that it is.

What features do you think are underrated?  Comment below to mention them!