PostgreSQL Advanced Features Comprehensive Practice

1. What You'll Learn

Use window functions for attendance statistics and ranking
Use views and materialized views to encapsulate complex queries
Design indexing strategies to tune query performance
Use JSONB to store flexible employee skill tags
Integrate full-text search for employee lookup
Complete a full employee-management-system database project

2. The Story

Alice is a database engineer at a SaaS company. The HR department needs her to build the database for an employee management system. The requirements are:

Attendance statistics: monthly attendance days, late counts, and overtime hours per employee, ranked with window functions
Performance ranking: a composite score from multi-dimensional ratings, cached in a materialized view
Skill tags: each employee's skill tags are not fixed, so store them flexibly in JSONB
Employee search: quickly search employees by name, skill, department, etc., using full-text search

Alice needs to combine window functions, views, indexes, JSONB, and full-text search to deliver this project.

3. Concept: Project Database Design

(1) ER Diagram

erDiagram
    EMPLOYEES ||--o{ ATTENDANCE : has
    EMPLOYEES ||--o{ PERFORMANCE : receives
    EMPLOYEES }o--|| DEPARTMENTS : belongs_to

    EMPLOYEES {
        int employee_id PK
        text name
        int department_id FK
        text position
        date hire_date
        jsonb skills
        tsvector search_doc
    }

    DEPARTMENTS {
        int department_id PK
        text dept_name
        text location
    }

    ATTENDANCE {
        int attendance_id PK
        int employee_id FK
        date attend_date
        text status
        time check_in_time
        time check_out_time
        numeric overtime_hours
    }

    PERFORMANCE {
        int perf_id PK
        int employee_id FK
        int year
        int quarter
        numeric technical_score
        numeric communication_score
        numeric leadership_score
        numeric overall_score
    }

▶ Example: Creating the Base Table Structure

SQL

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  dept_name TEXT NOT NULL,
  location TEXT NOT NULL
);

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT NOT NULL REFERENCES departments(department_id),
  position TEXT NOT NULL,
  hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
  skills JSONB NOT NULL DEFAULT '[]',
  search_doc tsvector
);

CREATE TABLE attendance (
  attendance_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  attend_date DATE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('present','late','absent','leave')),
  check_in_time TIME,
  check_out_time TIME,
  overtime_hours NUMERIC(4,2) DEFAULT 0
);

CREATE TABLE performance (
  perf_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  year INT NOT NULL,
  quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  technical_score NUMERIC(5,2) CHECK (technical_score BETWEEN 0 AND 100),
  communication_score NUMERIC(5,2) CHECK (communication_score BETWEEN 0 AND 100),
  leadership_score NUMERIC(5,2) CHECK (leadership_score BETWEEN 0 AND 100),
  overall_score NUMERIC(5,2) CHECK (overall_score BETWEEN 0 AND 100),
  UNIQUE (employee_id, year, quarter)
);

TEXT

CREATE TABLE

(2) Insert Test Data

▶ Example: Inserting Departments and Employees

SQL

INSERT INTO departments (dept_name, location) VALUES
  ('Engineering', 'Floor 3'),
  ('Sales', 'Floor 2'),
  ('Marketing', 'Floor 1'),
  ('HR', 'Floor 1');

INSERT INTO employees (name, department_id, position, hire_date, skills) VALUES
  ('Alice Chen', 1, 'Senior Engineer', '2022-03-15',
   '["PostgreSQL","Python","Docker","Kubernetes"]'),
  ('Bob Wang', 1, 'Junior Engineer', '2023-06-01',
   '["Java","Spring","MySQL"]'),
  ('Charlie Zhang', 2, 'Sales Manager', '2021-01-10',
   '["Negotiation","CRM","English","French"]'),
  ('Diana Liu', 3, 'Marketing Specialist', '2023-09-20',
   '["SEO","Content Writing","Google Analytics"]'),
  ('Edward Wu', 1, 'DevOps Engineer', '2022-11-01',
   '["Docker","Kubernetes","AWS","Terraform"]'),
  ('Fiona Li', 4, 'HR Manager', '2020-05-15',
   '["Recruiting","Employee Relations","Payroll"]');

TEXT

INSERT 0 1

▶ Example: Inserting Attendance Data

SQL

