3

I have a Postgresql database with a column type of text. Coming from a SQL Server background, is this equivalent to the (n)varchar(max) type?

My specific example/reason for asking this question, is that I have a table with a column of type text in which I would like to store unique values. The table is updated according to regular CSV imports, meaning that for every row in the CSV the the text column is checked for an existing entry (a column-value in the CSV), and if none is found then that value is inserted into the table.

My understanding is that this could mean checking thousands (or maybe hundreds of thousands) of text values against other text values. I imagine this to be incredibly inneficient. Is this the case?

3

Yes, text is (roughly) equivalent to varchar(max)

Comparing text values is not less efficient than comparing varchar values in Postgres as under the hood they are absolutely identical. So the efficiency of the comparison is related to the length of the values. I don't expect that to be any slower than your current implementation using varchar(max) in SQL Server.

If you want to enforce uniqueness on a column, create a unique index on it. Then you can use insert ... on conflict do nothing to efficiently insert new values and at the same time validating that they are unique.

However: there is a technical limit on how long an index entry is allowed to be which is roughly 2700 byte. You didn't mention how long your values are, but the index might not work for you then.

Consider also this answer https://dba.stackexchange.com/a/69164

  • This is a decision on how to normalize - for answers to survey questions that are likely to be very repetitive. Most checks should fail and therefore not result in a write. However the alternative is to not normalize this at all, which I think would be better – Zach Smith Sep 5 '18 at 19:46
  • Thank you for your feedback - particularly the on conflict do nothing bit – Zach Smith Sep 5 '18 at 19:47
1

There is no clear answer to this. It will be the bottleneck, unless something else takes enough time to be the bottleneck instead.

If the load process is write intensive, the overhead of writing will generally be the bottleneck. But if almost every csv row fails the uniqueness check and so doesn't result in a write, that won't be the case.

If almost every csv row does fail, and if you have an index on the text column, then the general overhead of searching the index (stepping from page to page while descending from the root to the leaf, especially if the entire path is not already in memory) will probably be the bottleneck.

If you don't have an index and so need to full-scan the table for every proposed insertion, then it is quite likely that the text equality operation can be the bottleneck. This is especially the case if your strings have long common prefixes and only differ in characters towards the end of the strings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.