postgresql delete old recorde

jamel2 years ago

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

Hector Cortez2 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]
jamel2 years ago

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

Anton Tananaev2 years ago

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

jamel2 years ago

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

Anton Tananaev2 years ago

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

jamel2 years ago

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

jamel2 years ago

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

Hector Cortez2 years ago

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