Database Structure in PostgreSQL
Developed by Professor Michael Stonebraker at the University of California, Berkeley, PostgreSQL is an ORDBMS. Named after the original relational database system (“Ingres”), “Postgres” PostgreSQL is noted for its durability, data integrity, and accuracy, as well as its 15-year-old architecture.
Designed for dependability, data integrity, and extensibility, PostgreSQL is an object-relational database management system (ORDBMS) that arranges data in a highly structured, hierarchical fashion. This framework makes it easier to translate intricate business needs into a solid database design.
The most basic PostgreSQL installation uses a client/server architecture, in which a database cluster is managed by a single PostgreSQL server instance. All managed databases are physically housed in this cluster, which is a disc storage area that was initialised by the initdb command. Postgres (for utilities and third-party applications), template1 (a default for cloning new databases), and template0 (a pristine template that stays unchanged) are the default databases included in each cluster.
PostgreSQL’s database structure is established in a hierarchical fashion:
Database Cluster: A PostgreSQL server instance oversees a database cluster at the most high level. In addition to being a group of databases, this cluster has shared items like roles and tablespaces that are present in all of the databases in it.
Databases: A cluster may contain one or more of these named databases. Although they can access objects at the cluster level, these databases are mainly segregated from one another.
Template Databases: A new database is basically a copy of an existing template database. There are two main types of template databases:
Template1: Extensions or settings that ought to be present in all newly created databases can be added to this template, which is the default.
Template0: Due to the fact that it does not contain encoding- or locale-specific data, template0 is used as a backup or version database to repair a corrupted template1 or to restore a database dump. Through access privileges and roles, database access rights can be controlled.
Schemas: One or more named schemas are present in every database, and they are used to arrange database objects. Similar to operating system directories, schemas offer a means of logically grouping items and avoiding name clashes.
- In every new database, the public schema is automatically constructed and, unless otherwise noted, is where new items are stored by default.
- When unqualified object names appear in queries, the order in which schemas are examined is determined by the schema search route (search path). This enables many schemas to utilise the same object name.
- Additionally, schemas can be used to retain third-party SQL code and manage authorisation.
Tables: In PostgreSQL, tables are the main unit of storage. Tables are organised groups of rows, or tuples, where each row has a set of named columns, or attributes.
Data Types: Each column has a data type numeric, character strings (TEXT, VARCHAR, CHAR), day and time, boolean, geometric, network address, array, composite, range, or object identifiers (OID). JSON and JSONB (binary JSON) are semi-structured data types in PostgreSQL, with JSONB indexing for faster processing.
Storage: The heap files, which employ a common slotted-page format, are where tuples in a table are kept.
Constraints: For the purpose of ensuring the quality and consistency of data, constraints are essential. Common varieties consist of:
NOT NULL: The NOT NULL constraint in PostgreSQL prevents attributes from having null values, guaranteeing a column always has data. This limitation is essential for data integrity. Any attempt to insert or update a NULL value into a column with a NOT NULL constraint will fail.
UNIQUE: The PostgreSQL UNIQUE constraint ensures data integrity by ensuring that all values in a column or collection of columns are distinct across all rows. Any attempt to insert or update data with duplicate entries will fail and create an error.
PRIMARY KEY: The PostgreSQL PRIMARY KEY constraint uniquely identifies each table row, ensuring data integrity. By definition, a primary key must be unique and NOT NULL to prevent missing or undefined entries in the primary key column(s).
FOREIGN KEY: A foreign key is a basic database concept that maintains referential integrity between two tables. It relates to and identifies a tuple (row) in another database by acting as an attribute or combination of attributes in the referencing table. Foreign keys usually reference the table’s primary key or unique constraint.
CHECK: A CHECK constraint enforces data validity by setting a condition for a field or combination of fields in each table row. This condition is a Boolean statement that can involve one or more characteristics in a tuple.
Inheritance: Table inheritance in PostgreSQL lets a “child” table inherit properties and columns from a “parent” or “root” table.
Indexes:Indexes speed up database retrieval. They are independent files that have references to the table’s data places. Different index types are supported by PostgreSQL, including:
B-tree:PostgreSQL’s default and most frequent index type is a B-tree, based on Lehman and Yao’s B-link tree, a high-concurrency variation of B+-trees. The balanced tree structure effectively handles several query patterns, including equality and range queries on sortable data, BETWEEN, IN, IS NULL, and IS NOT NULL conditions.
BRIN (Block Range Index): Large tables can benefit from BRIN (Block Range Index), which stores summary data for pages that are physically next to each other. It is slower than B-tree but uses less storage.
Hash, GiST (Generalized Search Tree), GIN (Generalized Inverted Index), SP-GiST (Space-Partitioned GiST): Hash, GIN (Generalised Inverted Index), SP-GiST (Space-Partitioned GiST), and GiST (Generalised Search Tree) are extra specialised index types made for particular kinds of data and query patterns.
Partial Indexes: In order to reduce index size and increase access efficiency, partial indexes only index a portion of table data that satisfies a particular criterion.
Unique Indexes: Unique indexes are database objects that enforce value uniqueness in columns or combinations of columns. Data integrity requires that no two rows in the table have identical indexed attribute(s) values. PostgreSQL only allows unique B-tree indexes.
Views: Essentially a named query, a view serves as a container for a SELECT statement. Views can assist manage data privileges, operate as an abstraction layer, and act as a data access layer.
Functions and Triggers
Functions: User-defined functions in PL/pgSQL, SQL, or C are supported by PostgreSQL. They are interdependent and categorizable.
Triggers: PostgreSQL triggers automatically execute a trigger function when a table, view, or foreign table event happens. PostgreSQL supports event triggers for DDL commands and DML operations like INSERT, UPDATE, DELETE, and TRUNCATE.
Tablespaces: Admins can store tables and indexes in these spaces. Tablespaces are important for maintenance (relocating data if a hard disc partition fills) and optimisation (storing frequently used data on SSDs).
Code Example:
CREATE SCHEMA IF NOT EXISTS public_schema;
CREATE SCHEMA IF NOT EXISTS hr;
SET search_path TO hr, public_schema;
CREATE TABLE hr.departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE hr.employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
birth_date DATE,
hire_date DATE CHECK (hire_date > '2000-01-01'),
salary NUMERIC(10,2) CHECK (salary > 0),
is_active BOOLEAN DEFAULT TRUE,
department_id INT REFERENCES hr.departments(department_id),
contact_info JSONB
);
INSERT INTO hr.departments (department_name) VALUES
('Engineering'), ('Sales'), ('Human Resources');
INSERT INTO hr.employees
(first_name,last_name,email,birth_date,hire_date,salary,department_id,contact_info) VALUES
('Alice','Johnson','alice.j@example.com','1990-05-15','2015-08-20',85000,1,'{"phone":"123-456-7890","emergency_contact":"Bob"}'),
('Bob','Williams','bob.w@example.com','1985-11-20','2018-03-10',95000,1,'{"phone":"987-654-3210"}'),
('Charlie','Brown','charlie.b@example.com','1992-07-30','2020-01-05',70000,2,NULL);
CREATE INDEX idx_employee_last_name ON hr.employees(last_name);
CREATE VIEW hr.employee_details AS
SELECT e.first_name,e.last_name,e.email,e.hire_date,d.department_name
FROM hr.employees e JOIN hr.departments d ON e.department_id=d.department_id;
SELECT * FROM hr.employee_details;
CREATE OR REPLACE FUNCTION hr.get_employee_count()
RETURNS INTEGER AS
BEGIN
RETURN (SELECT COUNT(*) FROM hr.employees);
END; LANGUAGE plpgsql;
SELECT hr.get_employee_count();Output:
CREATE SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
CREATE INDEX
CREATE VIEW
first_name | last_name | email | hire_date | department_name
------------+-----------+-----------------------+------------+-----------------
Bob | Williams | bob.w@example.com | 2018-03-10 | Engineering
Alice | Johnson | alice.j@example.com | 2015-08-20 | Engineering
Charlie | Brown | charlie.b@example.com | 2020-01-05 | Sales
(3 rows)
CREATE FUNCTION
get_employee_count
3
(1 row)Conclusion
In conclusion, PostgreSQL’s hierarchical architecture and extensibility ensure stability, data integrity, and flexibility for complex applications. From its database clusters, template databases, and schemas to its complex data types, constraints, indexing methods, and functions, PostgreSQL provides precision and scalability. Client/server design, comprehensive system catalogues, and user-defined items make it flexible for changing business and technical needs. An enterprise-grade, solution for mission-critical applications, PostgreSQL enforces data consistency with constraints and optimises performance with specialised indexes and tablespaces.