psql \dh: List High-Level (Root) Tables and Indexes - Mailing list pgsql-hackers

Dear hackers,

Please find attached the patch to add a new metacommand (\dh) to psql.
I find the functionality very useful for my day-to-day job, and decided
to add it to psql source code. Summary of the command, justification
for adding the functionality, as well as design choices are listed
below.

Best Regards,
Sadeq Dousti

Tech Lead and Staff Software Engineer at Trade Republic Bank GmbH

================

Summary
------------
The \dh command provides a concise overview of high-level (root)
relations, including tables and indexes. This helps DBAs and developers
quickly identify the core structure of a database, particularly in
environments with extensive partitioning. It offers two variants: \dht
for only showing root tables, and \dhi for only showing root indexes.
By adding the + option, additional details such as the total sizes of
the relations are also displayed.


Reference
------------
Commit 1c5d927 (psql \dP metacommand) is used as the basis for this
patch.

Justification
------------
In databases with partitioned tables, standard listing commands such as
\dt can produce long and cluttered outputs due to the large number of
partitions. The \dh command filters the output to show only root tables
(both regular and partitioned) and high-level indexes, improving
readability and usability.

This enhancement allows users to:

* Quickly scan the primary structure of a database without being
  overwhelmed by partitions.
* View table ownership and optionally include size information.
* Identify which indexes belong to which tables, along with
  descriptions.


Comparison Example
------------
Using \dt in a partitioned database results in an extensive list that
includes all partitions:


postgres=# \dt
              List of tables
 Schema | Name |       Type        | Owner
--------+------+-------------------+-------
 public | t    | table             | sadeq
 public | z    | partitioned table | sadeq
 public | z_1  | table             | sadeq
 public | z_2  | table             | sadeq
 public | z_3  | table             | sadeq
 public | z_4  | table             | sadeq
 public | z_5  | table             | sadeq
 public | z_6  | table             | sadeq
 public | z_7  | table             | sadeq
 public | z_8  | table             | sadeq
 public | z_9  | table             | sadeq
 public | z_10 | table             | sadeq
 public | z_11 | table             | sadeq
 public | z_12 | table             | sadeq
 public | z_13 | table             | sadeq
 public | z_14 | table             | sadeq
 public | z_15 | table             | sadeq


The \dh command simplifies this view by displaying only the root tables:

postgres=# \dht
  List of root tables
 Schema | Name | Owner
--------+------+-------
 public | t    | sadeq
 public | z    | sadeq


Design Decision: Introduction of the New Command \dh
------------
In the development of the \dh command to list high-level (root) tables
and indexes, it was initially considered to extend the existing \dP
command to support this functionality. However, after evaluating the
existing codebase, it became clear that adding this feature to \dP
would introduce significant complexity and reduce code readability.
Specifically, the following points led to the decision to implement a
new command instead of extending \dP or \d variant:

1. Functionality Mismatch: The \dP command is designed to display
   partitioned tables. Modifying it to also show non-partitioned table
   can potentially confuse the user.


2. Additional Branching and Readability: Introducing additional
   functionality to support high-level (root) relations would require a
   substantial amount of branching logic, making the code more
   difficult to follow and potentially error-prone.

3. Consistency with Existing \d Variants: Other \d variants exhibit a
   high degree of functional separation, with each command serving a
   unique role related to a specific type of database object. The logic
   for \dh is closer to \dP than any other \d variant, and it's already
   ruled out for the sake of readability.

Given these factors, the decision was made to introduce a new command,
\dh, to provide a concise overview of root tables and indexes. This
approach ensures clarity, avoids unnecessary complexity, and maintains
consistency with the overall design philosophy of psql metacommands.


Further Details
------------
* Patch is against the master branch
* Code compiles successfully. New functionality is tested manually with
  success
* Regression tests are added, and all existing and new tests pass
* Documentation (psql-ref.sgml) is updated with details for \dh