When working with a distributed SQL database like YugabyteDB, understanding object dependencies becomes essential—especially as your schema becomes more complex and layered. Whether you’re cleaning up unused objects, debugging permission issues, or performing migrations, being able to trace how objects relate to each other is a critical skill.
PostgreSQL—and by extension, YugabyteDB—tracks these relationships through system catalogs such as pg_depend and pg_shdepend.
In today’s tip, we’ll explore how to use these catalog tables to identify dependencies between objects, how they differ, and some practical examples to illustrate their usage.
Dependency Tracking in YugabyteDB
YugabyteDB, built on a PostgreSQL-compatible API layer, inherits PostgreSQL’s system catalogs. The two primary catalogs that store dependency information are:
pg_depend: Tracks dependencies between database objects within the same database.pg_shdepend: Tracks shared object dependencies that span across databases—most notably, dependencies on roles or tablespaces.
Let’s dive into each.
The pg_depend catalog is used to track dependencies among objects within the same database. These include things like:
A view depending on a table
A column default expression depending on a function
An index depending on a table
Example:
yugabyte=# CREATE TABLE orders (
yugabyte(# id SERIAL PRIMARY KEY,
yugabyte(# customer_id INT,
yugabyte(# amount NUMERIC
yugabyte(# );
CREATE TABLE
yugabyte=# SELECT d.objid::regclass, d.deptype, c.relname AS dependent_object
yugabyte-# FROM pg_depend d
yugabyte-# JOIN pg_class c ON d.objid = c.oid
yugabyte-# WHERE d.refobjid = 'orders'::regclass;
objid | deptype | dependent_object
---------------+---------+------------------
orders_id_seq | a | orders_id_seq
(1 row)
On the other hand, some objects in PostgreSQL/YugabyteDB are “shared” across all databases—like roles and tablespaces. Dependencies on these are stored in pg_shdepend.
Example:
yugabyte=# CREATE ROLE mary;
CREATE ROLE
yugabyte=# ALTER TABLE orders OWNER TO mary;
ALTER TABLE
yugabyte=# SELECT r.rolname, c.relname AS object_name, c.relkind
yugabyte-# FROM pg_shdepend sd
yugabyte-# JOIN pg_roles r ON sd.refobjid = r.oid
yugabyte-# JOIN pg_class c ON sd.objid = c.oid
yugabyte-# WHERE r.rolname = 'mary';
rolname | object_name | relkind
--------+---------------+---------
mary | orders_id_seq | S
mary | orders | r
(2 rows)
This shows all objects in the cluster that depend on the mary role—useful when cleaning up a role before dropping it.
While YugabyteDB abstracts away many of the complexities of distributed databases, understanding its PostgreSQL-compatible system catalogs like pg_depend and pg_shdepend gives you superpowers for introspection and management. Whether you’re debugging role ownership, planning a schema change, or just curious about how objects connect, these catalogs provide the map!
Have Fun!
