0

Referenced from this question, the most popular answer does not mention sharding. As far as I know, postgresql will acquire auto-inc lock every time it inserts, so I think sharding can improve insertion performance. Am I right? If sharding can really improve performance, how does it benefit and cost compared other methods?

4
  • What do you mean with "auto-inc lock"? Oct 20, 2020 at 6:32
  • @a_horse_with_no_name sorry, auto-inc lock is a term in mysql.Will postgresql have similiar locking behaviors when inserting records to generate sequence value? Oct 20, 2020 at 6:47
  • No, there will be no "select max(...)" for sequences in Postgres. While there is a lock when obtaining a sequence value, I have never seen this to be a bottleneck (but if it does, it can be alleviated by increasing the CACHE size of the sequence) Oct 20, 2020 at 6:59
  • @a_horse_with_no_name Thanks for the explanation, so do you think the insert operations will benefit from sharding? Oct 20, 2020 at 7:04

1 Answer 1

1

As has been clarified in the comments, accessing a sequence will not be a bottleneck in PostgreSQL.

If you are faster inserting into a sharded database depends on where the bottleneck is.

If it is disk I/O or CPU on the database, you will probably be faster spreading the workload across several databases (sharding), as long as they have independent storage and network bandwidth is not a bottleneck.

If the bottleneck is on the client side, you probably won't gain much.

2
  • If the bottleneck is I/O wouldn't be adding additional disks to the server (RAID) also be an option (instead of the more complex sharding setup)? Oct 20, 2020 at 8:14
  • Yes, absolutely. I tried to answer the question "can sharding help insertion performance", not "is sharding my best option". Oct 20, 2020 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.