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

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?

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

StackExchange Database Administrators Q#216778, answer score: 3