Обсуждение: Overhead changing varchar(2000) to text
Hi! I do have several tables that uses varchar(2000) as store for remarks. Lately, one customer need to store more than 2000 characteres, and I'm considering changing from varchar(2000) to text. What is the overhead? Is there any place where I can learn about storage impacto for each data type? Thanks, -- Atenciosamente, Edson Carlos Ericksson Richter
On Wed, Dec 9, 2015 at 5:13 PM, Edson Richter <edsonrichter@hotmail.com> wrote: > I do have several tables that uses varchar(2000) as store for remarks. > Lately, one customer need to store more than 2000 characteres, and I'm > considering changing from varchar(2000) to text. > > What is the overhead? None -- they are stored in exactly the same format; the only difference is whether the length is limited. > Is there any place where I can learn about storage impacto for each data > type? http://www.postgresql.org/docs/current/interactive/datatype-character.html -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Dec 9, 2015 at 5:17 PM, Kevin Grittner <kgrittn@gmail.com> wrote: > On Wed, Dec 9, 2015 at 5:13 PM, Edson Richter <edsonrichter@hotmail.com> wrote: > >> I do have several tables that uses varchar(2000) as store for remarks. >> Lately, one customer need to store more than 2000 characteres, and I'm >> considering changing from varchar(2000) to text. >> >> What is the overhead? > > None -- they are stored in exactly the same format; the only > difference is whether the length is limited. I probably should have mentioned that an ALTER TABLE to change the column type from varchar(2000) to text does not rewrite the data (since it is in the same format) -- it just changes the catalogs to reflect the lack of a limit on length. Changing the other way would require a pass to check that all existing data passes the length check. >> Is there any place where I can learn about storage impacto for each data >> type? > > http://www.postgresql.org/docs/current/interactive/datatype-character.html While it's fairly technical, you might also be interested in this: http://www.postgresql.org/docs/current/interactive/storage-toast.html -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks. After reading, I've run some tests and found no storage changes in tables moving from varchar(2000) to text. Actually, the biggest change is that I don't have to keep another constraint between app and database - if I want to increase the user perceived space, now I just have to change the application (of course, under the limits). Atenciosamente, Edson Carlos Ericksson Richter Em 09/12/2015 21:17, Kevin Grittner escreveu: > On Wed, Dec 9, 2015 at 5:13 PM, Edson Richter <edsonrichter@hotmail.com> wrote: > >> I do have several tables that uses varchar(2000) as store for remarks. >> Lately, one customer need to store more than 2000 characteres, and I'm >> considering changing from varchar(2000) to text. >> >> What is the overhead? > None -- they are stored in exactly the same format; the only > difference is whether the length is limited. > >> Is there any place where I can learn about storage impacto for each data >> type? > http://www.postgresql.org/docs/current/interactive/datatype-character.html >
On 12/9/15 5:43 PM, Edson Richter wrote: > Actually, the biggest change is that I don't have to keep another > constraint between app and database - if I want to increase the user > perceived space, now I just have to change the application (of course, > under the limits). For what it's worth, I usually put some limit on fields that a webapp can write to in the database. That way a bug in the app (or malicious action) can't just start allocating gigabytes of stuff in your database. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
Em 15/12/2015 00:27, Jim Nasby escreveu: > On 12/9/15 5:43 PM, Edson Richter wrote: >> Actually, the biggest change is that I don't have to keep another >> constraint between app and database - if I want to increase the user >> perceived space, now I just have to change the application (of course, >> under the limits). > > For what it's worth, I usually put some limit on fields that a webapp > can write to in the database. That way a bug in the app (or malicious > action) can't just start allocating gigabytes of stuff in your database. Thanks, Jim. It is a wise advice. I really do that today, but I'll start double checking all text fields with special care. Kind regards, Atenciosamente, Edson Carlos Ericksson Richter