CODEWITHSUNDEEP
postgresql
Johnny
Johnny

Reputation: 133

Will huge table entries slow down query performance?

Let's say I have a table persons that looks like this:

|id | name | age | |---|------|-----| |1 |foo |21 | |2 |bar |22 | |3 |baz |23 |

and add a new column history where I store a big JSON blob of, let's say ~4MB.

|id | name | age | history | |---|------|-----|----------| |1 |foo |21 |JSON ~ 4MB| |2 |bar |22 |JSON ~ 4MB| |3 |baz |23 |JSON ~ 4MB|

Will this negatively impact queries against this table overall?

What about queries like:

Are there any other side effects like various growing caches etc. that could slow down database performance overall?

Upvotes: 2

Views: 1146

Answers (2)

Luke Franklin
Luke Franklin

Reputation: 355

depending on how many transactions and the type of transactions (Create, Read, Update, Delete) that are using this table there could be performance issues.

if you are updating the history lots, you will be doing a lot of updates transactions which will cause the table to reindex each update transaction.

let say table persons is called every time a user logs in and it also updates the history for that user. you are doing a select and update, if this is happening a lot it will cause lots of reindexing and could cause issues when users are logging on and other users are also updating history.

a better option would be to have a separate table for personupdates with a relation to the person table.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246788

The JSON attribute will not be stored in the table itself, but in the TOAST table that belongs to the table, which is where all variable-length entries above a certain size are stored (and compressed).

Queries that do not read the JSON values won't affect performance at all, since the TOAST entries won't even be touched. Only if you read the JSON value performance will be affected, mostly because of the additional data read from storage and transmitted to the client, but of course the additional data will also reside in the database cache and compete with other data there.

So your guess is right.

Upvotes: 3

Related Questions