Why is `database/sql` in Go so much slower than direct PostgreSQL queries?

Discrepancy in Query Execution Time Between database/sql and Direct Database Queries

In a Go application, you're observing a significant time difference when querying a PostgreSQL database directly via psql compared to using the database/sql package. While running the same query, you've noticed that database/sql takes around 24ms, while psql processes the query in just 1ms.

Understanding the Difference:

The discrepancy can be attributed to two primary factors:

1. Creation and Management of Database Connections:

• psql: When you execute a query directly using psql, it establishes a single, active connection to the database, which remains open for the duration of your session.
• database/sql: In contrast, database/sql utilizes a connection pool, which manages multiple connections to the database. Initially, there are no active connections in the pool. When a query is executed using database/sql, the pool establishes a new connection.

2. Execution of Prepared Statements:

• psql: When you run a query via psql, it's generally a "simple query" without any parameters.
• database/sql: By default, database/sql uses prepared statements. Although your query doesn't have any parameters, the use of $1 in the query causes database/sql to create and execute a prepared statement.

Optimizing Query Performance:

To minimize the time difference, consider the following optimizations:

1. Initialize the Connection Pool:

• Use the db.Ping() method immediately after initializing the database/sql connection to ensure at least one connection is open in the pool. This reduces the latency of the initial connection establishment.

2. Release Connections:

• After executing a query using db.Query, call the rows.Close() method to release the connection back into the pool. Failure to do so keeps connections open, leading to potential resource leaks.

3. Use Simple Queries when Possible:

• If your query does not require parameterization, specify it as a string directly in db.Query. This avoids the overhead of creating and executing prepared statements.

By implementing these optimizations, you should significantly reduce the query execution time using database/sql and bring it closer to the performance of direct database queries.

The above is the detailed content of Why is `database/sql` in Go so much slower than direct PostgreSQL queries?. For more information, please follow other related articles on the PHP Chinese website!