From: | Rob Nikander <rob(dot)nikander(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | reclaiming space from heavily used tables? |
Date: | 2017-12-18 19:03:14 |
Message-ID: | 8034843E-CB75-4272-B89D-B9462161275B@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I've got a large table from which I'd like to completely reclaim space. I read the docs and it sounds like I can’t run `vacuum full`, because this table is accessed constantly and can’t have downtime. Assuming that’s true, what do you think of the following idea? Is there a better alternative?
1. Replace the table T with two tables T1 and T2 and a view T that is `select * from T1 union T2`.
2. Write only to T1, and slowly move records from T2 to T1.
3. When T2 is empty, redefine the view T to be simply `select * from T1`.
4. Recreate or vacuum full T2, so space is fully reclaimed.
5. Redefine view as the union select and repeat process going other direction from T1 to T2, as needed.
I guess one question is whether I can do 1 and 3 (renaming tables, redefining views) atomically without disturbing concurrent select queries.
Rob
From | Date | Subject | |
---|---|---|---|
Next Message | Jeremy Finzel | 2017-12-18 19:08:11 | Re: reclaiming space from heavily used tables? |
Previous Message | Edson Carlos Ericksson Richter | 2017-12-18 17:08:14 | Re: PostgreSQL needs percentage function |