Postgres 19 graph features

Postgres 19 adds graph features. This allow defining a graph representation of the existing relations. This does not change how data is stored, it’s more like view. This is pretty neat, because it helps to hide the way the data is linked together.

Let’s start with sample employee/department database schema and then add some test data there.

erDiagram
    EMPLOYEE {
        integer id PK
        varchar name UK
        varchar title
    }
    DEPARTMENT {
        integer id PK
        varchar name UK
    }
    EMPLOYMENT {
        integer id PK
        integer employee_id FK, UK
        integer department_id FK
    }
    REPORTING_LINE {
        integer id PK
        integer employee_id FK, UK
        integer manager_id FK
    }

    EMPLOYEE ||--o| EMPLOYMENT : has
    DEPARTMENT ||--o{ EMPLOYMENT : includes
    EMPLOYEE ||--o| REPORTING_LINE : reports
    EMPLOYEE ||--o{ REPORTING_LINE : manages

DROP PROPERTY GRAPH IF EXISTS organization_graph;
DROP TABLE IF EXISTS reporting_line, employment, employee, department;

CREATE TABLE department (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL UNIQUE
);

CREATE TABLE employee (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL UNIQUE,
    title varchar(100) NOT NULL
);

CREATE TABLE employment (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id integer NOT NULL UNIQUE REFERENCES employee (id),
    department_id integer NOT NULL REFERENCES department (id)
);

CREATE TABLE reporting_line (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id integer NOT NULL UNIQUE REFERENCES employee (id),
    manager_id integer NOT NULL REFERENCES employee (id),
    CHECK (employee_id <> manager_id)
);

Insert some test data

INSERT INTO department (name) VALUES
    ('Executive'),
    ('Engineering'),
    ('Product');

INSERT INTO employee (name, title) VALUES
    ('Ada Lovelace', 'CEO'),
    ('Grace Hopper', 'VP of Engineering'),
    ('Linus Torvalds', 'Engineering Manager'),
    ('Margaret Hamilton', 'Staff Engineer'),
    ('Edsger Dijkstra', 'Software Engineer'),
    ('Radia Perlman', 'Network Engineer'),
    ('Barbara Liskov', 'Product Director'),
    ('Alan Turing', 'Product Engineer');

INSERT INTO employment (employee_id, department_id)
SELECT employee.id, department.id
FROM (VALUES
    ('Ada Lovelace', 'Executive'),
    ('Grace Hopper', 'Executive'),
    ('Linus Torvalds', 'Engineering'),
    ('Margaret Hamilton', 'Engineering'),
    ('Edsger Dijkstra', 'Engineering'),
    ('Radia Perlman', 'Engineering'),
    ('Barbara Liskov', 'Product'),
    ('Alan Turing', 'Product')
) AS assignment(employee_name, department_name)
JOIN employee ON employee.name = assignment.employee_name
JOIN department ON department.name = assignment.department_name;

INSERT INTO reporting_line (employee_id, manager_id)
SELECT report.id, manager.id
FROM (VALUES
    ('Grace Hopper', 'Ada Lovelace'),
    ('Linus Torvalds', 'Grace Hopper'),
    ('Margaret Hamilton', 'Linus Torvalds'),
    ('Edsger Dijkstra', 'Linus Torvalds'),
    ('Radia Perlman', 'Linus Torvalds'),
    ('Barbara Liskov', 'Ada Lovelace'),
    ('Alan Turing', 'Barbara Liskov')
) AS hierarchy(employee_name, manager_name)
JOIN employee AS report ON report.name = hierarchy.employee_name
JOIN employee AS manager ON manager.name = hierarchy.manager_name;

Inserted data

Employee	Title	Department	Manager
Ada Lovelace	CEO	Executive	None
Grace Hopper	VP of Engineering	Executive	Ada Lovelace
Linus Torvalds	Engineering Manager	Engineering	Grace Hopper
Margaret Hamilton	Staff Engineer	Engineering	Linus Torvalds
Edsger Dijkstra	Software Engineer	Engineering	Linus Torvalds
Radia Perlman	Network Engineer	Engineering	Linus Torvalds
Barbara Liskov	Product Director	Product	Ada Lovelace
Alan Turing	Product Engineer	Product	Barbara Liskov

Create the graph overlay on top of existing tables

CREATE PROPERTY GRAPH organization_graph
    VERTEX TABLES (
        employee LABEL employee PROPERTIES (name, title),
        department LABEL department PROPERTIES (name)
    )
    EDGE TABLES (
        reporting_line
            SOURCE KEY (employee_id) REFERENCES employee (id)
            DESTINATION KEY (manager_id) REFERENCES employee (id)
            LABEL reports_to NO PROPERTIES,
        employment
            SOURCE KEY (employee_id) REFERENCES employee (id)
            DESTINATION KEY (department_id) REFERENCES department (id)
            LABEL works_in NO PROPERTIES
    );

flowchart LR
    employee["employee vertex:<br> name, title"]
    department["department vertex:<br> name"]

    employee -->|reports_to| employee
    employee -->|works_in| department

Traditional query with joins

SELECT
    report.name AS employee,
    report.title,
    manager.name AS manager,
    manager_department.name AS manager_department
FROM employee AS report
JOIN reporting_line
    ON reporting_line.employee_id = report.id
JOIN employee AS manager
    ON manager.id = reporting_line.manager_id
JOIN employment AS manager_employment
    ON manager_employment.employee_id = manager.id
JOIN department AS manager_department
    ON manager_department.id = manager_employment.department_id
WHERE manager_department.name = 'Engineering'
ORDER BY report.name;

Employee	Title	Manager	Manager department
Edsger Dijkstra	Software Engineer	Linus Torvalds	Engineering
Margaret Hamilton	Staff Engineer	Linus Torvalds	Engineering
Radia Perlman	Network Engineer	Linus Torvalds	Engineering

Then same query using graph

(employee) -[reports_to]-> (manager) -[works_in]-> (Engineering)

SELECT employee, title, manager, manager_department
FROM GRAPH_TABLE (
    organization_graph
    MATCH
        (report IS employee)
        -[IS reports_to]->
        (manager IS employee)
        -[IS works_in]->
        (department IS department WHERE department.name = 'Engineering')
    COLUMNS (
        report.name AS employee,
        report.title AS title,
        manager.name AS manager,
        department.name AS manager_department
    )
)
ORDER BY employee;

Employee	Title	Manager	Manager department
Edsger Dijkstra	Software Engineer	Linus Torvalds	Engineering
Margaret Hamilton	Staff Engineer	Linus Torvalds	Engineering
Radia Perlman	Network Engineer	Linus Torvalds	Engineering