Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting "bigger" data into PostgreSQL makes the system faster?

So, I witnessed the following behaviour while using PostgreSQL.

I have a table like this: (id INTEGER ..., msg VARCHAR(2000)) and then I run two programs A and B that do the exact same thing, namely doing 20000 insertions and then 20000 retrievals (based on their id). The only difference is that program A does insertions with messages containing 2000 characters while B just inserts messages containing at most 10 characters.

The thing is that the average time of all the insertions and retrievals in A is always about ~15ms less than in B which doesn't really make sense, since A is adding "bigger" data.

Any ideas or hints on why this could be happening? Could it be that when not using all the characters of the msg the system uses the rest of the space for other purposes and therefore if msg is full the system is faster?

Based on @Dan Bracuk comment. I save the time on different events and realized that the following happens, in program A there quite a few times that insertions are really really fast while in program B this is never the case and that's why on average A is faster than B but I cannot explain this behaviour either.

like image 308
insumity Avatar asked Nov 10 '13 19:11

insumity


People also ask

How make PostgreSQL query run faster?

Some of the tricks we used to speed up SELECT-s in PostgreSQL: LEFT JOIN with redundant conditions, VALUES, extended statistics, primary key type conversion, CLUSTER, pg_hint_plan + bonus.

How many inserts per second can Postgres handle?

If you're simply filtering the data and data fits in memory, Postgres is capable of parsing roughly 5-10 million rows per second (assuming some reasonable row size of say 100 bytes). If you're aggregating then you're at about 1-2 million rows per second.

What is PostgreSQL performance tuning?

PostgreSQL tries to hold the most frequently accessed data in memory to make performance improvements based on how your queries are performed and the configuration that you give it. But we'll return to memory-based performance optimization later. Separating the application from the database.


1 Answers

I can't reproduce this without more detail about your setup and your programs, so the following is just an educated guess. It's conceivable that your observation is due to TOAST. Once a text field exceeds a certain size, it is stored in a physically separate table. Therefore, the main table is actually smaller than in the case where all the text values are stored inline, and so searches could be faster.

like image 191
Peter Eisentraut Avatar answered Sep 28 '22 10:09

Peter Eisentraut