Would comparison of strings to a postgres 'text' type column for equality be a performance bottleneck?

Question

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?

Answer 1

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

Answer 2

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.