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
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:
Show quoted text
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