pg startup slower than pgx obviously

[i11010520]

[pg.zig]:

    const _c1 = std.time.milliTimestamp();
    const uri = try std.Uri.parse("postgresql://...?sslmode=require");
    var pool = try pg.Pool.initUri(allocator, uri, 5, 10_000);
    defer pool.deinit();
    const _c2 = std.time.milliTimestamp();

    const querier = Users.init(allocator, pool);
    const _c3 = std.time.milliTimestamp();
    const us = try querier.webauthnUserFindByName("a");
    const _c4 = std.time.milliTimestamp();
    defer us.deinit();

    std.debug.print("c2: {any} ms\n", .{_c2 - _c1});
    std.debug.print("c3: {any} ms\n", .{_c3 - _c2});
    std.debug.print("c4: {any} ms\n", .{_c4 - _c3});

[go/pgx]:

	_c1 := time.Now()
	DbCtx := context.Background()
	dbConn, err := sql.Open("pgx", "postgresql://...?sslmode=require")
	if err != nil {
		panic(fmt.Sprintf("failed to init db: %v", err.Error()))
	}
	_c2 := time.Since(_c1)

	_c22 := time.Now()
	Q := model.New(dbConn)
	_c3 := time.Since(_c22)
	_c33 := time.Now()
	if us, err := Q.WebauthnUserFindByName(DbCtx, "a"); err != nil {
		panic(err.Error)
	} else {
		fmt.Println(fmt.Sprintf("id: %v, phrase_id: %s\n", us.ID, us.PhraseID))
	}
	_c4 := time.Since(_c33)
	fmt.Println(fmt.Sprintf("c2: %v\nc3: %v\nc4: %v\n", _c2, _c3, _c4))

result comparison:

[pg.zig]
>>>pool/conn init: 10350 ms
query handler: 0 ms
one query: 2456 ms

[go/pgx]
>>>pool/conn init: 3.455708ms
query handler: 125ns
one query: 2.758706417s

[karlseguin]

The pool immediately opens 5 connections. In the pgx example, you aren't using a pool. If you use pgxpool, it doesn't eagerly open connections. I believe this is a more fair test:

package main

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    conf, err := pgxpool.ParseConfig(URI)
    if err != nil {
        panic(err)
    }
    c1 := time.Now()
    dbpool, err := pgxpool.NewWithConfig(context.Background(), conf)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
        os.Exit(1)
    }
    defer dbpool.Close()
    dbpool.Acquire(context.Background())
    dbpool.Acquire(context.Background())
    dbpool.Acquire(context.Background())
    dbpool.Acquire(context.Background())
    dbpool.Acquire(context.Background())
    fmt.Println(time.Since(c1))

For me, pgx is still a bit faster, but it's 1.5 vs 1.7 seconds.

If you still think this is an issue, I'm wondering if you're able to try without TLS (I realize this isn't practical to do, but I'm curious how much of the overhead comes from establishing TLS)? What's the ping to your DB server - opening the connection + the TLS handshake) * Pool size is going to take time.

If your request is to speed it up, I can think of two things pg.zig might be able to do:
1 - Lazily open the connections (like pgxpool does)
2 - Try to open the connections in parallel

[i11010520]

The pool immediately opens 5 connections. In the pgx example, you aren't using a pool. If you use pgxpool, it doesn't eagerly open connections. I believe this is a more fair test:

package main

import (
"context"
"fmt"
"os"
"time"

"github.com/jackc/pgx/v5/pgxpool"

)

func main() {
conf, err := pgxpool.ParseConfig(URI)
if err != nil {
panic(err)
}
c1 := time.Now()
dbpool, err := pgxpool.NewWithConfig(context.Background(), conf)
if err != nil {
fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
os.Exit(1)
}
defer dbpool.Close()
dbpool.Acquire(context.Background())
dbpool.Acquire(context.Background())
dbpool.Acquire(context.Background())
dbpool.Acquire(context.Background())
dbpool.Acquire(context.Background())
fmt.Println(time.Since(c1))
For me, pgx is still a bit faster, but it's 1.5 vs 1.7 seconds.

If you still think this is an issue, I'm wondering if you're able to try without TLS (I realize this isn't practical to do, but I'm curious how much of the overhead comes from establishing TLS)? What's the ping to your DB server - opening the connection + the TLS handshake) * Pool size is going to take time.

If your request is to speed it up, I can think of two things pg.zig might be able to do: 1 - Lazily open the connections (like pgxpool does) 2 - Try to open the connections in parallel

When change pgx demo code to acquire connections, its latency become much longer:

 pool/conn init: 17.191412833s
 query handler: 333ns
 one query: 3.442379042s

The startup efficiency is not core issue, mainly of user experience.
With regard to the actual data query,
the above contrast shows that pg.zig performs better than pgx, especially when make it eagerly open 5 connections.

[i11010520]

If you still think this is an issue, I'm wondering if you're able to try without TLS (I realize this isn't practical to do, but I'm curious how much of the overhead comes from establishing TLS)? What's the ping to your DB server - opening the connection + the TLS handshake) * Pool size is going to take time.

If your request is to speed it up, I can think of two things pg.zig might be able to do:
1 - Lazily open the connections (like pgxpool does)
2 - Try to open the connections in parallel

👍
These two features should enhance the development experience of pg.zig effectively.

[karlseguin]

Lazily initializing the connections is now controlled by the new connect_on_init_count pool option. This can be set to any vaue <= opts.size. It defaults to null which is the same as setting it to size (thus, the default behavior is unchanged).