In terms of performance, "Character Varying" or "text" in Postgresql?

Asked

Viewed 2,434 times

2

Before posting this question I scoured the internet to know the differences and performance of each type and I came to the conclusion that I should discuss a little more on this subject here with experts, rs, I already know the differences between CHAR, CHARACTER VARYING (Varchar) and TEXT (Blob), but what is catching 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 in real, I have a system of medicine and safety at work, where one keeps many texts so I have opted for the type TEXT, but this is costing me, the screens that need to open these fields has taken a long time, when hidden (retreat) the screen opens immediately.

The question is:

Can I use CHARACTER VARYING without limiting characters as if it were TEXT or I may have problems in the future?

The question is why I’ve been reading about TEXT or BLOB some banks store this field separately from the table and so should be used carefully, I explain, many 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 banks. What is the experience of friends in this respect and what you would recommend?

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

database postgresql performance varchar

2 answers

3

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

The implementation is unique, just changes the name.

If the problem is the screen then see what is wrong on the screen and not in the database. If you are slow with lots of data have other problems.

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

In Postgresql the custom is to use TEXT where there is no reason to use anything else, and almost always does not, and if so it is in the rare cases that a CHAR is more suitable (which also has the same performance).

  • Maniero, I confess that I stood behind the "Tip" that the help of the postgres gives, in programming either there is difference or no, excerpt: "in fact it is usually the slowest of the three", so it is slower, 100 milliseconds in a record is insignificant, but at 6 million the thing changes of figure, if we are talking about performance then all requirements should be looked at with caution, use a TEXT field for a NAME for example, does not seem to me a good practice, then as the end recommends should use CHAR or CHARACTER VARYING, so I decided to put this topic to see the experience of the staff.

  • Ali is talking about the char and not of varchar, and even if it was, you’re saying that the text is slow, which he clearly says it is not. 100 milliseconds? Where did it come from? Even the char is only slower in specific circumstances where it was used where it should not and the difference is absurdly less than this, unless you are using an 80’s computer and have made a char huge that only use a few characters. In fact, you need to be careful, start with, primarily to interpret texts.It says the opposite of what you’re saying.Documentation is the only reliable guide

  • Calm and Maniero, the discussion here is precisely to eliminate doubts as to the type of field and not to discuss on the reliability of the postgres manual, really did not understand the manual so I am asking about the types, as I said I am a programmer and not DBA so my doubts, the fact that I have posted this topic is precisely by being cautious and not go out using any type anywhere, I am making sure with people like you who have knowledge of the cause, that’s all. When he says "Character(n)Character(n)textcharacter Varying", I thought I was out of the TEXT type.

  • 100 milliseconds could be 1 or 10 or 1000 is hypothetical... I’m sorry the discrepancy

2


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

There is no performance Difference Among 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.

Summarized 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 postgresql data in a "real" application (only other dbs)but I’ve made some prototypes on top of it with a few million good records and more than 1 column of text and had no performance problem.

What I can tell you also is that is 6 million records is not no 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 by changing column types. Speculating on what causes performance problems doesn’t usually work out, especially if it’s something that escapes your expertise.

  • 1

    Thanks for the answer Brunobr, in fact they are tables with average of 6 million and my base has 260 tables, if we put that 10 tables has this quantity, would be 60 million, what in a query involving at least 3 to 4 tables would be a beautiful work to be done by the processor, So the subtle difference in performance between types can make a difference there at the end. I am doing tests and more tests since restructuring of Selects and data visualizations and I arrived at a time that I found interesting to know what colleagues say regarding the types of data and their experiences.

  • @Marcelo Saquei. But there are still so many other things more likely to be the cause of your slowness that the guy would be the last possibility I would consider. However if you profiled and exhausted all options, who knows. When solving the problem it would be nice to update the post on whether or not it was the datatype, it would help others and I was curious ;)

Browser other questions tagged database postgresql performance varchar

You are not signed in. Login or sign up in order to post.