How To Find A Toast Table’s Primary Table In Postgres

IT Support Forum Forums Databases PostgreSQL General Discussion How To Find A Toast Table’s Primary Table In Postgres

Tagged: 

Viewing 1 reply thread
  • Author
    Posts
    • #2500
      Webmaster
      Keymaster

      If you have a toast tables’ name, you can find the primary tables’ name from the number in the toast table name. To do this, use the following SQL (in the example below, my toast tables’ name is pg_toast.pg_toast_5232684):

      select ‘5232684’::regclass;

      Simply substitute the number in the above SQL with the number of your toast table.

      If you want to do more digging, the pg_class table is of interest.

    • #2501
      Webmaster
      Keymaster

      Personally, I found that there was a toast table that was being vacuumed for too long and I wanted to work out what the primary table for the toast table was, so I could deal with the problem. I used the above trick to find the table name that the toast table was related to. Once I had that, I could then understand why it was getting vacuumed so much (in my case, the data could have been stored in a better way and it was getting updated regularly and this wasn’t necessary. I stopped that and the associated toast table was no longer being constantly vacuumed).

Viewing 1 reply thread
  • You must be logged in to reply to this topic.