In terms of performance, "character varying" or "text" in PostgreSQL?

Question

In terms of performance, "character varying" or "text" in PostgreSQL?

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

Before posting this question I searched the internet to know the differences and performance of each type and I came to the conclusion that I should discuss a little more about this subject here with the experts, rs, I already know the differences between CHAR CHARACTER VARYING (Varchar) and TEXT (Blob) , but what you are getting is the performance, according to the comments Varchar and Text had the same average in milliseconds, but it is not what I am going through, I have a system of medicine and work security, where many texts are saved so I have opted for type TEXT , but this is going to cost me dear, the screens that need to open these fields have taken a lot of time, when hidden (retreat) the screen opens immediately. >

The question is:

Can I use CHARACTER VARYING without limiting characters like TEXT or can I have problems in the future?

The question is why from what I have read regarding TEXT or BLOB some banks save this field separately from the table and so they should be used carefully, I explain, a lot of people come out creating a table like this:

Nome: Text
Endereco: Text
Bairro: Text

The old custom of Access and this should not be done in relational databases. What is the experience of friends in this respect and what would you recommend?

I have taken all precautions regarding indexes, data types and selects to improve performance, when there were few data was all right, now the base with tables of 6 million records the system starts to take longer than expected.

desempenho postgresql text varchar

asked by anonymous 28.11.2017 / 12:26

2 answers

2

According to the documentation there is no performance difference . It is in this place that you must search, you can avoid other places.

The implementation is unique, it only changes the name.

If the problem is the screen then see what is wrong with the screen and not the database. If you are slow with too much data you have other problems.

If you are testing the same things it is to give the same result, if you are testing different things then the problem is the difference of what you are doing. If you test wrong you will get the wrong result.]

In PostgreSQL the custom is to use TEXT whenever there is no reason to use something else, and almost always does not, and if it is in the rare cases that a CHAR is more appropriate (which also has the same performance) .

28.11.2017 / 12:56

Display ID in form before registering php and mysql CONDITIONS IF STRUCTURE and an if inside another

score 1 · Accepted Answer

postgresql documentation on char types (char, varchar and text):

There is no performance difference between these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character (n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character (n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Short Translation: There is no significant difference in performance between types, some other databases have, but this one does not, so in doubt use text.

Now speaking of personal experience in postgresql, I have never dealt with a significant amount of data in postgresql in a "real" application (only other dbs), but I have already done some prototypes on top of it with a few good millions of records and more of 1 column of text and I had no performance problem.

What I can tell you is also that it is 6 million records is not absurd amount for a mature database like postgresql, if you are having performance problems I suggest doing what is always recommended in these cases: measure everything and see exactly where the bottleneck is before moving out typing columns. Speculating about what causes performance problem does not usually work out, especially if it's something that runs away from your specialty.