A
A
Anton2019-01-28 08:12:20
PostgreSQL
Anton, 2019-01-28 08:12:20

How do you do PostgreSQL periodic maintenance if you have large databases with hundreds of GB large tables?

How do you do PostgreSQL periodic maintenance if you have large databases with hundreds of GB large tables?
For example, a 700GB database, in which one of the tables is 600GB, there is about 350GB of free space.
By periodic maintenance I mean vacuum, reindex, analyze

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2019-01-28
Patsev @chemtech

By periodic maintenance I mean vacuum, reindex, analyze

What for?
Please note, this is a postgresql DBA question. Yes, we have tables for several terabytes.
After normal autovacuum settings for iron (instead of the default settings for any coffee maker), autovacuum normally copes with vacuum and analyze.
Instead of reindex - pgcompacttable , pg_repack , yes, at least manually or with some script create index concurrently + drop index concurrently

K
ky0, 2019-01-28
@ky0

Partitioning with rotation of old data to another tablespace, LVM, preliminary assessment of the amount of space needed for the database. In extreme cases - work on the implementation of all this with downtime.
What do you mean by service in general? Version update?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question