Subject: What does "Table rewrite" mean?

From: PG Doc comments form <[email-masked]>
Date: Fri, 19 Jan 2018 16:03:40 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.5/static/sql-altertable.html
Description:

I see references to a "table rewrite" all over the place, but I cannot find
one single definition on what that actually means.

What does a table rewrite do?  Does it drop and recreate the table? 
Everywhere I look people describe it with fear and trepedation as if it was
some catastrophically dangerous operation to perform.  What is it?

---

From: Tom Lane <[email-masked]>
Date: Fri, 19 Jan 2018 11:33:43 -0500

> some catastrophically dangerous operation to perform.  What is it?

It means reading the whole table and writing it out in some modified
form (for instance, with some column transformed into a new datatype).
It's not "dangerous" in any way ... but if you've got many GB of data in
the table and you can't afford to have the table locked for a long time,
then it's something to avoid.

			regards, tom lane

---

From: Michael Paquier <[email-masked]>
Date: Sat, 20 Jan 2018 19:18:07 +0900

> then it's something to avoid.

Yeah that can be costly. Note that WAL corresponding to this data needs
to be generated as well.
--
Michael

---

From: Alvaro Herrera <[email-masked]>
Date: Sun, 21 Jan 2018 00:47:50 -0300

> Yeah that can be costly. Note that WAL corresponding to this data needs

Maybe we need to document this somewhere, particularly now that we have
a "table_rewrite" event item.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: "Ilsa Loving" <[email-masked]>
Date: Mon, 22 Jan 2018 15:59:07 -0500

That’s all that’s needed, really.  It’s impossible to make an 
informed decision if there is no way for someone to know what a table 
rewrite actually does and how it does it.

/*---------------------------------------------------------------------------------*/
float o=0.075,h=1.5,T,r,O,l,I;/* Ilsa Loving        */int 
_,L=80,s=3200; int main()
{for(;s%L||(h-=o,T=-2),s;4-(r=/* IT Manager         
*/O*O)<(l=I*I)|++ _==L&&
write(1,(--s%L?_<L?--_%6:6:7) /* The Jonah Group    */+"Ilsa 
L.\n",1)&&(O=I=l=_=r=0,
T+=o /2))O=I*2*O+h,I=l+T-r;}  /* 416-304-0860x227   */