5

I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation.

VACUUM doesn't help much.

Unfortunetely CLUSTER isn't an option as it causes downtime and pg_repack isn't available for AWS RDS.

I'm looking for hacky alternatives to CLUSTER. One that seems to work fine in my local tests is:

begin;

create temp table tmp_target as select * from target;

delete from target;
insert into target select * from tmp_target order by field1 asc, field2 desc;

drop table tmp_target;

commit;

The ordering of ctid looks correct with:

select ctid, field1, field2 from target order by ctid;

The question is: does this look fine? Is it going to lock the target table for SELECT queries causing downtime in apps? Is there a way to list the locks involved in the transaction?

Related to Cluster command locking read only queries on replica

2
  • (Auto)vacuum is here to help. Is autovacuum configured and how ? You can also tune it per table (just like analyze) which might be useful for your most active tables. Please try those gentle methods first before. By the way, you could use TRUNCATE in your script, but would advise you against re-inventing the wheel. Commented May 26, 2017 at 12:36
  • @KookieMonster please reread the first and second lines
    – brauliobo
    Commented Jul 3, 2017 at 20:21

2 Answers 2

7

If it's just about table bloat, VACUUM FULL is the tool to do it. (VACUUM only shrinks the physical file if it can opportunistically truncate it at the end.) However, VACUUM FULL also takes an exclusive lock on the table, just like CLUSTER.
There are community tools to do the same without exclusive lock:

Unfortunately, most hosted services like Amazon RDS do not allow these additional modules, currently.

Related:

Your current solution is no good, either way. You do the sort operation after deleting all rows (locking them), which will not shorten the downtime (the duration of the lock) for SELECT queries.

If you have no concurrent write access and no depending objects, this might be better:

BEGIN;

CREATE TEMP TABLE tmp_target AS TABLE target ORDER BY field1, field2 DESC;

TRUNCATE target;
INSERT INTO target TABLE tmp_target;

-- DROP TABLE tmp_target;  -- optional; dropped at end of session automatically

COMMIT;
  1. Sort in the background before taking the lock.
    Strictly speaking, INSERT without ORDER BY is free to write rows in any physical order. Practically, though, it will normally copy the current physical order of the temp table with a plain SELECT * FROM ... (TABLE ... for short). May fail for big tables that involve multiple parallel workers. Then you may need ORDER BY.

  2. For big tables, TRUNCATE is faster than DELETE. Be aware of some implications. Like: TRUNCATE does not work with FK constraints. Read the manual for full coverage.

You may want to drop existing indexes right before TRUNCATE and recreate after INSERT to make this faster.

Related:

5
  • thanks @Erwin. It seems to work fine, the only thing necessary is to remove all FKs before and readd them after. Is there a simple way to do this?
    – brauliobo
    Commented May 26, 2017 at 21:13
  • another thing, how to lock a table only for writing? or isn't it necessary for this transaction?
    – brauliobo
    Commented May 26, 2017 at 21:20
  • this answer my first question stackoverflow.com/questions/1152260/… (using ccu instead of tc in the where)
    – brauliobo
    Commented May 26, 2017 at 21:31
  • @vao thanks for pointing out! fixed. Commented May 7, 2019 at 11:23
  • @ErwinBrandstetter my pleasure. You helped me a lot with your SO posts
    – Vao Tsun
    Commented May 7, 2019 at 11:28
2

pg_repack is available on RDS now.

4
  • pg_repack uses a lot of storage and is very disk and CPU intensive, so I gave up using it
    – brauliobo
    Commented Dec 18, 2018 at 13:15
  • What did you end up using?
    – timetofly
    Commented Dec 19, 2018 at 15:14
  • nothing :( still with fragmented data
    – brauliobo
    Commented Dec 21, 2018 at 12:00
  • 2
    Just to note, a VACUUM FULL or CLUSTER is just as resource-intensive. All solutions build a copy of the table (and indexes). With pg_repack you pay some extra price for not being down during (most of) the time, but in my experience it's not that bad. Commented May 7, 2019 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.