PostgreSQL – Resolving Duplicated Tables with Suffixes

djangopostgresqlpostgresql-9.3

due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the actual table, with the same name and a suffix. An example:

Running the following command once connected to my database:

SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class ORDER BY relpages DESC;

Returns this:

                            relname                       |  reltuples  |  MB  
----------------------------------------------------------+-------------+-------
 ...                                                      |             |
 reversion_revision                                       |      806547 |    58
 ...                                                      |             |
 app_session                                              |       96803 |    40
 ...                                                      |             |
 reversion_revision_pkey                                  |      806547 |    17
 reversion_revision_manager_slug_7521cb9583783434_like    |      806547 |    17
 reversion_revision_c69e55a4                              |      806547 |    17
 reversion_revision_e8701ad4                              |      806547 |    17
 reversion_revision_b16b0f06                              |      806547 |    17
 ...                                                      |             |
 app_session_dadd2494                                     |       96803 |     2
 app_session_f8a3193a                                     |       96803 |     2
 app_session_3ed54a03                                     |       96803 |     2
 app_session_4437cfac                                     |       96803 |     2
 app_session_pkey                                         |       96803 |     2
 app_session_e160a0b9                                     |       96803 |     2
 app_session_975a0aa5                                     |       96803 |     2
 app_session_b5ac7ae1                                     |       96803 |     2
 app_session_c585221c                                     |       96803 |     2
 ...                                                      |             |

I think they are duplicated since the number of reltuples is the exact same and the names share the original name (plus the weird suffix)

I am not a DB expert, but this seems wrong to me. Is there a problem with this? The only one I see (since everything works fine) is the space increase. Is there a clear solution? Did I perform something wrong? Is it dangerous?

I imagine it should be enough with deleting this "duplicated" tables. Would there be a programmatic way of doing this? Any hints? I am completely lost.

I am running psql (PostgreSQL) 9.3.13 under Ubuntu 14.04

Best Answer

No, those are not "duplicated", those are additional relations/objects created for the tables.

reversion_revision_pkey is most probably the unique index supporting the primary key for the table reversion_revision.

And the others are most probably indexes as well.

You can add this expression to your query to see the actual type of the relation:

case relkind
   when 'r' then 'table'
   when 'i' then 'index'
   when 'S' then 'sequence'
   when 'v' then 'view'
   when 'm' then 'materialized view'
   when 't' then 'TOAST table'
   else relkind
end as rel_kind

I imagine it should be enough with deleting this "duplicated" tables

No, do not delete (drop) those. They are essential for your database to work.

For a description on all columns in pg_class see here

Related Question