24

Yes, I am aware that data normalization should be my priority (as it is).

  1. 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.
  2. 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):

  1. Vehicle table with 65 columns or
  2. 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.

Improve this question
1
  • 1
    One reason do this (vertical partitioning) is if you have queries that deal with the columns from VehicleInterior, other queries that deal with columns from only VehicleTechnical, etc. Or if there are many rows/vehicles that have absolutely no info about (for example) VehicleExtra so instead of many rows with lots of nulls in the one table, you have rows in the rest of the tables and no rows in VehicleExtra Commented Apr 29, 2015 at 19:20

2 Answers 2

Reset to default
35

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:

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.

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:

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.

Improve this answer
2
  • 2
    A single table with a multicolumn index on the few columns to allow index-only scans for the result list might be the best route. (Be aware that column sequence matters in btree indexes.) Joins are not that expensive, but it will still be faster without join. The added storage size and spread-out of data for multiple tables may be the bigger slow-down (more data pages to read for each query). Commented Apr 29, 2015 at 20:26
  • For a 1:N relation you need two separate tables anyway. Except if you cram multiple rows into an array or document type. Then it depends. The principles outlined here apply regardless. Your access patterns and index strategies can make a difference. Ask a new question if you want to be more specific. Commented Jul 31, 2018 at 11:52
1

A select on a single table may be faster. (But not always). If you had all data in a single flat table, once you have found your vehicle you already have all the details. However that may involve more I/O and potentially more delay.

You also lose the efficiency of normalization. For example if 1 car had many models with different options. With normalised data, you could potentially return the entire record set with less IO than if it was in a fact table. Even though the db engine might have to do more computation, it may still be faster.

Is this a reference db of all cars? Or a list of second hand vehicles? Would there be many examples of the same make/model with the same options?

I should qualify my answer as being generic rdbms rather than Postgres-specific. I defer to Erwin's detailed answer specific to Postgres.

Improve this answer
1
  • vehiclemake and vehiclemodel are different tables, so vehicle table has foreign keys of vehiclemake and vehiclemodel. i don't think normalization is a problem here. i understand that select on single table would be faster, however we have a different situation, how will the row with many columns affect the performance and so forth versus tables with less columns (but few tables - 5 of them with joins) Commented Apr 29, 2015 at 19:45

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.