Idle connections / sessions

Oygun Josef schrieb am 12.12.2018 um 10:37:

Is it possible to terminate idle connections/sessions automatically
through a timeout in AWS or do I need to run a periodical cron job
for this?

Postgres version: 9.6.6 Instance: db.t2.micro RAM : 1GB

We are running a microservice architecture using docker with
kubernetes and I can see that every pod on every node that has
connected to the DB still has a idle connection as long as the node
is still active even.

An "idle" connection isn't really a problem.

Long running "idle in transaction" session are a problem however.

To cope with them, you could use idle_in_transaction_timeout:

https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT

It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open
idle connections.

The JDBC driver does not leave any connections "open" or "idle"
It's the application that uses the driver that fails to close them.

Maybe you need to configure your connection pool to release connections when they are idle.

Thomas

Hi Josef,

please avoid cross posting to multiple lists.

I m not a developer, but I think that if you do not want idle connections, you should terminate them on the side they have been created.

If your application leaves the connection open, then you will notice idle connections on Postgres when not in use.

regards,

fabio pardi

Hi Josef,

pg_terminator may help you.

thomas

Le mer. 12 déc. 2018 à 11:02, Fabio Pardi <f.pardi@portavita.eu> a écrit :

Hi,

Sorry for that!

Thank you for the answers, this is good but do you know if there is a way do to it through AWS console with some kind of configuration instead?

Reason for that is the we use terraform scripts to create and keep state of our instances so pg_terminator would require me to add every new db props to it.

/Josef

Från: Thomas Poty [mailto:thomas.poty@gmail.com]
Skickat: den 12 december 2018 11:11
Till: pgsql-general@lists.postgresql.org
Ämne: Re: Idle connections / sessions

Hi Josef,

pg_terminator may help you.

thomas

Le mer. 12 déc. 2018 à 11:02, Fabio Pardi <f.pardi@portavita.eu<mailto:f.pardi@portavita.eu>> a écrit :
Hi Josef,

please avoid cross posting to multiple lists.

I m not a developer, but I think that if you do not want idle connections, you should terminate them on the side they have been created.

If your application leaves the connection open, then you will notice idle connections on Postgres when not in use.

regards,

fabio pardi

On 12/12/2018 10:37, Oygun Josef wrote:
Hi,

Is it possible to terminate idle connections/sessions automatically through a timeout in AWS or do I need to run a periodical cron job for this?

Postgres version: 9.6.6
Instance: db.t2.micro
RAM : 1GB

We are running a microservice architecture using docker with kubernetes and I can see that every pod on every node that has connected to the DB still has a idle connection as long as the node is still active even.

It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open idle connections.

Josef Oygun

Sorry but i don't know (i am not familiar with aws) ....
Maybe this will help: If you want pg_terminator can run on postgresql
server.

Regards
Thomas

Le mer. 12 déc. 2018 à 12:20, Oygun Josef <josef.oygun@scania.com> a écrit :

I am running explain analyze cost on a SQL which reads from two large
tables (122mil and 37 mil). The query is an UPDATE SQL where we use
derives table in the from clause and then join it back to the table
being updated.
The explain analyze cost itself is taking forever to run. It is running
for the last 1 hr. Does that actually run the SQL to find out the
impact of I/O (as indicated in COSTS). If not, what can cause it to run
this slow.__

On 12/12/2018 04:37 PM, Ravi Krishna wrote:

I am running explain analyze cost on a SQL which reads from two large
tables (122mil and 37 mil).  The query is an UPDATE SQL where we use
derives table in the from clause and then join it back to the table being
updated.

The explain analyze cost itself is taking forever to run. It is running
for the last 1 hr. Does that actually run the SQL to find out the impact
of I/O (as indicated in COSTS).

Yes.

https://www.postgresql.org/docs/9.6/sql-explain.html

"The ANALYZE option causes the statement to be actually executed, not only
planned."

If not, what can cause it to run this slow.

--
Angular momentum makes the world go 'round.

On 2018-12-12 17:37:47 -0500, Ravi Krishna wrote:

I am running explain analyze cost on a SQL which reads from two large
tables (122mil and 37 mil). The query is an UPDATE SQL where we use
derives table in the from clause and then join it back to the table
being updated.
The explain analyze cost itself is taking forever to run. It is running
for the last 1 hr. Does that actually run the SQL to find out the
impact of I/O (as indicated in COSTS). If not, what can cause it to run
this slow.__

Please do not hijack other threads by replying to a message and changing
the subject. Just send a new mail to
pgsql-general@lists.postgresql.org, or whatever list you want to send an
email to.

Thanks,

Andres

Please do not hijack other threads by replying to a message and
changing> the subject. Just send a new mail to
pgsql-general@lists.postgresql.org, or whatever list you want
to send an> email to.

I am truly sorry and this will not be repeated. I was just lazy.
I guess this would break threaded views , because on non threaded views
it does notmatter.