Reduce network bandwidth with better statement names
- Dominant language
- Go
- Stars
- 14.3k
- Forks
- 1.1k
- Avg merge
- 6d 9h
- Merged PRs (30d)
- 11
Description
At Plato we have a lot of micro services that have about a dozen sql queries each and i noticed two things looking at a wireshark tcp dump of pgx:
1. if its calling a function, the statement name pgx creates can actually be longer than the original sql
2. the statement name includes the full hash of the sql which ends up being pretty long
The reason for investigating was a change in logic (with same request rate) dropped network traffic to postgres by 3x which was surprising and I was trying to understand what was being sent on the wire.
---
For 1:
Would it make sense to skip a prepared statement if the sql is just a call to a function such as:
```
SELECT my_func();
or
SELECT * FROM my_func();
```
instead of sending the statement name each time:
```
stmtcache_88f8794f3ae768b41c7fa5f7902be85a93ac6b97bab5cc06
```
For 2:
Can we make the stmtcache injectable via config (or maybe a sql => name mapper) so that smaller names can be used and save the network bytes.
i.e. from:
```
stmtcache_88f8794f3ae768b41c7fa5f7902be85a93ac6b97bab5cc06
```
to logic using an incrementing number like:
```
s1
```
---
I noticed that the logic in `StatementName` method is almost duplicated in the conn class here though not sure what the difference is https://github.com/jackc/pgx/blob/e4a063e7cb0fe9dd022fb1171d7655f6e837c98e/conn.go#L340
Also I noticed that all the column names are returned when preparing the statement and not sure if that is something that can be skipped or what it is used for.
Contributor guide
Research direction
Start by comparing the StatementName method with the nearly duplicated logic in conn.go around the referenced line. Investigate the prepared-statement traffic and the alternatives proposed for function calls and configurable statement names. Done requires choosing and clearly specifying one supported approach, then validating its effect on statement naming and network usage.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, postgresql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100