PostgreSQL
by
Bharat Veerchand Chhajer
PostgreSQL: Relational Database
Introduction to Relational Databases & SQL
What is a Relational Database?
A relational database stores data in tables made up of rows and columns. Each
table represents an entity (e.g., users, products), and relationships can be
established between them using foreign keys.
What is SQL?
SQL stands for Structured Query Language. It's used to:
Create and modify tables and databases
Insert, update, delete data
Query data (SELECT)
Control access to the data
PostgreSQL: Relational Database
What is PostgreSQL?
PostgreSQL is a powerful, open-source object-relational database system.
It supports:
• Advanced data types (JSON, XML, arrays)
• Full ACID compliance
• Concurrency with MVCC (Multi-Version Concurrency Control)
• Extensions like PostGIS (for GIS data), TimescaleDB (for time-series)
PostgreSQL Engine:
• WAL (Write-Ahead Logging) for durability
• MVCC for concurrent transactions
• Support for pluggable extensions (e.g., full-text search, time-series)
PostgreSQL: Relational Database
Object-Relational Database System
PostgreSQL is not just a relational database (like MySQL), but also an object-
relational one, meaning:
• You can define custom data types
• Use inheritance (a table can inherit columns from another)
• Store complex data (like arrays, JSON, etc.)
• Support for user-defined functions, operators, and aggregates
• So, it blends traditional relational models with object-oriented features.
PostgreSQL: Relational Database
psql –U postgres
• Exploring the Database
• SELECT current_user;
• SELECT current_database();
• List all databases with \I
• View tables in the current database with \dt
• Get details about a specific table with \d table_name
• See available schemas with \dn
• List functions with \df
• View defined views with \dv
PostgreSQL: Relational Database
Basic Queries:
Create a Database
CREATE DATABASE sample_db;
Create a Table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
age INT
);
PostgreSQL: Relational Database
PostgreSQL Cluster
├── sample_db (← current database)
│ ├── public (← default schema)
│ │ └── users (← your table)
│ └── (you can add more schemas here)
• In PostgreSQL, a schema is a way to organize and group database objects
(tables, views, functions, etc.) within a database.
• Think of it like folders inside a filesystem — it doesn't change the data, but
helps manage, secure, and organize it better.
PostgreSQL: Relational Database
CREATE SCHEMA hr;
CREATE TABLE [Link] (...);
\dt hr.*
Why Use Schemas?
1. Logical Organization
database: company_db
├── schema: hr
│ └── table: employees
└── schema: sales
└── table: employees
PostgreSQL: Relational Database
2. Security & Access Control
You can set permissions per schema, not just per table.
REVOKE ALL ON SCHEMA finance FROM public;
GRANT USAGE ON SCHEMA hr TO hr_user;
3. Multi-Tenancy
Schemas make it easy to support multi-tenant apps:
Schemas:
├── tenant_a
└── tenant_b
Each tenant’s data can live in their own schema, while still using the same database.
PostgreSQL: Relational Database
5. Search Path Control
SET search_path TO hr, public;
SELECT * FROM employees;
This tells PostgreSQL to look for employees in hr first, then public.
PostgreSQL: Relational Database
CREATE SCHEMA hr;
CREATE TABLE [Link] (
id SERIAL PRIMARY KEY,
name TEXT,
department TEXT
);
INSERT INTO [Link] (name, department) VALUES ('Alice', 'Recruitment');
SELECT * FROM [Link];
PostgreSQL: Relational Database
What is Multi-Tenancy?
Multi-tenancy is a software architecture where a single instance of a database or
application serves multiple customers (tenants).
SaaS Example: CRM Software (like Salesforce)
A company builds a CRM platform (Customer Relationship Management) that is used
by hundreds of businesses (tenants).
Each business:
Has its own users
Owns its own customer data
Has access to only its own data
Instead of hosting separate databases for each business, the platform uses multi-
tenancy.
PostgreSQL: Relational Database
Multi-Tenancy Approaches
Option 1: Shared Schema, Tenant ID Column
All tenants' data is in the same tables.
You filter by tenant_id.
CREATE TABLE customers (
tenant_id UUID,
customer_id UUID,
name TEXT,
email TEXT
);
Then: SELECT * FROM customers WHERE tenant_id = 'tenant-a-id';
Pros: Simple, efficient
Cons: Risk of mixing data if queries are wrong
NOTE: Use Row-Level Security (RLS) in PostgreSQL for safer shared-table designs.
PostgreSQL: Relational Database
Option 2: One Schema Per Tenant
Each tenant has a separate schema.
Schema names like tenant_a.customers, tenant_b.customers.
CREATE SCHEMA tenant_a;
CREATE TABLE tenant_a.customers (...);
CREATE SCHEMA tenant_b;
CREATE TABLE tenant_b.customers (...);
Pros: Clean isolation, per-tenant backup
Cons: Can get complex with hundreds/thousands of tenants
PostgreSQL: Relational Database
Option 3: One Database Per Tenant
Each tenant has their own PostgreSQL database.
Pros: Strongest isolation
Cons: Harder to manage at scale
Benefits of Multi-tenancy
• Lower infrastructure costs
• Easier to scale and maintain
• Faster onboarding of new customers
• Clean separation of data with good performance
PostgreSQL: Relational Database
Approach:
database: company_db
├── schema: hr
│ └── table: employees
└── schema: sales
└── table: employees
It improves organization, but it does introduce challenges when you need to
query across schemas.
NOTE: Each schema has its own table named employees, possibly with different
structure or purpose.
PostgreSQL: Relational Database
1. Fully Qualified Names
Use explicit [Link] references:
SELECT * FROM [Link]
UNION ALL
SELECT * FROM [Link];
This is the cleanest way to merge data from multiple schemas in a single query.
UNION ALL is preferred if both tables are identical.
PostgreSQL: Relational Database
2. Use a View to Simplify the above
Create a consolidated view:
CREATE OR REPLACE VIEW all_employees AS
SELECT 'hr' AS source, * FROM [Link]
UNION ALL
SELECT 'sales' AS source, * FROM [Link];
Now just: SELECT * FROM all_employees;
PostgreSQL: Relational Database
3. Use a Wrapper Table
If all employees tables have the same structure, you could create a master table to
insert from both:
CREATE TABLE all_employees (
name TEXT,
dept TEXT,
salary NUMERIC,
source TEXT
);
Then use triggers or batch jobs to sync data.
NOTE: More complex and often overkill unless you're archiving or denormalizing.
PostgreSQL: Relational Database
CREATE SCHEMA sales;
CREATE TABLE [Link] (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
age INT
);
Set search_path (if using only one at a time):
SET search_path TO sales;
SELECT * FROM employees;
PostgreSQL: Relational Database
Insert Data
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@[Link]', 30),
('Bob', 'bob@[Link]', 25);
Update Table Data
UPDATE users
SET age = 31
WHERE name = 'Alice';
Alter Table
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();
PostgreSQL: Relational Database
SELECT Query & Operations
Basic SELECT
SELECT * FROM users;
SELECT name, age FROM users;
WHERE Clause
SELECT * FROM users WHERE age > 25;
ORDER BY
SELECT * FROM users ORDER BY age DESC;
LIMIT
SELECT * FROM users LIMIT 5;
PostgreSQL: Relational Database
COUNT, SUM, AVG
Count all users
SELECT COUNT(*) FROM users;
Sum of ages
SELECT SUM(age) FROM users;
Average age
SELECT AVG(age) FROM users;
Delete a Row
DELETE FROM users WHERE name = 'Bob';
PostgreSQL: Relational Database
•Create User, Database and grant permission:
•CREATE DATABASE directorydb;
•CREATE USER my_user WITH PASSWORD password';
•GRANT ALL PRIVILEGES ON DATABASE directorydb TO my_user;
• \c directory \\changes current database
• CREATE SCHEMA hr;
• GRANT ALL PRIVILEGES ON DATABASE directorydb TO my_user;
NOTE: Above grants CONNECT AND CREATE PREVILEGE
• GRANT ALL ON SCHEMA hr TO my_user ;
• GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA hr TO
my_user;
PostgreSQL: Relational Database
TO LOGIN, CHANGE PASSWORD, give COMPLETE CONTROL - CHANGE OWNER
• psql -U my_user -d my_database –W
• ALTER USER my_user WITH PASSWORD 'new_password';
• ALTER DATABASE my_database OWNER TO my_user;
TO CHECK PERMISSION OF A USER ON PUBLIC SCHEMA
• \dn+ public
PostgreSQL: Relational Database
How to Create tenant_b from tenant_a:
Option 1: Use pg_dump + restore (for structure only)
Step-by-Step:
Dump the schema structure only (no data):
pg_dump -U your_user -d your_db --schema=tenant_a --schema-only >
tenant_a_schema.sql
Edit the SQL file:
Replace all tenant_a. with tenant_b. (schema name)
Run the script:
psql -U your_user -d your_db -f tenant_b_schema.sql
PostgreSQL: Relational Database
Option 2: Create schema manually and clone tables one by one
CREATE SCHEMA tenant_b;
CREATE TABLE tenant_b.users (LIKE tenant_a.users INCLUDING ALL);
CREATE TABLE tenant_b.orders (LIKE tenant_a.orders INCLUDING ALL);
Option 3: Use a function to automate cloning
You can write a function or script that:
Lists all tables in tenant_a
Creates tenant_b if not exists
Loops through tables and issues:
CREATE TABLE tenant_b.table_name (LIKE tenant_a.table_name
INCLUDING ALL)
PostgreSQL: Relational Database
1. Data Definition Language (DDL)
These statements define and modify the structure of the database
(schemas, tables, indexes, etc.).
Statements: CREATE, ALTER, DROP, TRUNCATE, RENAME,
2. Data Manipulation Language (DML)
These statements work with the data itself (insert, update, delete).
3. Data Control Language (DCL)
These statements control access to data and objects.
GRANT Give privileges to users/roles
REVOKE Remove privileges
4. Transaction Control Language (TCL)
These statements manage database transactions, which group multiple
operations into a single, atomic unit.
BEGIN / START TRANSACTION, COMMIT, ROLLBACK,
SAVEPPOINT (restore point inside a transaction), RELEASE SAVEPOINT
Thank You!