INSERT INTO attendance (employee_id, attend_date, status, check_in_time, check_out_time, overtime_hours)
SELECT
  e.employee_id,
  d.dt::date,
  CASE WHEN random() < 0.05 THEN 'absent'
       WHEN random() < 0.12 THEN 'late'
       ELSE 'present'
  END,
  CASE WHEN random() < 0.12 THEN '09:15' ELSE '08:55' END,
  CASE WHEN random() < 0.15 THEN '19:00' ELSE '18:00' END,
  CASE WHEN random() < 0.15 THEN 1.0 ELSE 0 END
FROM employees e
CROSS JOIN (
  SELECT generate_series('2025-01-01'::timestamp, '2025-03-31'::timestamp, '1 day') AS dt
) d
WHERE EXTRACT(dow FROM d.dt) NOT IN (0, 6);

TEXT

INSERT 0 1

▶ Example: Inserting Performance Data

SQL

INSERT INTO performance (employee_id, year, quarter, technical_score, communication_score, leadership_score, overall_score)
VALUES
  (1, 2025, 1, 92, 85, 88, 89.0),
  (2, 2025, 1, 78, 72, 65, 72.3),
  (3, 2025, 1, 70, 90, 85, 82.0),
  (4, 2025, 1, 75, 88, 72, 78.3),
  (5, 2025, 1, 88, 76, 80, 82.0),
  (6, 2025, 1, 65, 92, 90, 82.3);

TEXT

INSERT 0 1

4. Concept: Window Functions — Attendance Statistics and Ranking

(1) Monthly Attendance Summary

▶ Example: Monthly Attendance per Employee

SQL

SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
ORDER BY e.employee_id, month;

TEXT

 count 
-------
     5
(1 row)

Function	Description
`COUNT(*) FILTER (WHERE ...)`	Conditional count (PostgreSQL-specific, clearer than SUM(CASE))
`TO_CHAR(date, 'YYYY-MM')`	Group by month

(2) In-Department Attendance Ranking

▶ Example: Ranking within a Department with Window Functions

SQL

SELECT
  e.name,
  d.dept_name,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  SUM(a.overtime_hours) AS total_overtime,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC) AS attendance_rank,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(a.overtime_hours) DESC) AS overtime_rank
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
WHERE a.attend_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY e.employee_id, e.name, d.dept_name
ORDER BY d.dept_name, attendance_rank;

TEXT

 count 
-------
     5
(1 row)

Window function	Equal-value handling	Use case
`RANK()`	Ties skip ranks (1,1,3)	Ranking that allows ties
`DENSE_RANK()`	Ties don't skip ranks (1,1,2)	Continuous ranking
`ROW_NUMBER()`	Never ties (1,2,3)	Unique ranking

▶ Example: Cumulative Attendance Days (Running Total)

SQL

SELECT
  e.name,
  a.attend_date,
  a.status,
  SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END)
    OVER (PARTITION BY e.employee_id ORDER BY a.attend_date) AS cumulative_present
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE e.employee_id = 1
ORDER BY a.attend_date;

TEXT

  result  
----------
   42.50
(1 row)

▶ Example: This Month vs Last Month (LAG)

SQL

WITH monthly_stats AS (
  SELECT
    e.employee_id,
    e.name,
    TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  GROUP BY e.employee_id, e.name, TO_CHAR(a.attend_date, 'YYYY-MM')
)
SELECT
  name,
  month,
  present_days,
  LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS prev_month,
  present_days - LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS diff
FROM monthly_stats
ORDER BY employee_id, month;

TEXT

 count 
-------
     5
(1 row)

5. Concept: Views and Materialized Views — Encapsulating Performance Ranking

(1) View vs Materialized View

Dimension	View	Materialized View
Stores data	No; computed live on every query	Yes; reads the stored result directly
Data freshness	Real-time	Requires manual REFRESH
Query performance	Same as the underlying query	Fast (pre-computed)
Use case	Frequently updated data	Reports/statistics, data that rarely changes

▶ Example: Creating the Performance Ranking View

SQL

CREATE VIEW v_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  p.year,
  p.quarter,
  p.technical_score,
  p.communication_score,
  p.leadership_score,
  p.overall_score,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY p.overall_score DESC) AS dept_rank,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM v_performance_ranking WHERE quarter = 1 ORDER BY company_rank;

TEXT

CREATE TABLE

▶ Example: Creating the Performance Ranking Materialized View

