Postgres! What is it?

PostgresSQL is an open-source project that has become one of the most advanced database management systems in the world. It is a relational database system comprised of tables with unique keys and powered by the SQL command language. To better understand Postgres, it is important to have a fundamental understanding of relational databases, in general.

A relational database is one made of tables that are comprised of columns and rows. Each row is given a unique key allowing the user to access data specific to that key and utilize the properties that intersect at both row and column. Essentially, these tables are hash tables but its more accurate to describe the key or index as a hash index for that specific table.

Relational databases were invented in the 1970’s by E.F. Codd while he was working at I.B.M. and he developed a set of rules, known as Codd’s 12 rules, that define what constitutes a relational database. While it is not necessary to implement each rule in a functioning relational database, it illustrates the founding principles for how these data storage structures are built.

Now that we have some context on Postgres’ structure, let’s discuss the tools we use to make actions happen within the database. We’re talking about the SQL command language!

In Postgres inception, it was not used with SQL but a different command language known as QUEL but there was nothing cool about it. In 1995, the QUEL language and the monitor terminal associated with it were scrapped for SQL and the psql terminal.

SQL is a declarative programming language where a single command can be issued to access records in a database. It is based on relational algebra and tuple relational calculus. Luckily, the declarative style allows a user to type simple, intuitive commands.

For example, without knowing what this program executes…

const getEvents = () => {
  return new Promise((resolve, reject) => {
    db.query('SELECT * FROM events', (err, events) => {
      if (err) {
        return reject(err);
      }
      db.query('SELECT * FROM rsvps', (err, rsvps) => {
        events.forEach(event => {
          event.attendees = rsvps.filter(rsvp => {
            return event.id === rsvp.eventId;
          }).map(rsvp => rsvp.fullName);
        });
        resolve(events);
      });

    });
  });
};

We can get a general of sense of what is happening. SQL makes a selection from the database using the select all (*) wildcard. It reaches into the events table, getting all events then making a subsequent search into the RSVP table. It iterates through the events and finds the id’s that match the events foreign key id within the RSVP table.

This is an excellent display of the power and functionality of both the relational database and SQL command language.

So, what makes Postgres Postgres?

One of the main confusions I had when first working with Postgres was defining what set it apart from MySQL. It has many of the same commands and features and the terminals, though different, are similar enough. So, why should anyone use choose Postgres over MySQL.

I think the strongest selling point is going to be the implementation of object-oriented database functionality like that of MongoDb. An object-oriented database maintains a hierarchical relationship between database tables. As object-oriented programming makes use of inheritance patterns, it only makes sense that Postgres would establish the INHERIT keyword as its means of making this cross database utility feature.

Database Transactions

A transaction is an integral feature of any database. It combines multiple steps into a single ‘transaction’. It is an all or nothing type of commit that guarantees any update is permanently recorded to the database and possibly written to disk as well. Transactions are universal for relational databases but not common knowledge amongst inexperienced users.

Transactions are built upon ACID properties whereby data validity is guaranteed when these properties are upheld. ACID is an acronym for Atomicity, Consistency, Isolation, and Durability.

Atomicity describes the all-or-nothing bundling of steps that treat database queries as a whole. Several commands are bundled into a single transaction. Only when the entire transaction completes is any single step updated within the database.

Consistency is providing a set of rules and features that insist that a transaction is not corrupted. It does not guarantee the data itself but how the data is governed within the database.

Isolation is part of a larger relational database component known as concurrency control. This is where multiple transactions are executed simultaneously but the state of the transactions change as if they were happening sequentially. This is especially important for databases that hold finite sets of data.

Durability ensures that once a transaction is committed to the database it remains committed. This can be ensured as simply as writing the database to memory in the case of an outage.

Multiversion Concurrrency Control(MVCC) is a feature of Postgres that gives it an edge over MySQL’s performance. It allows multiple users to read data while others write data without hindering speed. Under the hood, each statement sees a snapshot of the previous state of the database to ensure the consistency of the current data being altered.

I hope this read helped to persuade you to give Postgres a try for your next project. Databases aren’t the most exciting feature of any website or application, but they are indispensable. If you don’t need a robust database MySQL should suffice. However, If you are going to be handling extreme amounts of data that changes on a daily basis, Postgres should be your choice.