[PATCH] psql: add size-based sorting options (O/o) for tables and indexes

On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:

Hello,
This patch adds two new meta-command modifiers for \dt(+) and \di(+):

- O : sort by total relation size descending
- o : sort by total relation size ascending

Thanks for your contribution. Register your patch in the next commitfest [1]https://commitfest.postgresql.org/57/ so
we don't loose track of it.

I didn't look at your patch but I was wondering if a general solution isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines this
property for all objects if applicable.

\sort [ name | size [ asc | desc ] ]

I thought about a list to be cover other sort cases too but if things starting
to be complex, it is time to write your own query.

With a parameter, it appends the ORDER BY clause in the SQL commands executed by
psql if applicable. Without a parameter, it uses the current behavior.

[1]: https://commitfest.postgresql.org/57/

--
Euler Taveira
EDB https://www.enterprisedb.com/

Hi

st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal:

On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:

Hello,
This patch adds two new meta-command modifiers for \dt(+) and \di(+):

- O : sort by total relation size descending
- o : sort by total relation size ascending

Thanks for your contribution. Register your patch in the next commitfest
[1] so
we don't loose track of it.

I didn't look at your patch but I was wondering if a general solution
isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines
this
property for all objects if applicable.

\sort [ name | size [ asc | desc ] ]

I thought about a list to be cover other sort cases too but if things
starting
to be complex, it is time to write your own query.

It is big question - if there should be specialized metacommand, or just
variable or \pset setting

it can be

\set PREFERRED_ORDER size_desc
\pset preffered_order size_desc

With a parameter, it appends the ORDER BY clause in the SQL commands
executed by
psql if applicable. Without a parameter, it uses the current behavior.

There were a lot of proposals related to this topic some years ago. I wrote
a lot of variants of this patch
Generic design is very big, and solutions like proposed are not generic
:-). We talked about this feature for maybe more than one year, and we
didn't find a generally acceptable design.

At the end I wrote pspg, and the sort can be done (over result) there.
Using a vertical cursor (column cursor) is very natural and user friendly.

https://github.com/okbob/pspg

Regards

Pavel

st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com>
napsal:

On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:

Hello,
This patch adds two new meta-command modifiers for \dt(+) and \di(+):

- O : sort by total relation size descending
- o : sort by total relation size ascending

Thanks for your contribution. Register your patch in the next commitfest
[1] so
we don't loose track of it.

I didn't look at your patch but I was wondering if a general solution
isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines
this
property for all objects if applicable.

\sort [ name | size [ asc | desc ] ]

I thought about a list to be cover other sort cases too but if things
starting
to be complex, it is time to write your own query.

It is big question - if there should be specialized metacommand, or just
variable or \pset setting

it can be

\set PREFERRED_ORDER size_desc
\pset preffered_order size_desc

With a parameter, it appends the ORDER BY clause in the SQL commands
executed by
psql if applicable. Without a parameter, it uses the current behavior.

There were a lot of proposals related to this topic some years ago. I
wrote a lot of variants of this patch
Generic design is very big, and solutions like proposed are not generic
:-). We talked about this feature for maybe more than one year, and we
didn't find a generally acceptable design.

/messages/by-id/CAFj8pRAVV2TFHsFCV=c9Aaeq7kPWGQBLkOwGronpAN583zqhWg@mail.gmail.com

The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested

Hi,

I tested the patch on top of current master. It applies cleanly and
builds without any issues.

I tried the new options with \dt and \di after creating tables and
indexes of different sizes. Both 'O' (desc) and 'o' (asc) work as
expected and the default behavior without these flags is unchanged.

Functionality looks good to me.

Regards,
Mahmoud Ayman

The new status of this patch is: Ready for Committer

On Thu, 27 Nov 2025 at 08:52, Pavel Stehule <pavel.stehule@gmail.com> wrote:

st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

Hi

st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal:

On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:

Hello,
This patch adds two new meta-command modifiers for \dt(+) and \di(+):

- O : sort by total relation size descending
- o : sort by total relation size ascending

Thanks for your contribution. Register your patch in the next commitfest [1] so
we don't loose track of it.

I didn't look at your patch but I was wondering if a general solution isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines this
property for all objects if applicable.

\sort [ name | size [ asc | desc ] ]

I thought about a list to be cover other sort cases too but if things starting
to be complex, it is time to write your own query.

It is big question - if there should be specialized metacommand, or just variable or \pset setting

it can be

\set PREFERRED_ORDER size_desc
\pset preffered_order size_desc

With a parameter, it appends the ORDER BY clause in the SQL commands executed by
psql if applicable. Without a parameter, it uses the current behavior.

