0

I want to store a lot of rows in a table, but I will often want to select only some of them, depending of their type. So I could just add a column type to each row, but then, if I want to select only the rows of one type, I'll have to make a query like SELECT * FROM MyTable WHERE type = 1 for example. But does it mean that the DBMS will have to fetch all the table, even if only a few rows have the type that I want?

Is it better to have a table per type, so I could just SELECT * FROM MyTableType1, without having to use a WHERE?

1
  • One entity - one table. A table per type - forget this forever. You must index it (and may partition it) by type.
    – Akina
    Nov 9 '20 at 16:05
0

As long as all the data have (roughly) the same attributes, and the only difference is the type, storing them in a single table is fine.

To make your query fast, create an index on type. Then two things can happen:

  • there are few enough rows of that type that you get an index scan (or bitmap index scan in PostgreSQL)

  • the type occurs often enough that PostgreSQL opts for a sequential scan

In both cases you won't be much slower than with many small tables.

The final decision might depend on the number of tables (having 10000 tables in a database is not much fun) and the type of query you have:

  • The following can be a static query with parameters:

    SELECT ... FROM mytable WHERE type = 'type1';
    

    while the following requires you to compose an SQL statement for every query:

    SELECT ... FROM mytable_type1;
    
  • If you want to query several types at once, it is nicer to write

    SELECT ... FROM mytable WHERE type IN ('type1', 'type2', 'type3');
    

    than having to write

    SELECT ... FROM mytable_type1
    UNION ALL
    SELECT ... FROM mytable_type2
    UNION ALL
    SELECT ... FROM mytable_type3;
    

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.