Flask with Postgres - Database is Crashing after few APIs call

Question

I have created the flask app with nginx running in a docker container and postgresql database in different container

I created like 10 pages, in each page max 3 or 4 are calling, and the response are getting normally and the page is showing the results

But, in one of the page, I used to call 7 APIs to get the data from flask, in each API, multiple queries are made to postgresql database.

All 7 APIs are working without any issue - tested, there is no error will occur even in the queries or in the logic.

What happens is - when I open that one particular page, the first 5 apis are getting the response without any issue, the last 2 apis are not getting the response and got '504 Gateway Time-out' error

when checking in the logs, I got this error -

    Traceback (most recent call last):
    File "./database_functions.py", line 7097, in get_status1
        results = session.query(Customer.id).filter(Customer.category_id==category_id).all()
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3178, in all
        return list(self)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3334, in __iter__
        return self._execute_and_instances(context)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3359, in _execute_and_instances
        result = conn.execute(querycontext.statement, self._params)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
        return meth(self, multiparams, params)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
        distilled_params,
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
        e, statement, parameters, cursor, context
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
        util.raise_from_cause(sqlalchemy_exception, exc_info)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
        raise value.with_traceback(tb)
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
        cursor, statement, parameters, context
    File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
            This probably means the server terminated abnormally
            before or while processing the request.

The error occurred in the simple query which is usually works

Later, I created a new page, where I called the last two APIs only and show the results on the web page, it is working

Then, In the web-page, I used call the all 7 APIs one after another - like, the 1st api get the response from the flask, then the 2nd api will call, and so on - In this experiment also, the last two APIs got the same error

This error is miss-leading, how to track the error

I used to access the database in postgres docker container on flask by sqlalchemy the engine -

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/customer',
    pool_size=20, max_overflow=10,
)

How to solve this

EDIT

Postgresql - LOG inside the docker container -

after calling the web-page, got the same issue, in the postgres logs, I got this -

LOG:  statement: BEGIN
LOG:  statement: <other query>
LOG:  statement: ROLLBACK
LOG:  statement: BEGIN
LOG:  statement: <other query>
LOG:  statement: ROLLBACK
LOG:  statement: BEGIN
LOG:  statement: <other query>
LOG:  statement: ROLLBACK
LOG:  statement: ROLLBACK

in docker-compose.yml

version: "3.4"
services:
    postgres:
        container_name: postgres_db
        restart: unless-stopped
        image: postgres:9.6.24
        command: ["postgres", "-c", "logging_collector=on", "-c", "log_directory=./postgres_db_logs", "-c", "log_filename=postgresql.log", "-c", "log_statement=all"]
        ports:
        - "5432:5432"
        environment:
        POSTGRES_USER: 'postgres'
        POSTGRES_PASSWORD: 'postgres'
        volumes:
        - ./pgdata:/var/lib/postgresql/data

For each API, I create the session and closed it properly,

in init:

session = sessionmaker(bind=engine)
self.Session = scoped_session(session)

and, in each API,

def get_customer_data(self):
    session = self.Session()
    try:
            ....
    except:
            ....
    finally:
            session.close()
            return data

Answer 1

I found the issue after debugging the query in postgresql by setting the pg_stat_statement, and the api is called, the query takes 38% of cpu_portion and takes more than 10000 ms to complete the query

So, I used the indexing technique on the columns where the query is searching. And the query is working fast and find, so far no issue as Connection stopped or ROLLBACK