Summary:
Original commit: de54adfc9aa80ffb1d331ceb4a48c27a362445d1 / D43383
PostgreSQL (and YSQL by extension) flushes output to the client in chunks. The server does not accumulate the entire output before sending it; instead, it streams results incrementally. This streaming mechanism helps keep memory usage tractable for very large queries. Output is temporarily stored in a buffer called the output buffer before being flushed to the client. Normally, the client is agnostic to the size of this buffer, since communication occurs over a TCP stream and the client only interprets high-level PostgreSQL protocol messages such as RowDescription, DataRow, and others.

However, this abstraction breaks down when encryption is used for server-client communication. TLS segments the stream into records with a maximum size of 16kB (see [RFC 8449](https://datatracker.ietf.org/doc/html/rfc8449)). As a result, the server may send a TLS record larger than 8kB. Some clients, however, cannot decrypt records with payload larger than 8kB, because PostgreSQL does not emit such large records. One example is the Ruby pg client, which has documented issues in this area (see ruby-pg issue [#325](https://github.com/ged/ruby-pg/issues/325#issuecomment-737561270)).

Unlike PostgreSQL, YSQL uses larger output buffers. These large buffers enable aggressive internal retries on serialization failures, which are more critical in YSQL due to the distributed nature of YugabyteDB and its higher exposure to transient failures.

To maintain both high retry efficiency and client compatibility, decouple the concepts of the output buffer and the flush boundary:

1. Maintain a large output buffer.

Do not reduce the output buffer size to 8kB. A smaller buffer would limit YSQL's ability to retry.

2. Flush more aggressively at 8kB boundaries.

This ensures that the size of data flushed follows what postgres itself sends, thus improving compatibility with clients that expect PostgreSQL-like behavior.
Jira: DB-16125

Test Plan:
Jenkins

Manual test reproduction steps.

1. Install pg and optparse packages

```
gem install pg
gem intstall optparse
```

2. Copy this script from Karthik Ramanathan

```
#!/usr/bin/env ruby

require 'optparse'
require 'pg'

class YsqlTest

  def self.main
    url = "postgresql://#{$user}#{$pass}@#{$host}:#{$port}/#{$dbname}?sslmode=" + $options[:ssl]
    puts "Hitting URL: #{url}"
    connection = PG.connect(url)
    puts "Connection successful"
    res = connection.exec("SELECT lpad(''::text, 53, '0') FROM generate_series(1, 1279);")
    rows_count = res.num_tuples
    column_names = res.fields
    col_header = column_names.join(', ')

    puts col_header

    for i in 0..rows_count-1
      row_hash = res[i]
      row_arr = []
      column_names.each do |col|
        row_arr << row_hash[col]
      end
      row = row_arr.join(', ')
      puts row
    end
  end
end

$options = {}
OptionParser.new do |opts|
  opts.banner = "Usage: ysql_test.rb [options]"
  opts.on('-l', '--limit <LIMIT>', 'Record limit') { |v| $options[:limit] = v }
  opts.on('-s', '--sslmode <mode>', 'SSL Mode') { |v| $options[:ssl] = v }
  opts.on('-t', '--target <yb/pg>', 'Target database') { |v| $options[:target] = v }
  opts.on('-h', '--host <ip>', 'Target host') { |v| $options[:host] = v }
  opts.on('-p', '--password <pass>', 'URL encoded password') { |v| $options[:pass] = v }
end.parse!

$host = "127.0.0.1"
$port = 5432
$user = "<user>"
$pass = ""
$dbname = "postgres"

if $options[:target] == "yb"
  $port = 5433
  $user = "yugabyte"
  $dbname = "yugabyte"
end

if $options[:host]
  $host = $options[:host]
end

if $options[:pass]
  $pass = ":" + $options[:pass]
end

YsqlTest.main
```

in file named hang.rb.

3. Generate test certificate files

```
mkdir ~/yb-ssl
bash build-support/generate_test_certificates.sh ~/yb-ssl
```

4. Ensure that the following files exist in ~/yb-ssl

```
~/yb-ssl/node.127.0.0.1.key
~/yb-ssl/node.127.0.0.1.crt
~/yb-ssl/ca.crt
```

If not present, see if they exist with some other name such as ~/yb-ssl/node.127.0.0.2.key and copy/move to ~/yb-ssl/node.127.0.0.1.key.

5. Run the server with TLS enabled.

```
export CERTS=~/yb-ssl
export ENABLE_TLS="use_client_to_server_encryption=true,certs_for_client_dir=$CERTS"
./bin/yb-ctl destroy && ./bin/yb-ctl create --tserver_flags="$ENABLE_TLS"
```

6. Verify that SSL is enabled

```
./bin/ysqlsh "sslmode=disable" <-- fails
./bin/ysqlsh "sslmode=require" <-- connection is successful
```

7. Run the ruby script

```
ruby ../hang.rb -t yb -s require
```

The script hangs on the SELECT without this change.

8. Verify that the script hangs with flush size same as buffer size

```
./bin/yb-ctl restart_node 1 --tserver_flags="$ENABLE_TLS,ysql_output_flush_size=262144"
ruby ../hang.rb -t yb -s require
```

Reviewers: smishra, kramanathan

Reviewed By: kramanathan

Subscribers: jason, yql

Differential Revision: https://phorge.dev.yugabyte.com/D43631