PostgreSQL Sees Up To 4x Query Performance With SIMD-Optimized JSON Escaping
([Programming] 5 Hours Ago
Optimize JSON Escaping Using SIMD)
- Reference: 0001483761
- News link: https://www.phoronix.com/news/PostgreSQL-Opt-JSON-Esc-SIMD
- Source link:
For those making use of JSON data with the PostgreSQL database server, now merged code to make use of SIMD for JSON escaping has shown up to a 4x improvement for query performance when dealing with lots of JavaScript Object Notation data.
Since PostgreSQL 16 has been [1]a lot of SIMD optimizations on both x86_64 and ARM . Now merged to PostgreSQL Git for what is PostgreSQL 18 development with v17 already branched and in beta phase, the SIMD support has been expanded to help with JSON escaping.
David Rowley who pursued this SIMD-optimized JSON escaping explained in [2]the commit merged yesterday:
"Here we adjust escape_json_with_len() to make use of SIMD to allow processing of up to 16-bytes at a time rather than processing a single byte at a time. This has been shown to speed up escaping of JSON strings significantly.
Escaping is required for both JSON string properties and also the property names themselves, so this should also help improve the speed of the conversion from JSON into text for JSON objects that have property names 16 or more bytes long.
Escaping JSON strings was often a significant bottleneck for longer strings. With these changes, some benchmarking has shown a query performing nearly 4 times faster when escaping a JSON object with a 1MB text property. Tests with shorter text properties saw smaller but still significant performance improvements. For example, a test outputting 1024 JSON strings with a text property length ranging from 1 char to 1024 chars became around 2 times faster."
So nearly four times faster when dealing with large sets of JSON data or around two times with smaller sizes. Not too surprising as with the "simdjson" project we've also seen [3]how AVX/AVX-512 can make JSON parsing extremely fast and continues to show impressive wins with [4]simdjson benchmarks .
Great seeing the continued SIMD efforts by PostgreSQL!
[1] https://www.phoronix.com/news/PostgreSQL-16-Released
[2] https://github.com/postgres/postgres/commit/ca6fde92258a328a98c1d9e41da5462b73da8529
[3] https://www.phoronix.com/review/simdjson-avx-512
[4] https://openbenchmarking.org/test/pts/simdjson#results
Since PostgreSQL 16 has been [1]a lot of SIMD optimizations on both x86_64 and ARM . Now merged to PostgreSQL Git for what is PostgreSQL 18 development with v17 already branched and in beta phase, the SIMD support has been expanded to help with JSON escaping.
David Rowley who pursued this SIMD-optimized JSON escaping explained in [2]the commit merged yesterday:
"Here we adjust escape_json_with_len() to make use of SIMD to allow processing of up to 16-bytes at a time rather than processing a single byte at a time. This has been shown to speed up escaping of JSON strings significantly.
Escaping is required for both JSON string properties and also the property names themselves, so this should also help improve the speed of the conversion from JSON into text for JSON objects that have property names 16 or more bytes long.
Escaping JSON strings was often a significant bottleneck for longer strings. With these changes, some benchmarking has shown a query performing nearly 4 times faster when escaping a JSON object with a 1MB text property. Tests with shorter text properties saw smaller but still significant performance improvements. For example, a test outputting 1024 JSON strings with a text property length ranging from 1 char to 1024 chars became around 2 times faster."
So nearly four times faster when dealing with large sets of JSON data or around two times with smaller sizes. Not too surprising as with the "simdjson" project we've also seen [3]how AVX/AVX-512 can make JSON parsing extremely fast and continues to show impressive wins with [4]simdjson benchmarks .
Great seeing the continued SIMD efforts by PostgreSQL!
[1] https://www.phoronix.com/news/PostgreSQL-16-Released
[2] https://github.com/postgres/postgres/commit/ca6fde92258a328a98c1d9e41da5462b73da8529
[3] https://www.phoronix.com/review/simdjson-avx-512
[4] https://openbenchmarking.org/test/pts/simdjson#results
uid313