Many columns vs few tables - performance wise Yes, I am aware that data normalization should be my priority (as it is).

I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth,  in total 65.
Now, I can divide that and have a Vehicle table, VehicleInterior, VehicleExterior, VehicleTechnical, VehicleExtra (all one-to-one with main Vehicle table).

Let's assume I'll have about 5 million rows (vehicles).
On SELECT with a WHERE clause: Will the performance be better searching through (both cases indexed at least on IDs):

Vehicle table with 65 columns or
Vehicle table with JOINS on four other tables (all with 5 million rows) to return all the data related to Vehicle?

(As per database engine, consider PostgreSQL and/or MySQL).
Truly appreciate any detailed insights you might have from your previous experience?
Updates will be rare if any, and selects will be mostly for all columns (vehicle details page) and main info (few columns) for search results list, and in fact maybe the best solution would be two tables: one with main info (few columns) and the other table with rest of the columns.
Assuming we are talking about 1:1 relationships among all tables.

Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate (redundant) index on each of these columns ... Size does matter for performance.

This is even true if many columns are NULL in most rows because NULL storage is very cheap:


Configuring PostgreSQL for read performance


While retrieving all columns a single table is substantially faster than 5 tables joined together. It's also much simpler. Five tables may be tricky to join if not all rows are present in all tables. With WHERE conditions targeting a single table, it's easy enough to append other tables with LEFT JOIN. Not as trivial if you have predicates on multiple tables ...

Vertical partitioning may still improve performance of certain queries. For example, if 90 % of your queries retrieve the same 5 columns out of the 65 available, this would be faster with a table just holding these 5 columns.


What is retrieved from disk during a query? (my answer)


OTOH, you might be able to cater for such queries on a few selected columns with a "covering" index allowing for index-only scans.

Another candidate for vertical partitioning: If you have lots of updates on just a few columns, while the rest hardly ever changes. It might be considerably cheaper to split rows in such a case, since Postgres writes a new row version for every update. There are exceptions for big values stored out-of-line ("TOASTed"). More details:


What is retrieved from disk during a query? (Daniel's answer)
Update all columns from another table


It really depends on the complete situation. If in doubt, go with the simple solution of having a single table, especially if it portraits reality well: In your example, those are all attributes of a car and make sense together.