PostgreSQL capabilities

PostgreSQL provides enterprise-level services which guarantee the continuation of the business.

Replication

Replication allows data from one database server to be replicated to another server. Replication is used mainly to achieve the following:

  • High availability: A second server can take over if the primary server fails.
  • Load balancing: Several servers can serve the same requests.
  • Faster execution: A query is executed on several machines at once to gain performance.

PostgreSQL supports replication out of the box via streaming replication. Streaming replication is a master-slave replication that uses file-based log shipping. Streaming replication is a binary replication technique, because SQL statements are not analyzed. It is based on taking a snapshot of the master node, and then shipping the changes—the WAL files—from the master node to the slave node and replaying them on the slave. The master can be used for read/write operations, and the slave can be used to serve read requests. Streaming replication is relatively easy to set up and configure; it can support synchronous and asynchronous replications as well as cascading replication. In synchronous replication, a data modifying operation must be committed on all servers in order to be considered successful. In cascading replication, one could add a replica to a slave. This allows PostgreSQL to scale horizontally for read operations.

In addition to PostgreSQL streaming replication, there are several other open source solutions to target different workloads, such as:

  • Slony-1: This is a master to multiple slave replication systems. Unlike PostgreSQL, it can be used with different server versions. So, one could replicate the 9.2 server data to the 9.4 server.
  • pgpool-II: This is middleware between PostgreSQL and the client. In addition to replication, it can be used for connection pooling, load balancing, and parallel query execution.
  • Distributed Replicated Block Device (DRBD): A general solution for HA. It can be understood as a network RAID-1.

Security

PostgreSQL supports several authentication methods including trust, password, LDAB, GSSAPI, SSPI, Kerberos, ident-based, RADUIS, certificate, and PAM authentication. All database vulnerabilities are listed in the PostgreSQL security information web page—http://www.postgresql.org/support/security/—with information about the affected version, vulnerability class, and the affected component.

The PostgreSQL security updates are made available as minor updates. Also, known security issues are always fixed with the next major releases. Publishing security updates in minor updates makes it easy for a PostgeSQL administrator to keep PostgreSQL secure and up-to-date with minimal downtime.

PostgreSQL can control the database object access at several levels including database, table, view, function, sequence, and column. This enables PostgreSQL to have a great authorization control.

PostgreSQL can use encryption to protect data by hardware encryption. Also, one can encrypt certain information by utilizing the pgcrypto extension.

Extension

PostgreSQL can be extended to support new data types. PostgreSQL provides the CREATE EXTENSION command to load extensions to the current database. Also, PostgreSQL has a central distribution network (PGXN)—www.pgxn.org—which allows users to explore and download extensions. When installing the PostgreSQL binaries, the postgresql-postgresql-contib package contains many useful extensions such as tablefunc, which allows table pivoting and pgcrypto extension; the README file in the installation directory contains the summary information.

The ability of PostgreSQL to support extensions is a result of the following features:

  • PostgreSQL data types: PostgreSQL has very rich data types. It supports primitive data types as well as some primitive data structures, such as arrays, out of the box. In addition to that, it supports complex data types, such as:
    • Geometric data types: Including point, line segment (lseg), path, polygon and box.
    • Network address types: Including cidr, inet, and macaddr.
    • tsvector: This is a sorted list of lexemes that enables postgres to perform full text search.
    • Universal unique identifiers (UUID): UUID solves many problems related to databases, such as offline data generation.
    • NoSQL: It supports several NoSQL data types including XML, Hstore, and JSONB.
    • Enum, range and domain are user-defined data types with specific constraints, such as a set of allowed values, data range constraint, and check constraints.
    • Composite data type is a user-defined data type, where an attribute is composed of several attributes.

      The following example shows how to create a new composite type called phone_number. An equality operator is also created to check if two phone numbers are equal by comparing the area code and the line number.

      --This example shows how to create a composite data type.
CREATE TYPE phone_number AS (
    area_code varchar(3),
    line_number varchar(7)
);
CREATE OR REPLACE FUNCTION phone_number_equal (phone_number,phone_number) RETURNS boolean AS
$$
    BEGIN
        IF $1.area_code=$2.area_code AND $1.line_number=$2.line_number THEN
            RETURN TRUE ;
        ELSE
            RETURN FALSE;
        END IF;
