Subject: [pgjdbc/pgjdbc] PR #3641: feat: use PreparedStatement for DatabaseMetaData.getCrossReference, getImportedKeys, getExportedKeys

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 28 May 2025 16:03:49 +0000

See https://github.com/pgjdbc/pgjdbc/issues/3511

At best, we should avoid building the same query again and again, however, let's start with just using `PreparedStatements`.

Benchmarks with PG 16.3

Note: `ps.closeOnCompletion()` enables server-prepared statements even though the prepared statements are never closed explicitly (the APIs return `ResultSet`, and there's no way to close the statement other than `closeOnCompletion`).

## Before

```
Benchmark                                     Mode  Cnt   Score   Error  Units
DatabaseMetadataBenchmark.getColumns_all      avgt   15   1,156 ± 0,184  ms/op
DatabaseMetadataBenchmark.getColumns_named    avgt   15   1,085 ± 0,079  ms/op
DatabaseMetadataBenchmark.getCrossReferences  avgt   15  42,108 ± 0,536  ms/op
DatabaseMetadataBenchmark.getTables           avgt   15   0,423 ± 0,018  ms/op
```

## After: `String` -> `StringBuilder`

```
Benchmark                                     Mode  Cnt  Score   Error  Units
DatabaseMetadataBenchmark.getColumns_all      avgt   15  0,794 ± 0,041  ms/op
DatabaseMetadataBenchmark.getColumns_named    avgt   15  0,820 ± 0,056  ms/op
DatabaseMetadataBenchmark.getCrossReferences  avgt   15  4,128 ± 0,156  ms/op
DatabaseMetadataBenchmark.getTables           avgt   15  0,489 ± 0,041  ms/op
```

## After: `String` -> `StringBuilder`; `closeOnCompletion`

```
Benchmark                                     Mode  Cnt  Score   Error  Units
DatabaseMetadataBenchmark.getColumns_all      avgt   15  0,774 ± 0,020  ms/op
DatabaseMetadataBenchmark.getColumns_named    avgt   15  0,797 ± 0,034  ms/op
DatabaseMetadataBenchmark.getCrossReferences  avgt   15  0,142 ± 0,007  ms/op
DatabaseMetadataBenchmark.getTables           avgt   15  0,316 ± 0,050  ms/op
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 28 May 2025 16:34:59 +0000

Fair, but I think we need to understand the root cause of the regression.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 30 May 2025 16:54:15 +0000

Any thoughts?
I think we could merge this and release yet another version to get the perf issue fixed.

The core changes here are like `String->StringBuilder`. It would be great to add `try-with-resources`, however I don't like to mix many changes into a single PR.