Thread: psql \dh: List High-Level (Root) Tables and Indexes
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
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
------------
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
Attachment
Sorry an id was missing in psql documentation, thus CI was failing.
Please find attached a patch that fixes this issue.
Best Regards,
Sadeq Dousti
Tech Lead and Staff Software Engineer at Trade Republic Bank GmbH
Please find attached a patch that fixes this issue.
Best Regards,
Sadeq Dousti
Tech Lead and Staff Software Engineer at Trade Republic Bank GmbH
Attachment
Sadeq Dousti <msdousti@gmail.com> writes:
> Please find attached the patch to add a new metacommand (\dh) to psql.
I don't have a strong feeling one way or the other about whether this
is useful functionality or not. But if we do want it, I'd like to
bikeshed on the name a bit. "High-level" is not terminology we use
anywhere else, and it's ambiguous too (just how high in a partition
tree does a table need to be to be listed?). We do use the terms
"root partition" or "partition root". So I'd prefer something
based on those terms. Possibilities:
1. Use "\dr" or maybe better "\dR" (letting \dr remain available for
some future command about roles). The trouble with either of these
is that the name is already partially overloaded, since we already
have \drds, \drg, \dRp, \dRs.
2. Extend the existing \dP (display partitions) command with some
flag that restricts the listing to root partitions, probably
"r" for "root".
\dP[itnx+] [PATTERN] list [only index/table] partitioned relations [n=nested
]
I kind of like #2 better, but am not set on it.
While we're at it maybe we could bring some sanity to the "n"
flag, which seems to have several bizarre properties like
working differently depending on whether you give a pattern.
It looks from this documentation like it's the inverse of what
"r" would do, but testing says it's not.
regards, tom lane
> Sorry an id was missing in psql documentation, thus CI was failing.
Combining patches into one, as otherwise CI does not combine them.
Best Regards
Sadeq Dousti
Combining patches into one, as otherwise CI does not combine them.
Best Regards
Sadeq Dousti
Attachment
Hi Tom,
Thanks a lot for looking into this!
> "High-level" is not terminology we use anywhere else
> We do use the terms "root partition" or "partition root".
I agree "high-level" is not standard, but "root partition" also entails
the table is actually partitioned. This is not necessarily the case.
The suggested command will show all non-partitioned tables, plus the
root partitions.
So, in the example I provided, t is a non-partitioned table, and z is
the root partition. The command shows them both:
postgres=# \dht
List of root tables
Schema | Name | Owner
--------+------+-------
public | t | sadeq
public | z | sadeq
The reason I called them high-level is that they provide a
high-level overview into the database table/index structure.
> Extend the existing \dP (display partitions)
I also thought about it at first, but that command is for displaying
partitions, while I want the opposite: Don't display partitions at all.
I tried to detail why I didn't use \dP or any existing \d commands
in the first email of the thread (section "Design Decision").
Best Regards,
Sadeq Dousti
Thanks a lot for looking into this!
> "High-level" is not terminology we use anywhere else
> We do use the terms "root partition" or "partition root".
I agree "high-level" is not standard, but "root partition" also entails
the table is actually partitioned. This is not necessarily the case.
The suggested command will show all non-partitioned tables, plus the
root partitions.
So, in the example I provided, t is a non-partitioned table, and z is
the root partition. The command shows them both:
postgres=# \dht
List of root tables
Schema | Name | Owner
--------+------+-------
public | t | sadeq
public | z | sadeq
The reason I called them high-level is that they provide a
high-level overview into the database table/index structure.
> Extend the existing \dP (display partitions)
I also thought about it at first, but that command is for displaying
partitions, while I want the opposite: Don't display partitions at all.
I tried to detail why I didn't use \dP or any existing \d commands
in the first email of the thread (section "Design Decision").
Best Regards,
Sadeq Dousti
> That definition seems bizarre, and even less derivable
> from the term "high-level" than what I thought you meant.
> You really need some other terminology, I think.
Totally agree about the naming. There's no other terminology known to
me, but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.
The way we can think about the concept: these are "top" tables,
regardless of whether they have children (partitions) or not.
> (BTW, you forgot to cc the list, so nobody else saw this defense
> of your idea.)
Oops! Thanks for mentioning this. I sent the previous email to the list
as well.
Best Regards,
Sadeq Dousti
> from the term "high-level" than what I thought you meant.
> You really need some other terminology, I think.
Totally agree about the naming. There's no other terminology known to
me, but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.
The way we can think about the concept: these are "top" tables,
regardless of whether they have children (partitions) or not.
> (BTW, you forgot to cc the list, so nobody else saw this defense
> of your idea.)
Oops! Thanks for mentioning this. I sent the previous email to the list
as well.
Best Regards,
Sadeq Dousti
Sadeq Dousti <msdousti@gmail.com> writes:
>> "High-level" is not terminology we use anywhere else
>> We do use the terms "root partition" or "partition root".
> I agree "high-level" is not standard, but "root partition" also entails
> the table is actually partitioned. This is not necessarily the case.
> The suggested command will show all non-partitioned tables, plus the
> root partitions.
Hmm, that seems even less derivable from the term "high-level"
than what I thought you meant. However, if that's the behavior
you want, maybe we could extend "\d[ti...]" with an option that
suppresses partition-tree members? My first thought about that
is "n" for "not a partition member", but maybe somebody else
can invent a better name. (One problem with "n" is that "\dn"
without either "t" or "i" would mean something else. Using "N"
would fix that, but the capitalization seems a bit random.)
Independently of that, extending \dP with an option for "roots
only" seems like a good idea. But that's a different patch.
regards, tom lane
On Sun, Feb 23, 2025 at 4:34 PM Sadeq Dousti <msdousti@gmail.com> wrote:
Totally agree about the naming. There's no other terminology known to me, but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.
The problem is that we are really tight on available letters for a bare \d. I immediately thought of "adult" tables (those that are not children). But both \da and \dA are used! The only other mnemonic option is "\de" where e stands for elder tables (get it? elder because they are not children).
Would it be useful to you to have them as separate items? In other words, not indexes and tables together, but some flag on both \dt and \di to limit to non-child relations. I think as far as "core structure" the list of tables is far more important than what indexes happen to be on them. Plus, \d also shows sequences, which is even less useful information than indexes, IMHO.
So maybe \dta and \dia? (adults only)
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
On Sun, Feb 23, 2025 at 4:31 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
On Sun, Feb 23, 2025 at 4:34 PM Sadeq Dousti <msdousti@gmail.com> wrote:Totally agree about the naming. There's no other terminology known to me, but I'll definitely think about it. Of course, I'm very open to
suggestions from you or anyone else in the community.The problem is that we are really tight on available letters for a bare \d. I immediately thought of "adult" tables (those that are not children). But both \da and \dA are used! The only other mnemonic option is "\de" where e stands for elder tables (get it? elder because they are not children).
How about:
\pset partition_roots_only 'true'
Then update all relevant queries to respect it.
That way no learning new ways to get the information you want but instead can set a global filter to suppress those child nodes that really don't need to be seen normally.
It would be nice if "N partition children suppressed due to partition_roots_only" would appear in the output somewhere if going this direction.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> How about:
> \pset partition_roots_only 'true'
FWIW, I don't especially love this idea. None of our existing
\d-type commands use pset parameters for filtering, and I don't
see a reason to believe that people would typically want this
to be a persistent setting. I'd guess that sometimes you want
the tables filtered one way and sometimes the other.
regards, tom lane
po 24. 2. 2025 v 6:16 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> How about:
> \pset partition_roots_only 'true'
FWIW, I don't especially love this idea. None of our existing
\d-type commands use pset parameters for filtering, and I don't
see a reason to believe that people would typically want this
to be a persistent setting. I'd guess that sometimes you want
the tables filtered one way and sometimes the other.
There can be numeric parameter that can specify the deep - some like
\dP 0
Regards
Pavel
regards, tom lane
Dear all,
I'm grateful for taking your time and providing various solutions!
Personally, I like Tom's idea of using N better:
* It can stand for "not a partition member" or "no partitions", and does
not provide any new terminology.
* It can be combined with various commands \dN, \dtN (or \dNt), \dNi
(or \diN), without causing confusion with existing command (e.g., \dn)
* While capitalization of N might seem arbitrary, it can be acceptable
as it's "negating" some concept
About other suggestions:
* While "a" (adult) and "e" (elders) are adorable, they introduce new
terminologies that are not widely known to the Postgres community. Being
super pedantic, an adult is always a child of some parent, and an elder
must always have one or more children. In addition, \da and \det already
exists.
* Using \pset: As Tom said, plus I think explicit is better than
implicit. Consider some DBA (or their colleague) adding this \pset into
.psqlrc, and then forget about it.
* Using \dP 0: The command \dP is already used to show partitioned
tables, and the default behavior (i.e., not \dPn) is to only show the
I'm grateful for taking your time and providing various solutions!
Personally, I like Tom's idea of using N better:
* It can stand for "not a partition member" or "no partitions", and does
not provide any new terminology.
* It can be combined with various commands \dN, \dtN (or \dNt), \dNi
(or \diN), without causing confusion with existing command (e.g., \dn)
* While capitalization of N might seem arbitrary, it can be acceptable
as it's "negating" some concept
About other suggestions:
* While "a" (adult) and "e" (elders) are adorable, they introduce new
terminologies that are not widely known to the Postgres community. Being
super pedantic, an adult is always a child of some parent, and an elder
must always have one or more children. In addition, \da and \det already
exists.
* Using \pset: As Tom said, plus I think explicit is better than
implicit. Consider some DBA (or their colleague) adding this \pset into
.psqlrc, and then forget about it.
* Using \dP 0: The command \dP is already used to show partitioned
tables, and the default behavior (i.e., not \dPn) is to only show the
roots of the partition. What I'd like is to show roots as well as
non-partitioned tables.
If there are no hard objections, I'll proceed with adding the N letter
to \d commands.
Best Regards,
Sadeq Dousti
non-partitioned tables.
If there are no hard objections, I'll proceed with adding the N letter
to \d commands.
Best Regards,
Sadeq Dousti
On Mon, Feb 24, 2025 at 3:07 AM Sadeq Dousti <msdousti@gmail.com> wrote:
* While capitalization of N might seem arbitrary, it can be acceptable as it's "negating" some concept
...
If there are no hard objections, I'll proceed with adding the N letter to \d commands.
Not strongly opposed, but "N" is a slightly odd as we already have "n" and pg_dump uses both "n" and "N", where the latter is the inverse of the former. But since I have no better idea at the moment, a weak +1 until more bike shedders arrive. :)
Cheers,
Greg
P.S. On reflection, I do like having a capital letter more than a lowercase for an exclusion case like this.
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support