From: | Clodoaldo Neto <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> |
---|---|
To: | PostgreSQL - General ML <pgsql-general(at)postgresql(dot)org> |
Subject: | Disk file effects of delete and vacuum |
Date: | 2012-02-24 22:17:44 |
Message-ID: | CA+Z73LFH2Mu2MAJFmt7N0D0yC13qnuikC=8mkXCaE3xcKb4KZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a very frequently updated table with 240 million rows (and growing).
Every three hours 1.5 million rows are inserted and 1.5 million are
deleted. When I moved the cluster to a SSD this bulk insert (using copy)
time was cut from 22 minutes to 2.3 minutes. The deletion time was also
improved. I plan to make this bulk update every two hours or every hour.
Although the performance now (after SSD) is compatible with a more frequent
update I have read some horror stories about SSD death due to limited NAND
endurance combined with write amplification. As SSDs are expensive I would
like to push its death as far into the future as possible. Hence my
question: What really happens to the disk file in a delete and subsequent
vacuum? I guess there are two disk writes, one to mark the row as deleted
and the other when vacuuming to mark it as available to overwrite. If
instead of deleting and vacuuming I partition the table creating and
dropping tables at each bulk insert/delete would I be minimizing the SSD
wearing?
Regards, Clodoaldo
From | Date | Subject | |
---|---|---|---|
Next Message | Jayashankar K B | 2012-02-24 22:42:29 | Re: [PERFORM] Disable-spinlocks while compiling postgres 9.1 for ARM Cortex A8 |
Previous Message | Ched Cheatham | 2012-02-24 22:05:08 | Pull the Trigger and Where's the SQL? |