SQL

CREATE MATERIALIZED VIEW mv_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  p.year,
  p.quarter,
  p.overall_score,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id
WITH DATA;

-- Refresh when performance data changes
REFRESH MATERIALIZED VIEW mv_performance_ranking;

-- Concurrent refresh (does not block reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

TEXT

CREATE TABLE

REFRESH method	Lock	Description
`REFRESH MATERIALIZED VIEW`	ACCESS EXCLUSIVE	Blocks reads and writes, but needs no unique index
`REFRESH ... CONCURRENTLY`	SHARE	Does not block reads, requires a unique index

▶ Example: Attendance Summary Materialized View

SQL

CREATE MATERIALIZED VIEW mv_attendance_summary AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
WITH DATA;

TEXT

 count 
-------
     5
(1 row)

6. Concept: Indexing Strategy Tuning

(1) Index Design Principles

Principle	Description
Index columns used in high-frequency WHERE conditions	The most basic use case
Index JOIN columns	Foreign-key columns have no index by default; create them manually
Avoid over-indexing	Every index adds write overhead
Watch composite index column order	Equality-condition columns first, range-condition columns last
Verify with EXPLAIN ANALYZE	The actual execution plan is the final arbiter

▶ Example: Creating Indexes for Core Queries

SQL

-- Index for attendance queries by employee and date range
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);

-- Index for performance queries by year/quarter
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);

-- Index for employees by department
CREATE INDEX idx_employees_dept
ON employees (department_id);

-- Partial index: only index non-absent attendance records
CREATE INDEX idx_attendance_present
ON attendance (employee_id, attend_date)
WHERE status != 'absent';

TEXT

CREATE TABLE

Index type	Syntax	Use case
B-tree (default)	`CREATE INDEX idx ON t(col)`	Equality, range, sorting
Composite index	`CREATE INDEX idx ON t(col1, col2)`	Multi-column combined queries
Partial index	`CREATE INDEX idx ON t(col) WHERE ...`	Index only rows that meet a condition
Expression index	`CREATE INDEX idx ON t(lower(col))`	Queries on function results

▶ Example: Verifying Index Effect with EXPLAIN ANALYZE

SQL

-- Before index: Seq Scan
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

-- After index: Index Scan
CREATE INDEX idx_attendance_emp_date ON attendance (employee_id, attend_date);
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT

CREATE TABLE

▶ Example: Covering Index to Avoid Table Access

SQL

-- Include columns to avoid table access
CREATE INDEX idx_attendance_covering
ON attendance (employee_id, attend_date)
INCLUDE (status, overtime_hours);

-- This query only needs index, no table access
EXPLAIN ANALYZE
SELECT status, overtime_hours
FROM attendance
WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT

CREATE TABLE

7. Concept: JSONB for Storing Skill Tags

(1) JSONB Design for Skill Tags

▶ Example: Querying Employee Skills

SQL

-- Query specific skill
SELECT name, skills
FROM employees
WHERE skills @> '["PostgreSQL"]';

-- Query multiple skills (has ANY of these)
SELECT name, skills
FROM employees
WHERE skills ?| array['PostgreSQL', 'Docker'];

-- Query multiple skills (has ALL of these)
SELECT name, skills
FROM employees
WHERE skills @> '["Docker", "Kubernetes"]';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Expanding Skills into Rows

SQL

SELECT
  e.name,
  jsonb_array_elements_text(e.skills) AS skill
FROM employees e
ORDER BY e.name;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Counting Employees per Skill

SQL

SELECT
  skill,
  COUNT(*) AS employee_count
FROM (
  SELECT jsonb_array_elements_text(skills) AS skill FROM employees
) sub
GROUP BY skill
ORDER BY employee_count DESC;

TEXT

 count 
-------
     5
(1 row)

(2) JSONB Index to Speed Up Skill Queries

▶ Example: Creating a GIN Index

SQL

CREATE INDEX idx_employees_skills ON employees USING gin (skills);

-- GIN index supports @> and ?| queries
EXPLAIN ANALYZE
SELECT name FROM employees WHERE skills @> '["PostgreSQL"]';

TEXT

CREATE TABLE

▶ Example: Upgrading Skill Tags to Structured JSONB

SQL

-- Upgrade from simple array to structured skills
ALTER TABLE employees ADD COLUMN skill_profile JSONB DEFAULT '{}';

