Обсуждение: reduce size of table partitioned
Dear All,
Good morning, I have a small doubt regarding a partitioned table, which has its "mother" table and many "daughter" tables, well the main question is... I need to reduce the size of my partitioned table, I can simply delete ( drop table) the oldest daughter tables??? or it is not recommended....or maybe there is another method to do this??
From already thank you very much
Greetings
Good morning, I have a small doubt regarding a partitioned table, which has its "mother" table and many "daughter" tables, well the main question is... I need to reduce the size of my partitioned table, I can simply delete ( drop table) the oldest daughter tables??? or it is not recommended....or maybe there is another method to do this??
From already thank you very much
Greetings
Erik R. Serrano Saavedra
Ingeniero de Sistemas Informáticos
Data Base Administrator
998596691
Am 27.01.24 um 14:31 schrieb Erik Serrano:
Dear All,
Good morning, I have a small doubt regarding a partitioned table, which has its "mother" table and many "daughter" tables, well the main question is... I need to reduce the size of my partitioned table, I can simply delete ( drop table) the oldest daughter tables??? or it is not recommended....or maybe there is another method to do this??
From already thank you very much
GreetingsErik R. Serrano SaavedraIngeniero de Sistemas InformáticosData Base Administrator998596691
see here:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW
Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
Thanks Holger,
Greetings
Erik R. Serrano Saavedra
Ingeniero de Sistemas Informáticos
Data Base Administrator
998596691
El sáb, 27 ene 2024 a las 11:00, Holger Jakobs (<holger@jakobs.com>) escribió:
Am 27.01.24 um 14:31 schrieb Erik Serrano:Dear All,
Good morning, I have a small doubt regarding a partitioned table, which has its "mother" table and many "daughter" tables, well the main question is... I need to reduce the size of my partitioned table, I can simply delete ( drop table) the oldest daughter tables??? or it is not recommended....or maybe there is another method to do this??
From already thank you very much
GreetingsErik R. Serrano SaavedraIngeniero de Sistemas InformáticosData Base Administrator998596691see here:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEWDropping an individual partition using
DROP TABLE, or doingALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid theVACUUMoverhead caused by a bulkDELETE.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
On Sat, Jan 27, 2024 at 8:31 AM Erik Serrano <eserranos@gmail.com> wrote:
Dear All,
Good morning, I have a small doubt regarding a partitioned table, which has its "mother" table and many "daughter" tables, well the main question is... I need to reduce the size of my partitioned table, I can simply delete ( drop table) the oldest daughter tables??? or it is not recommended....or maybe there is another method to do this??
Detaching and dropping will (as mentioned by Holger) certainly reduce the size of your partitioned table.
Remember, though, that you might have partitioned the table by some other key besides a date field.