postgresql delete old recorde

jamel3 years ago

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

Hector Cortez3 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]
jamel3 years ago

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

Anton Tananaev3 years ago

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

jamel3 years ago

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

Anton Tananaev3 years ago

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

jamel3 years ago

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

jamel3 years ago

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

Hector Cortez3 years ago

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