PostGreSQL

PostgreSQL is a relational database management system, which means it’s a set-theory-based system, implemented as two-dimensional tables with data rows and strictly enforced column types. Despite the growing interest in newer database trends, the relational style remains the most popular and probably will for quite some time.

  • The prevalence of relational databases comes from their vast toolkits

    • triggers

    • stored procedures

    • advanced indexes

    • their data safety (via ACID compliance),

    • or their mind share (many programmers speak and think rela- tionally)

    • query flexibility

Unlike some other type of datastores, you needn’t know how you plan to use the data

  • PostgreSQL is by far the oldest and most battle-tested database in this book. It has

    • plugins for natural-language parsing

    • multi-dimensional indexing

    • geographic queries

    • custom datatypes

    • sophisticated transaction handling

    • built-in stored procedures for a dozen languages

    • and runs on a variety of platforms

    • built-in Unicode support,

    • sequences

    • table inheritance

    • subselects

    • and it is one of the most ANSI SQL–compliant relational databases on the market

PostgreSQL, being of the relational style, is a design-first datastore.
First you design the schema, and then you enter data that conforms to the 
definition of that schema.

Why are relational databases called relational ?

Relational databases are relational based on mathematics. They aren’t relational because tables “relate” to each other via foreign keys. Well this link explains it very well

What's unique in relational databases ?

All of the other databases we’ll read about in this book perform CRUD opera- tions as well. What sets relational databases like PostgreSQL apart is their ability to join tables together when reading them. Joining, in essence, is an operation taking two separate tables and combining them in some way to return a single table.

Transactions

PostgreSQL transactions follow ACID compliance, which stands for

  • Atomic (all ops succeed or none do)

  • Consistent (the data will always be in a good state—no inconsistent states)

  • Isolated (transactions don’t interfere)

  • Durable (a committed transaction is safe, even after a server crash)

We should note that consistency in ACID is different from consistency in CAP theorem

Stored Procedures

Every command we’ve seen until now has been declarative, but sometimes we need to run some code. At this point, you must make a decision:

  • execute code on the client side or

  • execute code on the database side.

Stored procedures can offer huge performance advantages for huge architectural costs. You may avoid streaming thousands of rows to a client application, but you have also bound your application code to this database. It will cause problems when you will wish to migrate to some other database . The decision to use stored procedures should not be arrived at lightly.

Triggers

Triggers automatically fire stored procedures when some event happens, like an insert or update. They allow the database to enforce some required behavior in response to changing data. For example running a function that logs whenever a row is updated

Views

Using views, we can use the results of a complex query just like any other table. Unlike stored procedures, these aren’t functions being executed but rather aliased queries. Creating a view is as simple as writing a query and prefixing it with CREATE VIEW view_name AS. For example:

CREATE VIEW holidays AS
SELECT event_id AS holiday_id, title AS name, starts AS date FROM events
WHERE title LIKE '%Day%' AND venue_id IS NULL;

and now you can use this "holidays" view just like a table

SELECT name, to_char(date, 'Month DD, YYYY') AS date FROM holidays
WHERE date <= '2012-04-01';

PostgreSQL’s Strengths

  • flexible query ability

  • very consistent and durable data

  • Most programming languages have battle-tested driver support for Postgres

  • flexibility of the join

  • You needn’t know how you plan to actually query your model, since you can always perform some joins, filters, views, and indexes—odds are good you will always have the ability to extract the data you want

PostgreSQL goes beyond the normal open source RDBMS offerings, such as powerful schema constraint mechanisms. You can write your own

  • language extensions,

  • customize indexes,

  • create custom datatypes,

  • and even overwrite the parsing of incoming queries

PostgreSQL’s Weaknesses

  • Partitioning is not one of the strong suits of relational databases like Post- greSQL. If you need to scale out rather than up (multiple parallel datastores rather than a single beefy machine or cluster), you may be better served looking elsewhere.

  • If your data requirements are too flexible to easily fit into the rigid schema requirements of a relational database

  • or you don’t need the overhead of a full database,

  • or require very high-volume reads and writes as key values,

  • or need to store only large blobs of data,

    then one of the other data-stores might be a better fit

Parting Thoughts

A relational database is an excellent choice for query flexibility. While Post- greSQL requires you to design your data up front, it makes no assumptions on how you use that data. As long as your schema is designed in a fairly normalized way, without duplication or storage of computable values, you should generally be all set for any queries you might need to create. And if you include the correct modules, tune your engine, and index well, it will perform amazingly well for multiple terabytes of data with very small resource consumption. Finally, to those for whom data safety is paramount, Post- greSQL’s ACID-compliant transactions ensure your commits are completely atomic, consistent, isolated, and durable.

PreviousImportant questionsNextC++

Last updated