0

I'm seeing higher than expected latency when I query postgres from my Go application.

Below is a working example, using a publicly accessible DB.

When I run the below code, I get a response in 800ms. When I run the same query directly in my sql editor, the response is 120 ms. I'm not sure what is causing such a large difference.

I'm fairly new to GO, so I'm guessing that I'm missing something fundamental, but I've tried quite a few things and can't figure out the cause of the latency.

I tried to follow the PGX 'todo' example for my queries. https://github.com/jackc/pgx/blob/master/examples/todo/main.go

Anyone have ideas on what I should be doing differently?

   package main

   import (
      "context"
      "fmt"
      "log"
      "os"

      "github.com/jackc/pgx/v4"
   )

  func main() {
    
     //these are public credentials: https://rnacentral.org/help/public-database
     urlString := "postgres://reader:NWDMCE5xdipIjRrp@hh-pgsql- 
     public.ebi.ac.uk:5432/pfmegrnargs" 

     conn, err := pgx.Connect(context.Background(), urlString)

     if err != nil {
       fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
       os.Exit(1)
     }

     defer conn.Close(context.Background())

     var upi string
     err = conn.QueryRow(context.Background(), "SELECT upi FROM xref LIMIT 1").Scan(&upi)

    if err != nil {
       fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
       os.Exit(1)
    }

    fmt.Println(upi)
}
samester
  • 21
  • 4
  • 2
    In Go you are timing not just the query but also establishing of the connection, that is what takes most of the time. In your SQL editor you are timing only the query, the editor is already connected. Also note that things like `conn` (at least generally, not sure how `pgx` works) need to be created only once, at program startup, and then can be safely used by multiple goroutines through the program's lifetime. – mkopriva Mar 07 '22 at 20:53
  • I've closed your question as duplicate. Please take a look at the linked question & answer. Although the linked duplicate is about `database/sql` and `lib/pq`, the answer *should* apply to `pgx` as well. If the duplicate does not successfully resolve your problem please let me know so by writing a comment and I'll reopen your question. – mkopriva Mar 07 '22 at 21:06
  • 2
    `pgx` does differ from `database/sql` somewhat (except when using [`github.com/jackc/pgx/v4/stdlib`](https://pkg.go.dev/github.com/jackc/pgx/v4@v4.15.0/stdlib)). "The `*pgx.Conn` returned by `pgx.Connect()` represents a single connection and is not concurrency safe" so for more complex programs you will probably want to use a [connection pool](https://github.com/jackc/pgx/wiki/Getting-started-with-pgx#using-a-connection-pool). The comments above are still relevant but as the linked answer discusses connection pooling I thought it was worth noting the differences. – Brits Mar 07 '22 at 21:37
  • thanks mkopriva and Brits. You were right. In my actual code, I was accidentally opening/closing the connection in one of my handlers, so that was the issue. Didn't think it would have that large of an impact. Got it all working now. Thanks! – samester Mar 07 '22 at 22:38

0 Answers0