0

After reading about performance a bit in sql I notice that it is quite often suggested to add indices for foreign keys - as often data is looked up through the foreign key. Similarly PostgreSQL seems to automatically index the primary column.

This has me wondering: should I be liberal with adding indices (CREATE INDEX ...)?

As two examples, considering data in our database has serial/integer primary key. However for communication with the external world we generate a random textual key that is used as reference. The primary keys are kept internal only (for foreign keys and management from our application).

Example:

CREATE TABLE public.address
(
    id serial NOT NULL,
    key text NOT NULL,
    customer integer NOT NULL,
    address text,
    zipcode text,
    municipality text,
    country text,
    PRIMARY KEY (id),
    UNIQUE (key),
    FOREIGN KEY (id)
        REFERENCES public.customer (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

As this key will be the main entry point of lookups, there soould be a index on that column, right?


Secondly, sometimes there need to be administrative functionality on users. This is done from the frontend by providing the username. (IE: remove a user, link a user to a customer).

However this is by far not the primary way to access this information nor is it really time critical.

Should an index be created (on the username column) in such a case as well? Or is this more dependent on other factors? -- What is the disadvantage of adding this (and other) index?

1 Answer 1

0

As this key will be the main entry point of lookups, there should be a index on that column, right?

Correct, and it ought to be unique as well.

... administrative functionality on users. This is done from the frontend by providing the username ...

Just because the User sees the Username does not mean you have to use the Username to access your Data. You can pass the "key" value to your Application (as well) and use it "under the covers". An overly-simple example (you'd never do this in practice, but):

<select><!-- User List -->
  <option value='key1'>User 1</option>
  <option value='key2'>User 2</option>
  <option value='key3'>User 3</option>
  . . . 
</select>

Of course, the one time you can't do this is when you're creating a new User (and have no "key" to use!).

However, if you do decide to do lookups against Username, then yes, it should have an index on it.
Try finding a single username in a 100 million rows table without an index - it may take "some time".

Indexes are there to make "lookups" faster - I put that in quotes because it might be a "where"-type lookup (where you're searching for a particular record) or it may be a "join"-type lookup (where it's happening internally). Remember, the database will join tables together however it thinks is best and that may not be the same way that you or I might think about things. Give it a nice, well-discriminating, index as an option to work with and it might do so, from time to time.

The overhead of indexes is that they need updating, same as the base table - they do contain the same data after all. An index on Username will have the Username value, plus a "pointer" to the record that it identifies, plus some internal "pointers" to support the index structure itself. Change the Username value in the base table and you have to change it in the index as well which, in extreme cases, might lead to some serious restructuring of that index (say, "Algenon" changes their [user]name to "Zebediah"). It's all managed by the database and you shouldn't notice it; just one to be aware of.

1
  • So basically the question is: "how many updates in regarding to lookups happen - on the specific column"
    – paul23
    Commented Mar 8, 2023 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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