Source: PostgreSQL Overview: Key Features and Innovations (2023) | Computer Science and Engineering, Arizona State University
Difficulty: Intermediate | Prerequisites: PostgreSQL Fundamentals study notes (Part 1), basic SQL, familiarity with ACID and MVCC concepts.
This set of notes covers the practical feature set of PostgreSQL: data types, integrity constraints, indexing, concurrency mechanisms, reliability tools, security, extensibility, and internationalisation. These are the building blocks you will use when designing schemas, writing queries, and administering a PostgreSQL deployment. If the fundamentals doc covered what PostgreSQL is and why it matters, this one covers what it can do and how it does it. You should already be comfortable with basic SQL, the ACID properties, and the concept of MVCC before working through this material.
PostgreSQL ships a wide range of data types (from integers to JSON documents to geometric shapes), enforces data integrity through constraints and locks, and provides advanced indexing (B-tree, GiST, GIN, BRIN) alongside MVCC for high-concurrency performance. It handles reliability through WAL and multiple replication modes, secures data with row-level security and multi-factor authentication, and is extensible through custom types, procedural languages, and foreign data wrappers.
JSONB
A binary JSON storage format in PostgreSQL that allows efficient indexing and querying of JSON documents. Think of it as JSON that the database can search through quickly, rather than just storing as a text blob.
B-tree index
The default index type in PostgreSQL, suitable for equality and range queries on sortable data. In simple terms, it is a balanced tree structure that keeps your lookups fast for most common query patterns.
GiST (Generalised Search Tree)
An index framework that supports complex data types such as geometric shapes, full-text search, and range types. Think of it as a flexible index template that different data types can plug into.
GIN (Generalised Inverted Index)
An index type optimised for values that contain multiple elements, such as arrays, JSONB documents, and full-text search vectors. In simple terms, it indexes the individual elements inside a composite value, so you can search for "any row containing this element."
BRIN (Block Range Index)
A compact index that stores summary information about ranges of physical table blocks. It is most effective on large, naturally ordered tables (e.g., time-series data). Think of it as a rough guide that tells PostgreSQL which blocks to skip entirely.
Table partitioning
The practice of splitting a large table into smaller, more manageable pieces (partitions) based on a key such as date range or category. Each partition is stored separately, which can dramatically improve query performance and maintenance.
Point-in-time recovery (PITR)
The ability to restore a database to any specific moment in the past by replaying WAL records up to that point. In simple terms, you can "rewind" the database to exactly 2:47pm last Tuesday, not just to the last full backup.
Row-level security (RLS)
A PostgreSQL feature that lets you define policies controlling which rows a given user can see or modify in a table. Think of it as per-row permissions: different users querying the same table get different results based on who they are.
Logical replication
A replication method that sends changes at the logical level (individual row inserts, updates, deletes) rather than copying raw disk blocks. This allows selective replication of specific tables and even cross-version replication between different PostgreSQL major versions.
JIT (Just-in-Time) compilation
A feature where PostgreSQL compiles query expressions into native machine code at runtime, rather than interpreting them. This can significantly speed up complex analytical queries that process large volumes of data.
Integer, Numeric (arbitrary precision), String (varchar, text, char), Boolean
These are the bread-and-butter types present in all SQL databases
Date/Time: full support for timestamps, intervals, and time zones
Array: any column can hold an array of its base type (e.g., integer[], text[])
Range / Multirange: represent a range of values in a single column (e.g., date ranges for bookings)
UUID: natively supported, useful for distributed systems where sequential IDs are impractical
JSON: stored as text, validated on input
JSONB: binary representation, indexable with GIN indexes, supports containment and path queries
XML: full XML storage and querying
Hstore: simple key-value store, predates JSONB and still used for flat key-value needs
Built-in types for Point, Line, Circle, Polygon
Extended to full geospatial capability via PostGIS (separate extension)
Composite types: user-defined row types (like a struct in C)
Enum types: named sets of allowed values
Custom base types: entirely new types with their own input/output functions and operators
UNIQUE, NOT NULL: enforce that column values are non-null and/or distinct across rows
Primary keys: the row's unique identifier; every table should have one
Foreign keys: enforce referential integrity between tables (a value in one table must exist in another)
Exclusion constraints: a PostgreSQL-specific feature that prevents overlapping or conflicting values using a generalised operator (e.g., no two bookings may overlap in time for the same room)
Explicit locks: manually control access to rows or tables when the default MVCC behaviour is insufficient
Advisory locks: application-level locks that PostgreSQL manages but does not enforce automatically; used for coordinating between application processes
B-tree: default index; handles equality and range queries; most common choice
Multicolumn indexes: a single index across multiple columns, useful for queries that filter on several fields
Expression indexes: index the result of a function or expression, not just a raw column (e.g., index on lower(email))
Partial indexes: index only rows that satisfy a condition, saving space and speeding up targeted queries
GiST: supports complex types (geometry, full-text, ranges)
SP-GiST: space-partitioned GiST, for data with natural clustering (quad-trees, k-d trees)
KNN GiST: nearest-neighbour search using GiST
GIN: inverted index for multi-valued columns (arrays, JSONB, tsvector)
BRIN: block range index for large, physically ordered tables
Covering indexes: include non-key columns in the index so index-only scans can answer the query without touching the table
Bloom filters: probabilistic index for queries with many columns in the WHERE clause
Sophisticated query planner/optimiser that evaluates multiple execution strategies
Index-only scans: answers queries entirely from the index when all required columns are included
Multicolumn statistics: the optimiser can collect statistics on correlations between columns for better query plans
MVCC ensures readers never block writers, and writers never block readers
Supports all SQL-standard transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable
Nested transactions via savepoints: partial rollback within a transaction
Parallelisation of read queries and B-tree index builds across multiple CPU cores
Table partitioning: split large tables for faster scans and simpler maintenance
JIT compilation: complex expressions compiled to native code at runtime for analytical workloads
Write-Ahead Logging (WAL): all changes logged before being applied; the foundation for crash recovery and replication
Asynchronous replication: changes are sent to replicas after the transaction commits on the primary; slight lag is possible but throughput is higher
Synchronous replication: the primary waits for at least one replica to confirm receipt before committing; stronger durability guarantee at the cost of latency
Logical replication: replicates changes at the row level (not raw disk blocks); allows selective table replication and cross-version replication
Point-in-time recovery (PITR): restore the database to any moment by replaying WAL records from a base backup up to the target timestamp
Active standbys: replica servers that can serve read queries while staying synchronised with the primary
Tablespaces: place tables and indexes on different physical storage devices, useful for managing I/O and storage capacity
Authentication methods: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, certificate-based authentication, and more; configured in the pg_hba.conf file
Robust access-control system: role-based access control with GRANT/REVOKE on databases, schemas, tables, columns, and functions
Column-level security: restrict which columns a role can SELECT, INSERT, or UPDATE
Row-level security (RLS): define policies that filter which rows a given role can see or modify; enforced transparently by the database engine
Multi-factor authentication: combine certificate-based authentication with an additional method (e.g., password) for stronger access control
Stored functions and procedures: write reusable logic that runs inside the database, reducing round-trips between application and server
Procedural languages: PL/pgSQL (built-in), plus Perl, Python, Tcl natively; Java, JavaScript (V8), R, Lua, and Rust available via extensions
SQL/JSON support: constructors, query functions, path expressions, and JSON_TABLE for working with JSON data using SQL syntax
Foreign data wrappers (FDW): connect to external data sources (other databases, CSV files, REST APIs) and query them as if they were local PostgreSQL tables
Customisable storage interface: the pluggable storage API allows alternative table storage engines
Extension ecosystem: hundreds of extensions available; PostGIS (geospatial), pg_stat_statements (query statistics), pgcrypto (encryption), and many others; installed with a simple CREATE EXTENSION command
ICU collations: support for international character sets and locale-aware sorting through the International Components for Unicode library
Case-insensitive and accent-insensitive collations: query "cafe" and match "Café" without extra application logic
Full-text search: built-in support for tokenising, stemming, ranking, and searching natural-language text; uses the tsvector and tsquery types with GIN or GiST indexes; no external search engine required for many use cases
The JSONB data type is why many teams use PostgreSQL as both a relational database and a document store, avoiding the need for a separate NoSQL system. PostGIS powers mapping and logistics platforms worldwide. Row-level security is used in multi-tenant SaaS applications where a single database serves many customers, each seeing only their own data. Logical replication enables zero-downtime major version upgrades in production environments.
Students often think a B-tree index helps with every query. It does not. B-trees are suited to equality and range predicates on sortable types. A full-text search query needs a GIN or GiST index; a spatial query needs GiST; a containment query on JSONB needs GIN.
There is a common assumption that JSON and JSONB are interchangeable. They are not. JSON stores the original text (preserving whitespace and key order). JSONB stores a decomposed binary representation that supports indexing and efficient querying. For almost all purposes, JSONB is the better choice.
Students sometimes believe that row-level security replaces application-level access control. It complements it. RLS is enforced at the database layer, but application logic still needs to set the correct role or session variables.
Partitioning is not always faster. If the query planner cannot eliminate partitions (because the query does not filter on the partition key), it may scan all partitions, which can be slower than a single table.
⚠️ Know which index type to use for which query pattern. B-tree for equality/range, GIN for containment and full-text, GiST for spatial and complex types, BRIN for large ordered tables. This comes up in schema design and query optimisation questions.
⚠️ Be able to explain the difference between JSON and JSONB, and when to choose each.
⚠️ Understand the three replication modes (asynchronous, synchronous, logical) and their trade-offs. Expect questions asking you to choose a mode for a given scenario.
⚠️ PITR is a common exam topic. Know the mechanism: base backup + WAL replay to a target timestamp.
⚠️ Exclusion constraints are a distinctive PostgreSQL feature. Be able to give an example (e.g., preventing overlapping time ranges for the same resource).
True or False: A GIN index is the best choice for range queries on an integer column. (False: B-tree is the default and best choice for range queries on sortable types. GIN is for multi-valued/containment queries.)
Fill in the blank: _____ replication sends row-level changes and allows selective table replication. (Logical)
True or False: Row-level security policies are enforced at the application layer, not the database layer. (False: RLS is enforced by the PostgreSQL engine itself.)
Fill in the blank: To restore a database to a specific point in time, you need a base backup and the _____ records up to that moment. (WAL)
True or False: JSONB preserves the original formatting and key order of the JSON input. (False: JSONB stores a decomposed binary representation; JSON preserves the original text.)
Q: You have a table of hotel bookings with columns for room_id, check_in, and check_out. How would you prevent double-bookings at the database level?
A: Use an exclusion constraint with a GiST index on a daterange constructed from check_in and check_out, constrained per room_id. The exclusion constraint prevents any two rows with the same room_id from having overlapping date ranges.
Q: When would you choose a BRIN index over a B-tree index?
A: BRIN is best for very large tables where the indexed column is naturally correlated with the physical order of rows on disk (e.g., a timestamp column in a time-series table that is inserted in chronological order). BRIN indexes are much smaller than B-tree indexes on the same data, but they only help when the data's physical layout matches the query's filter.
Q: Explain the difference between synchronous and asynchronous replication and give a scenario where each is appropriate.
A: In asynchronous replication, the primary commits and sends changes to replicas without waiting for confirmation; this is faster but risks a small window of data loss if the primary fails before the replica receives the changes. In synchronous replication, the primary waits for at least one replica to confirm, ensuring no committed data is lost, but at higher latency. Use asynchronous for read-scaling where a brief lag is acceptable; use synchronous for financial or medical systems where data loss is unacceptable.
Q: What is the practical difference between JSON and JSONB in PostgreSQL, and which should you typically use?
A: JSON stores the input as exact text (preserving whitespace, duplicate keys, and key order). JSONB parses it into a binary representation that drops whitespace, removes duplicate keys, and does not preserve key order, but supports indexing (GIN), containment operators, and efficient path queries. For nearly all application use cases, JSONB is the correct choice because it enables the database to query and index the document contents.
Q: How does point-in-time recovery (PITR) work in PostgreSQL?
A: PITR combines a base backup (a full copy of the database files) with WAL archives. To recover to a specific moment, you restore the base backup and then replay WAL records sequentially up to the target timestamp. This allows recovery to any point after the base backup was taken, not just to the moment of the last full backup.
The indexing material connects to data structures coursework (B-trees, hash tables, inverted indexes). Replication and WAL link to distributed systems topics such as consensus, leader election, and the CAP theorem. Row-level security connects to access control theory and multi-tenant architecture design. Foreign data wrappers and the extensibility model relate to middleware, data integration, and federated database systems. Full-text search connects to information retrieval, tokenisation, and ranking algorithms.
PostgreSQL features, Postgres data types, JSONB vs JSON, B-tree index, GiST index, GIN index, BRIN index, covering index, Bloom filter index, expression index, partial index, exclusion constraint, advisory lock, table partitioning, query planner, query optimiser, index-only scan, JIT compilation, WAL, write-ahead logging, asynchronous replication, synchronous replication, logical replication, PITR, point-in-time recovery, active standby, tablespace, SCRAM-SHA-256, row-level security, RLS, column-level security, multi-factor authentication, PL/pgSQL, foreign data wrapper, FDW, PostGIS, full-text search, tsvector, tsquery, ICU collation, stored procedures, CREATE EXTENSION