END;
$$
LANGUAGE plpgsql;
CREATE OPERATOR = (
    LEFTARG = phone_number,
    RIGHTARG = phone_number,
    PROCEDURE = phone_number_equal
);
--For test purpose
SELECT row('123','222244')::phone_number = row('1','222244')::phone_number;
  • Supported languages: PostgreSQL allows functions to be written in several languages. The PostgreSQL community supports the following languages: SQL, C, Python, PL/pgSQL, Perl, and Tcl. In addition to these, there are many externally maintained procedural languages, including Java, R, PHP, Ruby, and UNIX shell.

NoSQL capabilities

PostgreSQL is more than a relational database and an SQL language. PostgreSQL is now home to different NoSQL data types. The power of PostgreSQL and schema-less data stores enable the developers to build reliable and flexible applications in an agile way.

PostgreSQL supports the JavaScript Simple Object Notation (JSON) data type, which is often used for sharing data across different systems in modern RESTful web applications. In PostgreSQL release 9.4, PostgreSQL introduced another structured binary format for saving JSON documents instead of using the JSON format in the prior versions. The new data type is called JSONB. This data type eliminates the need for parsing a JSON document before it is committed to the database. In other words, PostgreSQL can ingest a JSON document at a speed comparable with document databases, while still maintaining compliance with ACID.

Key/value pairs are also supported by the PostgreSQL hstore extension. The Hstore is used to store semi-structured data, and it can be used in several scenarios to decrease the number of attributes that are rarely used and often contain null values.

Finally, PostgreSQL supports the Extensible Markup Language (XML) data type. XML is very flexible and it is often used to define document formats. XML is used in RSS, Atom, SOAP, and XHTML. PostgreSQL supports several XML functions to generate and create XML documents. Also, it supports xpath to find information in an XML document.

Foreign data wrapper

In 2011, PostgreSQL 9.1 was released with a read-only support for SQL / Management of External Data (MED) ISO/IEC 9075-9:2003 standard. SQL/MED defines foreign data wrappers (FDW) to allow the relational database to manage external data. Foreign data wrappers can be used to achieve data integration in a federated database system environment. PostgreSQL supports RDBMS, NoSQL, and file foreign data wrappers including Oracle, Redis, Mongodb, and delimited files.

A simple use case for FDW is to have one database server for analytical purposes, and then ship the result of this server to another server that works as a caching layer.

Also, FDW can be used for testing data changes. Imagine you have two databases, one with different data due to applying a certain development patch. One could use FDW to assess the effect of this patch by comparing the data from the two databases.

PostgreSQL supports postgres_fdw starting from release 9.3. postgres_fdw is used to enable data sharing and access between different PostgreSQL databases. It supports the SELECT, INSERT, UPDATE, and DELETE operations on foreign tables.

Performance

PostgreSQL has a proven performance. It employs several techniques to improve concurrency and scalability, including the following:

  • PostgreSQL locking system: PostgreSQL provides several types of locks at the table and row levels. PostgreSQL is able to use more granular locks that prevent locking/blocking more than necessary; this increases concurrency and decreases the blocking time.
  • Indexes: PostgreSQL provides four types of indexes: B-Tree, hash, generalized inverted index (GIN), and the Generalized Search Tree (GiST) index. Each index type can be used for a certain scenario. For example, B-tree can be used for equality and range queries efficiently. GIST can be used for text search and for geospatial data. PostgreSQL supports partial, unique, and multicolumn indexes. It also supports indexes on expressions and operator classes.
  • Explain, analyze, vacuum, and cluster: PostgreSQL provides several commands to boost performance and provide transparency. The EXPLAIN command shows the execution plan of an SQL statement. One can change some parameter settings such as memory settings, and then compare the execution plan before and after the change. The ANALYSE command is used to collect the statistics on tables and columns. The VACUUM command is used for garbage collection to reclaim unused hard disk space. The CLUSTER command is used for arranging data physically on the hard disk. All these commands can be configured based on the database workload.
  • Table inheritance and constraint exclusion: Table inheritance allows the creation of tables with the same structure easily. Those tables are used to store subsets of data based on a certain criteria. This allows a very fast retrieval of information in certain scenarios, because only a subset of data is accessed when answering a query.

Very rich SQL constructs

PostgreSQL supports very rich SQL constructs. It supports correlated and non-correlated subqueries. It supports common table expression (CTE), window functions, and recursive queries. Once developers have learned these SQL constructs, they will be able to write a crisp SQL code very quickly. Moreover, they will be able to write complex queries with minimal effort. The PostgreSQL community keeps adding new SQL features in each release; in release 9.4, three SQL clauses were added: FILTER, WITHIN GROUP, and the ALTER SYSTEM clause.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.93.104