Hello experts, Could someone kindly tell me how to run vacuum

Question

PostgreSQL (English)

Amol Palav

Hello experts, Could someone kindly tell me how to run vacuum

inside a plpgsql function? If you have any link please share.
Thank you

#programming #russian

0

27.06.2024

10 ответов

19 просмотров

Amol Palav Автор вопроса

Stefanie Janine Stölting
What is the goal, what do you expect to achieve? ...

We have many such tables who have different characteristics and demands wrt vacuuming. Having a centralised and self managed routine will help us to manage it easily rather than adjusting each table individually. The question is do we have any way to run vacuum inside plpgsql?

0

2 дня назад

Stefanie Janine Stölting

Amol Palav
We have many such tables who have different charac...

You want to add all those fables automatically to a list and vaccuum them even when it might not be necessary? By select table_name from information_schema.tables where not tabke_name in ('not this one'); ? And you believe that to be better and less intrusive for your application?

0

2 дня назад

Amol Palav Автор вопроса

Stefanie Janine Stölting
You want to add all those fables automatically to ...

Nope, not at all In fact I want to have a job that will support autovacuum but with manual control instead relying on autovacuum. Also, this manual job will help me to discover application's behaviour and even how each customer is using each functionality/table. With your questions, I'm sensing that there's no way to run vacuum inside plpgsql

0

2 дня назад

Stefanie Janine Stölting

Amol Palav
We have many such tables who have different charac...

And no, it is not possible to run vacuum inside functions or procedures. Because that would be inside a transaction. It's documented https://www.postgresql.org/docs/current/sql-vacuum.html under NOTES.

0

2 дня назад

Amol Palav Автор вопроса

Stefanie Janine Stölting
And no, it is not possible to run vacuum inside fu...

Ok

0

2 дня назад

Stefanie Janine Stölting

Amol Palav
Ok

But you can get the information about dead tuples from statistics: SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables;

0

2 дня назад

Amol Palav Автор вопроса

Stefanie Janine Stölting
But you can get the information about dead tuples ...

That's a cumulative info and these counters get reset when maintenance processes run against those tables. Due to this, I can't discover application's behaviour

0

2 дня назад

Stefanie Janine Stölting

Amol Palav
That's a cumulative info and these counters get re...

Together with last_vacuum you can copy the data regularly into your own statistics table.

0

2 дня назад

Amol Palav Автор вопроса

Stefanie Janine Stölting
Together with last_vacuum you can copy the data re...

Certainly, thank you

0

2 дня назад

Stefanie Janine Stölting · Accepted Answer

Stefanie Janine Stölting

What is the goal, what do you expect to achieve? Because usually autovaacuum solves the problem. Sometimes high traffic tables with lots of updates and deletes need some adjustment. And yes, you can change the settings per table.

0

2 дня назад

169 похожих чатов

Hello experts, Could someone kindly tell me how to run vacuum

10 ответов

Похожие вопросы