Обсуждение: How to find the table which is not used in recent 3 month?
Dear all,
--
I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.
How to find the table which is not used in recent 3 month?
Thanks!
P Help save trees...please don't print this e-mail unless you really need to.
If you are keeping the pg_log files, may be using grep for the respective tables will help.
On Mon, Dec 31, 2018 at 11:59 AM Frantz Song <frantz.song@gmail.com> wrote:
Dear all,I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.How to find the table which is not used in recent 3 month?Thanks!--P Help save trees...please don't print this e-mail unless you really need to.
Hi Frantz,
For the same thing we have enabled "pg_stat_statements" on postgresql.conf.
If you are not using "pg_stat_statements" as of now, sadly you're left with 2 options
1. Slow logs with lower response time, if you have enabled them, though not much trustworthy.
2. Grep in application logs of all apps using postgres as database backend.
On Mon, Dec 31, 2018 at 11:59 AM Frantz Song <frantz.song@gmail.com> wrote:
Dear all,I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.How to find the table which is not used in recent 3 month?Thanks!--P Help save trees...please don't print this e-mail unless you really need to.
Hi all,
maybe a tool such as pgbadger help getting stats from logs ?
Kind regards
Sylvain
Le lun. 31 déc. 2018 à 07:52, Prince Pathria <prince.pathria@goevive.com> a écrit :
Hi Frantz,For the same thing we have enabled "pg_stat_statements" on postgresql.conf.If you are not using "pg_stat_statements" as of now, sadly you're left with 2 options1. Slow logs with lower response time, if you have enabled them, though not much trustworthy.2. Grep in application logs of all apps using postgres as database backend.On Mon, Dec 31, 2018 at 11:59 AM Frantz Song <frantz.song@gmail.com> wrote:Dear all,I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.How to find the table which is not used in recent 3 month?Thanks!--P Help save trees...please don't print this e-mail unless you really need to.
Hi Frantz,
You can trace the timestamp by using below query, it shows the list of the table which is not modified or unused since last 3 month.
You can trace the timestamp by using below query, it shows the list of the table which is not modified or unused since last 3 month.
select relname, NOW()-interval '3 month' from pg_stat_user_tables where n_live_tup=0;
On Mon, Dec 31, 2018 at 11:59 AM Frantz Song <frantz.song@gmail.com> wrote:
Dear all,I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.How to find the table which is not used in recent 3 month?Thanks!--P Help save trees...please don't print this e-mail unless you really need to.
Hi Frantz,
If 'used' means 'written', then I think it might be an idea to look at the youngest 'xmin' in the table, to know when it was last modified together with keeping a log of what xmin is associated to each day.
regards,
fabio pardi
If 'used' means 'written', then I think it might be an idea to look at the youngest 'xmin' in the table, to know when it was last modified together with keeping a log of what xmin is associated to each day.
regards,
fabio pardi
On 31/12/2018 07:30, Frantz Song wrote:
Dear all,I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.How to find the table which is not used in recent 3 month?Thanks!--P Help save trees...please don't print this e-mail unless you really need to.
Hi Frantz,
please keep the list posted.
If you need also to track the reads, then I would follow Prince Pathria's suggestion about pg_stat_statements.
There are indeed also alternatives, but much depends on the setup, the workload, the real final goal..
good luck,
fabio pardi
please keep the list posted.
If you need also to track the reads, then I would follow Prince Pathria's suggestion about pg_stat_statements.
There are indeed also alternatives, but much depends on the setup, the workload, the real final goal..
good luck,
fabio pardi
On 31/12/2018 14:31, Frantz Song wrote:
Hi Fabio,thank you!How about "read"?because i need also need clean the insert task if the table no one read.Fabio Pardi <f.pardi@portavita.eu> 于2018年12月31日周一 下午5:22写道：Hi Frantz,
If 'used' means 'written', then I think it might be an idea to look at the youngest 'xmin' in the table, to know when it was last modified together with keeping a log of what xmin is associated to each day.
regards,
fabio pardiOn 31/12/2018 07:30, Frantz Song wrote:Dear all,I'm use postgresql 9.6, it have 800+tables and some of them is not frequently used.How to find the table which is not used in recent 3 month?Thanks!--P Help save trees...please don't print this e-mail unless you really need to.--P Help save trees...please don't print this e-mail unless you really need to.