Postgres Pro
Downloads
Home   >   mailing lists

Thread: Findout long unused tables in database

Findout long unused tables in database

From
Andreas Fröde
Date:
Hello,
I am looking for a way to find out when a table was last used for 
reading. (Without writing every statement in the logfile or putting a 
trigger on it). Is there such a thing?


CIAO
andreas

Re: Findout long unused tables in database

From
Laurenz Albe
Date:
On Mon, 2022-09-26 at 14:05 +0200, Andreas Fröde wrote:
> I am looking for a way to find out when a table was last used for 
> reading. (Without writing every statement in the logfile or putting a 
> trigger on it). Is there such a thing?

No, there is no way to do that short of logging all statements.

I expect that removing permissions on a table and checking whether
your application hits an error is not an option...

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Findout long unused tables in database

From
Andreas Fröde
Date:
Hi Laurenz,


> No, there is no way to do that short of logging all statements.

Thank you for the quick if unfortunate reply.

> I expect that removing permissions on a table and checking whether
> your application hits an error is not an option...

I will try to suggest this. :-)

Have a nice day.

Andreas

Re: Findout long unused tables in database

From
Andreas Kretschmer
Date:

Am 26.09.22 um 14:05 schrieb Andreas Fröde:
> Hello,
> I am looking for a way to find out when a table was last used for 
> reading. (Without writing every statement in the logfile or putting a 
> trigger on it). Is there such a thing?
>

no really what you are looking for, i know, but we have 
pg_stat_user_tables. There can you find how often the table was queried 
in the past. Take the data, wait some time, take it again and compare.



Regards, Andreas

-- 
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

Re: Findout long unused tables in database

From
Andreas Fröde
Date:
Hi Andreas,


> no really what you are looking for, i know, but we have 
> pg_stat_user_tables. There can you find how often the table was queried 
> in the past. Take the data, wait some time, take it again and compare.

Thanks for this idea. i will try it out.

Andreas

Re: Findout long unused tables in database

From
Laurenz Albe
Date:
On Tue, 2022-09-27 at 08:35 +0200, Andreas Kretschmer wrote:
> 
> 
> Am 26.09.22 um 14:05 schrieb Andreas Fröde:
> > Hello,
> > I am looking for a way to find out when a table was last used for 
> > reading. (Without writing every statement in the logfile or putting a 
> > trigger on it). Is there such a thing?
> > 
> 
> no really what you are looking for, i know, but we have 
> pg_stat_user_tables. There can you find how often the table was queried 
> in the past. Take the data, wait some time, take it again and compare.

Ah, that is the best solution.  I should have thought of that.

Yours,
Laurenz Albe