CODEWITHSUNDEEP
postgresqldatabase-design
shayk
shayk

Reputation: 29

postgres many tables vs one huge table

I am using postgresql db. my application manages many objects of the same type. for each object my application performs intense db writing - each object has a line inserted to db at least once every 30 seconds. I also need to retrieve the data by object id. my question is how it's best to design the database? use one huge table for all the objects (slower inserts) or use table for each object (more complicated retrievals)?

Upvotes: 2

Views: 3573

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Logically, you should use one table.

However, so called "write amplification" problem exhibited by PostgreSQL seems to have been one of the main reasons why Uber switeched from PostgreSQL to MySQL. Quote:

"For tables with a large number of secondary indexes, these superfluous steps can cause enormous inefficiencies. For instance, if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row."

Whether this is a problem for your workload, only measurement can tell - I'd recommend starting with one table, measuring performance, and then switching to multi-table (or partitioning, or perhaps switching the DBMS altogether) only if the measurements justify it.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246268

A single table is probably the best solution if you are certain that all objects will continue to have the same attributes.

INSERT does not get significantly slower as the table grows – it is the number of indexes that slows down data modification.

I'd rather be worried about data growth. Do you have a design for getting rid of old data? Big DELETEs can be painful; sometimes partitioning helps.

Upvotes: 0

raykscott
raykscott

Reputation: 21

Tables are meant to hold a huge number of objects of the same type. So, your second option, that is one table per object, doesn't seem to look right. But of course, more information is needed.

My tip: start with one table. If you run into problems - mainly performance - try to split it up. It's not that hard.

Upvotes: 2

Related Questions