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"?– user1822Oct 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?– spike 王建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)– user1822Oct 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?– spike 王建Oct 20, 2020 at 7:04
Add a comment
|
1 Answer
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.
answered Oct 20, 2020 at 7:31
-
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)?– user1822Oct 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