Thread: What does "Table rewrite" mean?
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?
=?utf-8?q?PG_Doc_comments_form?= <noreply@postgresql.org> writes:
> 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?
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
On Fri, Jan 19, 2018 at 11:33:43AM -0500, Tom Lane wrote: > 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. Yeah that can be costly. Note that WAL corresponding to this data needs to be generated as well. -- Michael
Attachment
Michael Paquier wrote: > On Fri, Jan 19, 2018 at 11:33:43AM -0500, Tom Lane wrote: > > 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. > > Yeah that can be costly. Note that WAL corresponding to this data needs > to be generated as well. 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
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 */
On 20 Jan 2018, at 22:47, Alvaro Herrera wrote:
Michael Paquier wrote:
On Fri, Jan 19, 2018 at 11:33:43AM -0500, Tom Lane wrote:
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.Yeah that can be costly. Note that WAL corresponding to this data needs
to be generated as well.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