I've seen the url like this


many times. There's no ID embedded in it. Therefore, in a db it must be defined as "unique article_url varchar(NNN)"

While it's good for SEO, doesn't this have too much overhead for a database: there's an index on "article_url" and it's very long.

Or is there a trick in regards to a db I'm not aware of?

  • 4
    What precisely is your concern? That an index for a non-numeric column will consume lots of storage? Or that lookup for a large key will be slower, even with an index? “Too much overhead” also implies there is a specific point where “acceptable overhead” becomes “too much overhead”. Where do you place that point? And how many rows do you have in mind. Hundreds? Thousands? Millions? Billions? – amon Dec 3 '17 at 11:23
  • Is a database even involved? – 8bittree Dec 5 '17 at 19:19

Is your url a primary key ?

If you want to use a long url as unique ID for a table, you would need to use that ID in other tables as well. Joins risk to become less efficient.

If this is the case, I'd strongly suggest to keep a single copy of the url and add an additional ID (either a sequential number, or some hash code of the url).

Note: PostgreSQL from time too time uses a hash join to optimise joins involving large strings, but this might be inefficient in your case. The extra ID avoids this issue completely.

Are you concerned with determine uniqueness of an url that is not a primary key ?

Here the unique index is just the way to go.

Alternatively, you could keep next to the url a column with the hash code of the url: hash_code are good unicity indicators. But they are not perfect, and I think that performance differences would matter only if you would have url that are in the kilobyte range.

Are you concerned about storage ?

URL could be huge in theory, but are in practice limited to around 2000 characters. This should in anyway fit into the varchar sizes allowed by PostgreSQL.

You should then not worry about the size, as the PostgreSQL documentation reminds:

Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

  • it's not a problem at all to have an indexed varchar of, say, size 100 then? – Rakori Dec 4 '17 at 11:03
  • it's not a problem at all to have an indexed varchar of, say, size 100 then? – Rakori Dec 5 '17 at 23:21
  • @Rakori no, not at all ! – Christophe Dec 6 '17 at 5:47

You expressed concern that the index on that column is very long, meaning that it's the space occupied by the index, that worries you.

In a similar situation on an Oracle database, we replaced the normal index by a function-based one, taking the first 20 characters of the column value: we indexed SUBSTR20(COLUMN) instead of COLUMN, with SUBSTR20 being a user-defined function.

That greatly helped to reduce the index size, but we had to rewrite all queries to

    SUBSTR20(COLUMN) = SUBSTR20('abcdefghijklmnopqrstuvwxyz') 
    COLUMN = 'abcdefghijklmnopqrstuvwxyz'

to make the database use the index (first part) and check the results for also matching the remaining characters (second part).

And handling LIKE comparisons correctly became a non-trivial challenge.

The query performance we experienced was good (comparable to the full-column index), as the main selection was done by the substring index.

So, this solution reduces space consumption, but comes at the cost of more complex SQL queries.

Of course, the assumption is that the first 20 characters are significant enough to reduce the result set to just a few candidates (which was true in our case).

To generalize this approach, you are of course free to do whatever you want in the indexing function, e.g. compute some hash value.

  • is it possible to do that in PostgresSql? – Rakori Dec 3 '17 at 15:42
  • I don't know if PostgresSql supports function-based indexes, I never used that database. – Ralf Kleberhoff Dec 3 '17 at 15:44

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.