UPDATE employees SET skill_profile = '{
  "primary": ["PostgreSQL", "Python"],
  "secondary": ["Docker"],
  "certifications": ["AWS Solutions Architect"],
  "years_of_experience": {"PostgreSQL": 5, "Python": 8}
}'::jsonb
WHERE employee_id = 1;

-- Query by primary skill
SELECT name, skill_profile -> 'primary' AS primary_skills
FROM employees
WHERE skill_profile -> 'primary' @> '["PostgreSQL"]';

-- Query by certification
SELECT name
FROM employees
WHERE skill_profile -> 'certifications' @> '["AWS Solutions Architect"]';

-- Query by years of experience
SELECT name
FROM employees
WHERE (skill_profile -> 'years_of_experience' ->> 'PostgreSQL')::int >= 3;

TEXT

UPDATE 3

8. Concept: Full-Text Search for Employee Lookup

(1) Building the tsvector Search Document

▶ Example: Creating the Search-Document Column and Trigger

SQL

-- Add search document column
ALTER TABLE employees ADD COLUMN search_doc tsvector;

-- Create function to build search document
CREATE FUNCTION employees_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_doc :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.position, '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(
      array_to_string(
        ARRAY(SELECT jsonb_array_elements_text(NEW.skills)), ' '
      ), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trg_employees_search
BEFORE INSERT OR UPDATE OF name, position, skills ON employees
FOR EACH ROW EXECUTE FUNCTION employees_search_update();

-- Update existing rows
UPDATE employees SET name = name; -- Trigger fires, populates search_doc

TEXT

INSERT 0 1

▶ Example: Creating the GIN Index

SQL

CREATE INDEX idx_employees_search ON employees USING gin (search_doc);

TEXT

CREATE TABLE

(2) The Search Function

▶ Example: The Employee Search Function

SQL

CREATE FUNCTION search_employees(p_query TEXT)
RETURNS TABLE (
  employee_id INT,
  name TEXT,
  position TEXT,
  dept_name TEXT,
  skills JSONB,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    e.position,
    d.dept_name,
    e.skills,
    ts_rank(e.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(e.name, '') || ' ' || COALESCE(e.position, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>'
    ) AS headline
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;

-- Test: search for engineer
SELECT * FROM search_employees('engineer');

-- Test: search for Docker skills
SELECT * FROM search_employees('Docker');

TEXT

CREATE TABLE

9. Concept: Comprehensive Query in Action

(1) Multi-Dimensional Employee Report

▶ Example: Employee Comprehensive Information Report

SQL

CREATE VIEW v_employee_dashboard AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  e.hire_date,
  EXTRACT(YEAR FROM age(CURRENT_DATE, e.hire_date)) AS years_of_service,
  e.skills,
  p.overall_score AS latest_perf_score,
  p_r.company_rank,
  a_s.present_days AS q1_present,
  a_s.late_days AS q1_late,
  a_s.total_overtime AS q1_overtime
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN LATERAL (
  SELECT overall_score FROM performance
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p ON true
LEFT JOIN LATERAL (
  SELECT company_rank FROM mv_performance_ranking
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p_r ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE status = 'late') AS late_days,
    SUM(overtime_hours) AS total_overtime
  FROM attendance
  WHERE employee_id = e.employee_id
    AND attend_date BETWEEN '2025-01-01' AND '2025-03-31'
) a_s ON true;

TEXT

 count 
-------
     5
(1 row)

▶ Example: Department Performance Comparison

SQL

SELECT
  d.dept_name,
  AVG(p.overall_score) AS avg_score,
  MAX(p.overall_score) AS max_score,
  MIN(p.overall_score) AS min_score,
  COUNT(*) AS employee_count,
  RANK() OVER (ORDER BY AVG(p.overall_score) DESC) AS dept_rank
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
WHERE p.year = 2025 AND p.quarter = 1
GROUP BY d.dept_name
ORDER BY avg_score DESC;

TEXT

 count 
-------
     5
(1 row)

(2) Skill-Gap Analysis

▶ Example: Skill Distribution by Department

SQL

SELECT
  d.dept_name,
  skill,
  COUNT(*) AS employees_with_skill,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER (PARTITION BY d.dept_name) * 100, 1) AS pct
FROM employees e
JOIN departments d ON e.department_id = d.department_id
CROSS JOIN LATERAL jsonb_array_elements_text(e.skills) AS skill
GROUP BY d.dept_name, skill
ORDER BY d.dept_name, employees_with_skill DESC;

TEXT

 count 
-------
     5
(1 row)

10. In Practice: A Complete Employee-Management-System Database

Alice integrates all the modules into one complete project.

SQL

-- ============================================
-- Employee Management System - Complete Schema
-- ============================================

-- Step 1: Tables (already created above)
-- Ensure all tables, indexes, triggers exist

-- Step 2: Core indexes
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);
CREATE INDEX idx_employees_dept
ON employees (department_id);
CREATE INDEX idx_employees_skills
ON employees USING gin (skills);
CREATE INDEX idx_employees_search
ON employees USING gin (search_doc);

