postgresql delete old recorde

jamel 4 years ago

Hi,
I'm using traccar server with PostgreSQL database ,
i would like to remove old data .
can anyone help?

Hector Cortez 4 years ago

I use pgAdmin and Query editor to delete records of tc_events and tc_positions

SELECT * FROM public.tc_events
WHERE servertime > '2022-01-16 00:00:00'
ORDER BY id ASC LIMIT 10

DELETE FROM public.tc_events
WHERE id < [nuerical position id]

SELECT * FROM public.tc_positions
WHERE servertime > '2021-12-23 00:00:00'
ORDER BY id ASC LIMIT 10

DELETE FROM public.tc_positions
WHERE id < [nuerical position id]
jamel 4 years ago

Hey ,
when i delete some rows in the position table the size didn't change

Anton Tananaev 4 years ago

It won't change. That's how databases work.

jamel 4 years ago

so if the size does not decrease
Why would we delete the old data ?

Anton Tananaev 4 years ago

Because existing space in the database will be reused by new records and it won't grow.

jamel 4 years ago

that make sense . so we don't have to shrink it cause it will grow again.
Thanks

jamel 4 years ago

is it okay that the size of tc_positions more than 20GB ?

Hector Cortez 4 years ago

To recover space used in postgres I use:
VACUUM: Free up space for reuse.
VACUUM FULL : Frees up unused disk space.