There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patch
Generic design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybe more than one year, and we didn't find a generally acceptable design.

/messages/by-id/CAFj8pRAVV2TFHsFCV=c9Aaeq7kPWGQBLkOwGronpAN583zqhWg@mail.gmail.com

At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is very natural and user friendly.

https://github.com/okbob/pspg

Regards

Pavel

[1] https://commitfest.postgresql.org/57/

--
Euler Taveira
EDB https://www.enterprisedb.com/

Hi hackers.

I noted that this patch cf entry has the status "Ready for committer"
[0]: https://commitfest.postgresql.org/patch/6258/
For my 2c, I would vote for general-purpose separate \sort command or
some suffix for meta-command as proposed by Pavel in thead from 2017.

I also suggest to rename commitfest entry to describe "what" instead of "how"

[0]: https://commitfest.postgresql.org/patch/6258/

--
Best regards,
Kirill Reshke

Hi,
As you mentioned, this issue has remained unresolved since 2017, and I
think we need to start somewhere.
While having it as a meta-command would provide a general solution,
having size-based sorting specific to tables/indexes wouldn't prevent
a future meta-command feature from being implemented.

Best regards,

Kirill Reshke <reshkekirill@gmail.com>, 3 Oca 2026 Cmt, 22:34
tarihinde şunu yazdı:

On Thu, 27 Nov 2025 at 08:52, Pavel Stehule <pavel.stehule@gmail.com> wrote:

st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

Hi

st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal:

On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:

Hello,
This patch adds two new meta-command modifiers for \dt(+) and \di(+):

- O : sort by total relation size descending
- o : sort by total relation size ascending

Thanks for your contribution. Register your patch in the next commitfest [1] so
we don't loose track of it.

I didn't look at your patch but I was wondering if a general solution isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines this
property for all objects if applicable.

\sort [ name | size [ asc | desc ] ]

I thought about a list to be cover other sort cases too but if things starting
to be complex, it is time to write your own query.

It is big question - if there should be specialized metacommand, or just variable or \pset setting

it can be

\set PREFERRED_ORDER size_desc
\pset preffered_order size_desc

With a parameter, it appends the ORDER BY clause in the SQL commands executed by
psql if applicable. Without a parameter, it uses the current behavior.

There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patch
Generic design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybe more than one year, and we didn't find a generally acceptable design.

/messages/by-id/CAFj8pRAVV2TFHsFCV=c9Aaeq7kPWGQBLkOwGronpAN583zqhWg@mail.gmail.com

At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is very natural and user friendly.

https://github.com/okbob/pspg

Regards

Pavel

[1] https://commitfest.postgresql.org/57/

--
Euler Taveira
EDB https://www.enterprisedb.com/

Hi hackers.

I noted that this patch cf entry has the status "Ready for committer"
[0]. I do not think so. I see major design concerns in the proposal.
For my 2c, I would vote for general-purpose separate \sort command or
some suffix for meta-command as proposed by Pavel in thead from 2017.

I also suggest to rename commitfest entry to describe "what" instead of "how"

[0] https://commitfest.postgresql.org/patch/6258/

--
Best regards,
Kirill Reshke

--
M.Atıf Ceylan

Hi

so 3. 1. 2026 v 21:04 odesílatel M.Atıf Ceylan <mehmet@atifceylan.com>
napsal:

Hi,
As you mentioned, this issue has remained unresolved since 2017, and I
think we need to start somewhere.
While having it as a meta-command would provide a general solution,
having size-based sorting specific to tables/indexes wouldn't prevent
a future meta-command feature from being implemented.

Now, two backslash commands uses parameters, so we have some precedents

\g (pset options)
\watch attr=val

I almost like a solution based on pset and possibility to use pset option
for \describe commands

\pset preferred_order SCHEMA_NAME_SORTED | SIZE_SORTED_DESC |
SIZE_SORTED_DESC_ALWAYS
\dt+
or just \dt+ (preferred_order=SIZE_SORTED_DESC)

I don't think the length of text should be problematic, there is tab
complete and history. The advantage is possibility to store setting to
.psqlrc

another solution is using attributes

\dt+ o=size_desc

It can works too, I think, but there is not possibility to set it for
.psqlrc

Personally, I think, both proposed solution can work together (command
attributes has higher priority than pset options)

I don't think so we need to implement fully generic or complex solution,
because anybody can easily to modify buildin queries

Both proposals are based on currently implemented design - there is large
possibilities for design anything else, but maybe can be better to don't
introduce new principes

Regards

Pavel