2

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

3

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

  • Thanks for the answer. I added your expression and all tables happen to be i (all the hashed and pkey ones) and t the actual tables. I don't understand why t since there is no t in your expression, but anyway: Any link to documentation where I can understand a bit more about this? Then, the answer is to keep it as it is? I have a bigger table that occupies 34GB, and its pkey alone is 5GB; so I have to worry a bit hehe. – vabada Jun 23 '16 at 14:25
  • 1
    @dabad: the t comes from the else relkind and indicates a TOAST table. For details on all columns in pg_class see here: postgresql.org/docs/current/static/catalog-pg-class.html for details on TOAST see here: postgresql.org/docs/current/static/storage-toast.html – a_horse_with_no_name Jun 23 '16 at 14:30
  • Thanks again. I'll have to read it more than once to get to know it. Just to conclude: Any reason why this could have happened? I checked it last week and I only saw one table "per table". And... should I worry? Should I start to think in performing some compression in this data? – vabada Jun 23 '16 at 14:40
  • 1
    This is normal behavior. If you add a primary key, an index will be created. If you store data that can be TOASTed, a TOAST table will be created. – a_horse_with_no_name Jun 23 '16 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.