-- Step 3: Materialized views
CREATE MATERIALIZED VIEW mv_dept_performance AS
SELECT
  d.dept_name,
  p.year,
  p.quarter,
  AVG(p.overall_score) AS avg_score,
  AVG(p.technical_score) AS avg_tech,
  AVG(p.communication_score) AS avg_comm,
  AVG(p.leadership_score) AS avg_lead,
  COUNT(*) AS headcount
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
GROUP BY d.dept_name, p.year, p.quarter
WITH DATA;

CREATE UNIQUE INDEX idx_mv_dept_perf
ON mv_dept_performance (dept_name, year, quarter);

-- Step 4: Attendance report function
CREATE FUNCTION get_attendance_report(
  p_year INT, p_month INT
) RETURNS TABLE (
  employee_id INT, name TEXT, dept_name TEXT,
  present_days BIGINT, late_days BIGINT,
  absent_days BIGINT, overtime_hours NUMERIC,
  attendance_rate NUMERIC, dept_rank BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    d.dept_name,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
    COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
    COALESCE(SUM(a.overtime_hours), 0) AS overtime_hours,
    ROUND(
      COUNT(*) FILTER (WHERE a.status IN ('present','late'))::numeric
      / NULLIF(COUNT(*), 0) * 100, 1
    ) AS attendance_rate,
    RANK() OVER (PARTITION BY d.dept_name
      ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC)
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  JOIN departments d ON e.department_id = d.department_id
  WHERE EXTRACT(YEAR FROM a.attend_date) = p_year
    AND EXTRACT(MONTH FROM a.attend_date) = p_month
  GROUP BY e.employee_id, e.name, d.dept_name
  ORDER BY d.dept_name, dept_rank;
END;
$$ LANGUAGE plpgsql;

-- Step 5: Employee search (already created above)

-- Step 6: Test the complete system
-- Attendance report for January 2025
SELECT * FROM get_attendance_report(2025, 1);

-- Department performance comparison
SELECT * FROM mv_dept_performance
WHERE year = 2025 AND quarter = 1
ORDER BY avg_score DESC;

-- Find employees with Docker AND Kubernetes skills
SELECT name, position, skills
FROM employees
WHERE skills @> '["Docker","Kubernetes"]';

-- Full-text search for "engineer"
SELECT name, position, rank
FROM search_employees('engineer');

-- Refresh materialized view when data changes
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

❓ FAQ

Q When should I use REFRESH CONCURRENTLY on a materialized view?

A Use CONCURRENTLY when the materialized view is queried frequently and read-blocking is unacceptable. It requires at least one UNIQUE index on the materialized view. If a brief unavailability is acceptable, a plain REFRESH is faster.

Q Which performs better, the FILTER syntax or CASE WHEN?

A Performance is essentially the same—PostgreSQL internally optimizes FILTER into an execution plan equivalent to CASE WHEN. The FILTER syntax is cleaner and more readable, so it's recommended.

Q What's the difference between a LATERAL JOIN and a subquery?

A LATERAL lets a subquery reference columns from the outer query, like a correlated subquery but it can return multiple rows. A normal subquery cannot reference outer columns. LATERAL fits the scenario "compute one result per outer row."

Q JSONB skill array vs a linking table—which is better?

A If skills need to be managed independently (add/remove/modify, statistics, relational queries), a linking table (employee_skills) is better. If skills are just tag-like attributes with simple query patterns, JSONB is more flexible. In this project skills are tag-like, so JSONB fits better.

Q Why use the english config instead of simple when searching for the Docker skill?

A The english config stems the query words, but "Docker" is a proper noun so stemming has no effect. The simple config would also work, but if the search term contains common English words (e.g., "running"), the english config's stemming works better. This project uses the english config uniformly.

Q How do I refresh a materialized view automatically on a schedule?

A PostgreSQL has no built-in auto-refresh. You can use the pg_cron extension to run REFRESH on a schedule, or use the OS cron/Task Scheduler to invoke a psql command. For example, refresh daily: SELECT cron.schedule('0 2 * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance$$).

Q What's the rule for composite index column order?

A Equality-query columns go first, range-query columns go last. For example, (employee_id, attend_date): first filter by employee_id with equality, then range-scan by attend_date. Wrong order may prevent the index from being used.

📖 Summary

Window functions power attendance statistics, ranking, year-over-year comparison (LAG/LEAD), and running totals
Views encapsulate complex queries; materialized views cache results; CONCURRENTLY refresh doesn't block reads
Indexing strategy: index high-frequency WHERE/JOIN columns, watch composite index column order, partial indexes cut maintenance overhead
JSONB stores flexible skill tags; a GIN index speeds up containment queries; @> is the most common query
Full-text search uses setweight to weight fields and triggers to auto-sync the tsvector
The comprehensive project fuses multiple features: window functions + materialized views + JSONB + full-text search + index tuning

📝 Exercises

⭐ Use window functions to query each employee's Q1 attendance days, their company-wide attendance rank (DENSE_RANK), and how many fewer days they have than the employee ranked just above them (using the LAG function).
⭐⭐ Create a materialized view mv_skill_gap that lists the skills each department lacks (skills present in other departments but missing from that department). Create a UNIQUE index, refresh it with CONCURRENTLY, and use EXPLAIN ANALYZE to compare query performance before and after the refresh.
⭐⭐⭐ Design a complete solution for the employee management system: create a training_records table (employee_id, course_name, completion_date, score, tags JSONB), and write a function recommend_training(p_employee_id INT) that recommends missing training courses based on the employee's current skills (employees.skills) and existing training (training_records.tags). Use full-text search to match course descriptions, returning the recommendations ordered by relevance.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Advanced Features Comprehensive Practice

1. What You'll Learn

2. The Story

3. Concept: Project Database Design

(1) ER Diagram

▶ Example: Creating the Base Table Structure

(2) Insert Test Data

▶ Example: Inserting Departments and Employees

▶ Example: Inserting Attendance Data

▶ Example: Inserting Performance Data

4. Concept: Window Functions — Attendance Statistics and Ranking

(1) Monthly Attendance Summary

▶ Example: Monthly Attendance per Employee

(2) In-Department Attendance Ranking

▶ Example: Ranking within a Department with Window Functions

▶ Example: Cumulative Attendance Days (Running Total)

▶ Example: This Month vs Last Month (LAG)

5. Concept: Views and Materialized Views — Encapsulating Performance Ranking

(1) View vs Materialized View

▶ Example: Creating the Performance Ranking View

▶ Example: Creating the Performance Ranking Materialized View

▶ Example: Attendance Summary Materialized View

6. Concept: Indexing Strategy Tuning

(1) Index Design Principles

▶ Example: Creating Indexes for Core Queries

▶ Example: Verifying Index Effect with EXPLAIN ANALYZE

▶ Example: Covering Index to Avoid Table Access

7. Concept: JSONB for Storing Skill Tags

(1) JSONB Design for Skill Tags

▶ Example: Querying Employee Skills

▶ Example: Expanding Skills into Rows

▶ Example: Counting Employees per Skill

(2) JSONB Index to Speed Up Skill Queries

▶ Example: Creating a GIN Index

▶ Example: Upgrading Skill Tags to Structured JSONB

8. Concept: Full-Text Search for Employee Lookup

(1) Building the tsvector Search Document

▶ Example: Creating the Search-Document Column and Trigger

▶ Example: Creating the GIN Index

(2) The Search Function

▶ Example: The Employee Search Function

9. Concept: Comprehensive Query in Action

(1) Multi-Dimensional Employee Report

▶ Example: Employee Comprehensive Information Report

▶ Example: Department Performance Comparison

(2) Skill-Gap Analysis

▶ Example: Skill Distribution by Department

10. In Practice: A Complete Employee-Management-System Database

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好