July 30th, 2008 by depesz | Tags: , , , , , | 1 comment »
Did it help? If yes - maybe you can help me?

Gregory Stark wrote, and Bruce Momjian committed (on 14th of July) patch which shows storage type in psql.

Log Message:
Add column storage type to psql \d+ display.
Gregory Stark

How does it work? It's pretty simple. Let's create new table:

# create table testit (i int4, ts timestamp, t text, z varchar(10), z255 varchar(255));

And see \d+ of it:

# \d+ testit
                           Table "public.testit"
 Column |            Type             | Modifiers | Storage  | Description
 i      | integer                     |           | plain    |
 ts     | timestamp without time zone |           | plain    |
 t      | text                        |           | extended |
 z      | character varying(10)       |           | extended |
 z255   | character varying(255)      |           | extended |
Has OIDs: no

As you can see there is new column which has some new information.

In case you don't know what plain/extended (or external or main) means, please consult the documentation.

Basically, if it says anything else than plain, real data of this column is (or might be) in separate, system, tables – so called “toast tables".

If the storage is main or extended, it means the data in given column is compressed (PostgreSQL uses very fast compression algorithm from the LZ family).

  1. One comment

  2. # Vincenzo Romano
    Jul 30, 2008

    Both last things are quite interesting, while not adding real value to PostgreSQL itself.
    Sounds like the eye-candy I see in modern desktop environment (as nice as useless).
    tThese two things can have some real use, though.

Leave a comment