[Proposal] Add \dAt [AMPTRN [TBLPTRN]] to list tables by Table Access Method in psql

On Tue, 15 Apr 2025 at 14:27, Srinath Reddy <srinath2133@gmail.com> wrote:

Hi all,

I'd like to propose adding a new `psql` meta-command:

\dAt [AMPTRN [TBLPTRN]]

This would list all user tables that are using a specific Table Access Method (TAM), optionally filtered by table name pattern.

Why?
- Table Access Methods are increasingly used.
- There's currently no native `\d`-style way to explore which tables are using a given TAM.
- Users often need to check or debug TAM-based extensions and it'd be useful to have a shortcut.

Example usage:

\dAt sometam -- list all tables using sometam TAM
\dAt sometam auto% -- filter to tables starting with 'auto'

Would love to hear if this sounds like a useful addition — if so, I’d be happy to work on a patch. Also, please let me know if something similar has been discussed before.

I think we can get both the details by SELECT command. Additionally,
this info should be visible by the admin only because there is no use
case for the user to know all the tables from one TAM.

postgres=# select * from pg_am;
oid | amname | amhandler | amtype
------+--------+----------------------+--------
2 | heap | heap_tableam_handler | t
403 | btree | bthandler | i
405 | hash | hashhandler | i
783 | gist | gisthandler | i
2742 | gin | ginhandler | i
4000 | spgist | spghandler | i
3580 | brin | brinhandler | i
(7 rows)

postgres=#
postgres=# select relname from pg_class where relam = (select relam
from pg_am where amname = 'heap');
relname
------------------------------------------------
test
pg_statistic
pg_type
pg_toast_1255
pg_toast_1255_index
pg_toast_1247
pg_toast_1247_index
pg_toast_2604
pg_toast_2604_index
pg_toast_2606
pg_toast_2606_index
pg_toast_2610
pg_toast_2610_index
pg_toast_2612
-------------------------

In the above command, we can add FILTER also to SELECT only pattern
matching table only.

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com

Hi,

On Tue, Apr 15, 2025 at 3:22 PM Mahendra Singh Thalor <mahi6run@gmail.com>
wrote:

I think we can get both the details by SELECT command.

Thanks for the input!
While it's true that we can use catalog queries to get this info, the main
goal of adding a \dAt command or "\"[Informational] commands is to
improve *user
experience and discoverability*. Not everyone wants to write or remember
complex queries — having a direct command helps users quickly inspect TAM
usage, especially when working with multiple forks or extensions.

Additionally,

this info should be visible by the admin only because there is no use
case for the user to know all the tables from one TAM.

I don’t think this information should be restricted to admins. When
specialized TAMs are in use, it’s actually valuable for users to know which
TAM a table is using. This helps them understand how best to interact with
the table and structure their workloads to align with the TAM's intended
access patterns.

Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

On Tue, Apr 15, 2025 at 4:57 AM Srinath Reddy <srinath2133@gmail.com> wrote:

- Table Access Methods are increasingly used.

Citation needed.

- There's currently no native `\d`-style way to explore which tables are
using a given TAM.

There's also no way to list which tables are unlogged. Or which have
autovacuum disabled. Or which belong to a specific tablespace. All of which
are arguably more commonly requested than a TAM.

Perhaps there could be a more generic table-filtering mechanism for \d, but
carving something out for such a niche case seems unwarranted, IMO.

Downstream, you wrote:

Not everyone wants to write or remember complex queries

True - but you can write a view or a function to support those cases.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Greg Sabino Mullane <htamfids@gmail.com> writes:

On Tue, Apr 15, 2025 at 4:57 AM Srinath Reddy <srinath2133@gmail.com> wrote:

- There's currently no native `\d`-style way to explore which tables are
using a given TAM.

Perhaps there could be a more generic table-filtering mechanism for \d, but
carving something out for such a niche case seems unwarranted, IMO.

I don't have a strong opinion on whether this functionality is worth
having in a psql meta-command. But I don't like the proposed syntax
one bit. In my mind the \dA group of meta-commands are supposed to
provide information on the *properties* of access methods. Not on
what uses them. It could be reasonable to have a \dAt command that
shows information about a table access method (although not much is
exposed at SQL level today, so there's not a lot for it to do).
But, for example, \dAf does not run around and find all indexes
using that operator family.

I like your thought that maybe this functionality could be cast
as some sort of filter in the \dt command group (with a syntax that
would allow for other sorts of filters too). I don't have concrete
ideas about how to write that though.

regards, tom lane