Storing multiple tables by type vs one table 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?
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;