Lack of transactions is definitely a key pain point. It changes your code as you need to do fewer things per function when you can't rely on rollback to save you.
But do note that the view in the benchmark does not use transactions. So I *am* comparing 1000 inserts vs. 1000 inserts.
You're comparing 1000 transactions vs. 1000 inserts to a database that does not guarantee durability.
Try running the tests when you wrap the inserts into *one* transaction.
> So I *am* comparing 1000 inserts vs. 1000 inserts.
No you dont.
Postgresql will add an implicit transactions if no one opened an explicit one.
In your special case you are using django without transactions in your code. Djangos documentation states that per default it will wrap every call in one transaction.
So basically you are dealing with 1000 explicit transactions and 1000 inserts on PGs side.
Sorry to say that but your benchmark results are misleading.
Also note that this is a syntetic micro benchmark. In real world there would be many queries within one transaction and also bulk queries to get rid of the client<>db roundtrip.
Comment
Lack of transactions is definitely a key pain point. It changes your code as you need to do fewer things per function when you can't rely on rollback to save you.
But do note that the view in the benchmark does not use transactions. So I *am* comparing 1000 inserts vs. 1000 inserts.
Parent comment
You're comparing 1000 transactions vs. 1000 inserts to a database that does not guarantee durability. Try running the tests when you wrap the inserts into *one* transaction.
Replies
> So I *am* comparing 1000 inserts vs. 1000 inserts.
No you dont.
Postgresql will add an implicit transactions if no one opened an explicit one.
In your special case you are using django without transactions in your code. Djangos documentation states that per default it will wrap every call in one transaction.
So basically you are dealing with 1000 explicit transactions and 1000 inserts on PGs side.
Sorry to say that but your benchmark results are misleading.
Also note that this is a syntetic micro benchmark. In real world there would be many queries within one transaction and also bulk queries to get rid of the client<>db roundtrip.
Perhaps you're right. Perhaps PostgreSQL is faster than the benchmark makes it out to look. But this is how Django+PostgreSQL works together.
Also, like I said before, both "sides" of this benchmark can be optimized further.