PostgreSQL

Basic funtions

ACID compliant

ACID (A tomicity, C onsistency, I solation, D urability) is a series of properties that database transactions should fulfil to guarantee the validity of the data even in the event of a fault.

SQL:2011

temporal_tables also meet the SQL standard ISO/IEC 9075:2011, including:

  • Time period definitions

  • Valid time tables

  • Transaction time tables (system-versioned tables) with time-sliced and sequenced queries

Data types

The following data types are supported out of the box:

  • primitive data types: Integer, Numeric, String, Boolean

  • structured data types: Date/Time, Array, Range, UUID

  • document types: JSON/JSONB, XML, key-value (Hstore)

  • geometric data types: point, line, circle, polygon

  • adjustments: composite, custom Types

  • transactional data definition language (DDL)

    Transactional DDL is implemented via write-ahead logging. Big changes are also possible, but not adding and dropping databases and tables:

    $ psql mydb
    mydb=# DROP TABLE IF EXISTS foo;
    NOTICE: table "foo" does not exist
    DROP TABLE
    mydb=# BEGIN;
    BEGIN
    mydb=# CREATE TABLE foo (bar int);
    CREATE TABLE
    mydb=# INSERT INTO foo VALUES (1);
    INSERT 0 1
    mydb=# ROLLBACK;
    ROLLBACK
    mydb=# SELECT * FROM foo;
    ERROR: relation "foo" does not exist
    
Concurrent Index

PPostgreSQL can create indexes without having to lock write access to tables.

Extensions

PostgreSQL can easily be extended. The contrib/ directory supplied with the source code contains various extensions that are described in Appendix F. Other extensions have been developed independently, such as PostGIS or Slony-I.

Common Table Expression

WITH Queries (Common Table Expressions) divides complex queries into simpler queries, e.g .:

WITH regional_insolation AS (
    SELECT region, SUM(amount) AS total_insolation
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_insolation
    WHERE total_insolation > (SELECT SUM(total_insolation)/10 FROM regional_insolation)
)

There is also a RECURSIVE modifier that refers the WITH query to its own output. The following is an example of how to sum the numbers from 1 to 100:

WITH RECURSIVE t (n) AS (
    WERTE (1)
  UNION ALL
    SELECT n + 1 FROM t WO <100
)
SELECT sum (n) FROM t;
Multi-Version Concurrency Control (MVCC)

Multi-Version Concurrency Control allows two or more sessions to access the same data at the same time without compromising the integrity of the data.

Cross platform

PostgreSQL runs on common CPU architectures such as x86, PowerPC, Sparc, ARM, MIPS or PA-RISC. Most operating systems are also supported: Linux, Windows, FreeBSD, OpenBSD, NetBSD, Mac OS, AIX, HP/UX and Solaris.

See also

explain.depesz.com

Web app that visualises PostgreSQL’s EXPLAIN and ANALYZE statements.