7 Powerful Techniques to Boost Database Performance in Python Applications

06111

As a best-selling author, I invite you to explore my books on Amazon. Don’t forget to follow me on Medium and show your support. Thank you! Your support means the world!

Python’s ability to interact with databases efficiently is crucial for developing high-performance applications. I’ll share seven techniques that can significantly improve database querying and ORM optimization in Python projects.

  1. Effective use of SQLAlchemy’s query optimization techniques

SQLAlchemy, a popular ORM for Python, offers several ways to optimize database queries. One of the most powerful is eager loading, which allows us to load related objects in a single query, reducing the number of database hits.

Let’s consider a scenario where we have a User model with associated Posts: 

<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span><span>,</span> <span>Column</span><span>,</span> <span>Integer</span><span>,</span> <span>String</span><span>,</span> <span>ForeignKey</span>
<span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>relationship</span><span>,</span> <span>sessionmaker</span>
<span>from</span> <span>sqlalchemy.ext.declarative</span> <span>import</span> <span>declarative_base</span>
<span>Base</span> <span>=</span> <span>declarative_base</span><span>()</span>
<span>class</span> <span>User</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>users</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>name</span> <span>=</span> <span>Column</span><span>(</span><span>String</span><span>)</span>
    <span>posts</span> <span>=</span> <span>relationship</span><span>(</span><span>"</span><span>Post</span><span>"</span><span>,</span> <span>back_populates</span><span>=</span><span>"</span><span>user</span><span>"</span><span>)</span>
<span>class</span> <span>Post</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>posts</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>title</span> <span>=</span> <span>Column</span><span>(</span><span>String</span><span>)</span>
    <span>user_id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>ForeignKey</span><span>(</span><span>'</span><span>users.id</span><span>'</span><span>))</span>
    <span>user</span> <span>=</span> <span>relationship</span><span>(</span><span>"</span><span>User</span><span>"</span><span>,</span> <span>back_populates</span><span>=</span><span>"</span><span>posts</span><span>"</span><span>)</span>
<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>Session</span> <span>=</span> <span>sessionmaker</span><span>(</span><span>bind</span><span>=</span><span>engine</span><span>)</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span><span>,</span> <span>Column</span><span>,</span> <span>Integer</span><span>,</span> <span>String</span><span>,</span> <span>ForeignKey</span>
<span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>relationship</span><span>,</span> <span>sessionmaker</span>
<span>from</span> <span>sqlalchemy.ext.declarative</span> <span>import</span> <span>declarative_base</span>

<span>Base</span> <span>=</span> <span>declarative_base</span><span>()</span>

<span>class</span> <span>User</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>users</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>name</span> <span>=</span> <span>Column</span><span>(</span><span>String</span><span>)</span>
    <span>posts</span> <span>=</span> <span>relationship</span><span>(</span><span>"</span><span>Post</span><span>"</span><span>,</span> <span>back_populates</span><span>=</span><span>"</span><span>user</span><span>"</span><span>)</span>

<span>class</span> <span>Post</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>posts</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>title</span> <span>=</span> <span>Column</span><span>(</span><span>String</span><span>)</span>
    <span>user_id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>ForeignKey</span><span>(</span><span>'</span><span>users.id</span><span>'</span><span>))</span>
    <span>user</span> <span>=</span> <span>relationship</span><span>(</span><span>"</span><span>User</span><span>"</span><span>,</span> <span>back_populates</span><span>=</span><span>"</span><span>posts</span><span>"</span><span>)</span>

<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>Session</span> <span>=</span> <span>sessionmaker</span><span>(</span><span>bind</span><span>=</span><span>engine</span><span>)</span>
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    posts = relationship("Post", back_populates="user")

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="posts")

engine = create_engine('postgresql://user:password@localhost/dbname')
Session = sessionmaker(bind=engine)

Enter fullscreen mode Exit fullscreen mode

To fetch users and their posts efficiently, we can use joinedload: 

<span>session</span> <span>=</span> <span>Session</span><span>()</span>
<span>users</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>User</span><span>).</span><span>options</span><span>(</span><span>joinedload</span><span>(</span><span>User</span><span>.</span><span>posts</span><span>)).</span><span>all</span><span>()</span>
<span>session</span> <span>=</span> <span>Session</span><span>()</span>
<span>users</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>User</span><span>).</span><span>options</span><span>(</span><span>joinedload</span><span>(</span><span>User</span><span>.</span><span>posts</span><span>)).</span><span>all</span><span>()</span>
session = Session()
users = session.query(User).options(joinedload(User.posts)).all()

Enter fullscreen mode Exit fullscreen mode

This single query will fetch all users and their associated posts, avoiding the N+1 query problem.

  1. Implementing query caching mechanisms

Caching frequently accessed data can significantly reduce database load. We can use libraries like Redis or Memcached to implement query caching. Here’s an example using Redis: 

<span>import</span> <span>redis</span>
<span>import</span> <span>pickle</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span><span>,</span> <span>text</span>
<span>redis_client</span> <span>=</span> <span>redis</span><span>.</span><span>Redis</span><span>(</span><span>host</span><span>=</span><span>'</span><span>localhost</span><span>'</span><span>,</span> <span>port</span><span>=</span><span>6379</span><span>,</span> <span>db</span><span>=</span><span>0</span><span>)</span>
<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>def</span> <span>get_user_data</span><span>(</span><span>user_id</span><span>):</span>
    <span>cache_key</span> <span>=</span> <span>f</span><span>"</span><span>user:</span><span>{</span><span>user_id</span><span>}</span><span>"</span>
    <span>cached_data</span> <span>=</span> <span>redis_client</span><span>.</span><span>get</span><span>(</span><span>cache_key</span><span>)</span>
    <span>if</span> <span>cached_data</span><span>:</span>
        <span>return</span> <span>pickle</span><span>.</span><span>loads</span><span>(</span><span>cached_data</span><span>)</span>
    <span>with</span> <span>engine</span><span>.</span><span>connect</span><span>()</span> <span>as</span> <span>conn</span><span>:</span>
        <span>result</span> <span>=</span> <span>conn</span><span>.</span><span>execute</span><span>(</span><span>text</span><span>(</span><span>"</span><span>SELECT * FROM users WHERE id = :id</span><span>"</span><span>),</span> <span>{</span><span>"</span><span>id</span><span>"</span><span>:</span> <span>user_id</span><span>})</span>
        <span>user_data</span> <span>=</span> <span>result</span><span>.</span><span>fetchone</span><span>()</span>
        <span>if</span> <span>user_data</span><span>:</span>
            <span>redis_client</span><span>.</span><span>setex</span><span>(</span><span>cache_key</span><span>,</span> <span>3600</span><span>,</span> <span>pickle</span><span>.</span><span>dumps</span><span>(</span><span>user_data</span><span>))</span>  <span># Cache for 1 hour </span>
        <span>return</span> <span>user_data</span>
<span>import</span> <span>redis</span>
<span>import</span> <span>pickle</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span><span>,</span> <span>text</span>

<span>redis_client</span> <span>=</span> <span>redis</span><span>.</span><span>Redis</span><span>(</span><span>host</span><span>=</span><span>'</span><span>localhost</span><span>'</span><span>,</span> <span>port</span><span>=</span><span>6379</span><span>,</span> <span>db</span><span>=</span><span>0</span><span>)</span>
<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>

<span>def</span> <span>get_user_data</span><span>(</span><span>user_id</span><span>):</span>
    <span>cache_key</span> <span>=</span> <span>f</span><span>"</span><span>user:</span><span>{</span><span>user_id</span><span>}</span><span>"</span>
    <span>cached_data</span> <span>=</span> <span>redis_client</span><span>.</span><span>get</span><span>(</span><span>cache_key</span><span>)</span>

    <span>if</span> <span>cached_data</span><span>:</span>
        <span>return</span> <span>pickle</span><span>.</span><span>loads</span><span>(</span><span>cached_data</span><span>)</span>

    <span>with</span> <span>engine</span><span>.</span><span>connect</span><span>()</span> <span>as</span> <span>conn</span><span>:</span>
        <span>result</span> <span>=</span> <span>conn</span><span>.</span><span>execute</span><span>(</span><span>text</span><span>(</span><span>"</span><span>SELECT * FROM users WHERE id = :id</span><span>"</span><span>),</span> <span>{</span><span>"</span><span>id</span><span>"</span><span>:</span> <span>user_id</span><span>})</span>
        <span>user_data</span> <span>=</span> <span>result</span><span>.</span><span>fetchone</span><span>()</span>

        <span>if</span> <span>user_data</span><span>:</span>
            <span>redis_client</span><span>.</span><span>setex</span><span>(</span><span>cache_key</span><span>,</span> <span>3600</span><span>,</span> <span>pickle</span><span>.</span><span>dumps</span><span>(</span><span>user_data</span><span>))</span>  <span># Cache for 1 hour </span>
        <span>return</span> <span>user_data</span>
import redis
import pickle
from sqlalchemy import create_engine, text

redis_client = redis.Redis(host='localhost', port=6379, db=0)
engine = create_engine('postgresql://user:password@localhost/dbname')

def get_user_data(user_id):
    cache_key = f"user:{user_id}"
    cached_data = redis_client.get(cache_key)

    if cached_data:
        return pickle.loads(cached_data)

    with engine.connect() as conn:
        result = conn.execute(text("SELECT * FROM users WHERE id = :id"), {"id": user_id})
        user_data = result.fetchone()

        if user_data:
            redis_client.setex(cache_key, 3600, pickle.dumps(user_data))  # Cache for 1 hour 
        return user_data

Enter fullscreen mode Exit fullscreen mode

This function checks the Redis cache before querying the database, reducing database load for frequently accessed user data.

  1. Utilizing bulk operations and batch processing

When dealing with large datasets, bulk operations can significantly improve performance. SQLAlchemy provides methods for bulk inserts and updates: 

<span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>Session</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>from</span> <span>sqlalchemy.ext.declarative</span> <span>import</span> <span>declarative_base</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>Column</span><span>,</span> <span>Integer</span><span>,</span> <span>String</span>
<span>Base</span> <span>=</span> <span>declarative_base</span><span>()</span>
<span>class</span> <span>User</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>users</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>name</span> <span>=</span> <span>Column</span><span>(</span><span>String</span><span>)</span>
<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>session</span> <span>=</span> <span>Session</span><span>(</span><span>engine</span><span>)</span>
<span># Bulk insert </span><span>users</span> <span>=</span> <span>[</span><span>User</span><span>(</span><span>name</span><span>=</span><span>f</span><span>"</span><span>User </span><span>{</span><span>i</span><span>}</span><span>"</span><span>)</span> <span>for</span> <span>i</span> <span>in</span> <span>range</span><span>(</span><span>1000</span><span>)]</span>
<span>session</span><span>.</span><span>bulk_save_objects</span><span>(</span><span>users</span><span>)</span>
<span>session</span><span>.</span><span>commit</span><span>()</span>
<span># Bulk update </span><span>session</span><span>.</span><span>query</span><span>(</span><span>User</span><span>).</span><span>filter</span><span>(</span><span>User</span><span>.</span><span>id</span> <span><</span> <span>500</span><span>).</span><span>update</span><span>({</span><span>"</span><span>name</span><span>"</span><span>:</span> <span>"</span><span>Updated User</span><span>"</span><span>})</span>
<span>session</span><span>.</span><span>commit</span><span>()</span>
<span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>Session</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>from</span> <span>sqlalchemy.ext.declarative</span> <span>import</span> <span>declarative_base</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>Column</span><span>,</span> <span>Integer</span><span>,</span> <span>String</span>

<span>Base</span> <span>=</span> <span>declarative_base</span><span>()</span>

<span>class</span> <span>User</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>users</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>name</span> <span>=</span> <span>Column</span><span>(</span><span>String</span><span>)</span>

<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>session</span> <span>=</span> <span>Session</span><span>(</span><span>engine</span><span>)</span>

<span># Bulk insert </span><span>users</span> <span>=</span> <span>[</span><span>User</span><span>(</span><span>name</span><span>=</span><span>f</span><span>"</span><span>User </span><span>{</span><span>i</span><span>}</span><span>"</span><span>)</span> <span>for</span> <span>i</span> <span>in</span> <span>range</span><span>(</span><span>1000</span><span>)]</span>
<span>session</span><span>.</span><span>bulk_save_objects</span><span>(</span><span>users</span><span>)</span>
<span>session</span><span>.</span><span>commit</span><span>()</span>

<span># Bulk update </span><span>session</span><span>.</span><span>query</span><span>(</span><span>User</span><span>).</span><span>filter</span><span>(</span><span>User</span><span>.</span><span>id</span> <span><</span> <span>500</span><span>).</span><span>update</span><span>({</span><span>"</span><span>name</span><span>"</span><span>:</span> <span>"</span><span>Updated User</span><span>"</span><span>})</span>
<span>session</span><span>.</span><span>commit</span><span>()</span>
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('postgresql://user:password@localhost/dbname')
session = Session(engine)

# Bulk insert users = [User(name=f"User {i}") for i in range(1000)]
session.bulk_save_objects(users)
session.commit()

# Bulk update session.query(User).filter(User.id < 500).update({"name": "Updated User"})
session.commit()

Enter fullscreen mode Exit fullscreen mode

These bulk operations reduce the number of database queries, leading to improved performance.

  1. Leveraging database-specific features

Different databases offer unique features that can be exploited for better performance. For instance, PostgreSQL’s JSONB type allows for flexible data storage and efficient querying of JSON data: 

<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span><span>,</span> <span>Column</span><span>,</span> <span>Integer</span><span>,</span> <span>JSON</span>
<span>from</span> <span>sqlalchemy.ext.declarative</span> <span>import</span> <span>declarative_base</span>
<span>from</span> <span>sqlalchemy.dialects.postgresql</span> <span>import</span> <span>JSONB</span>
<span>Base</span> <span>=</span> <span>declarative_base</span><span>()</span>
<span>class</span> <span>Product</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>products</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>details</span> <span>=</span> <span>Column</span><span>(</span><span>JSONB</span><span>)</span>
<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>Base</span><span>.</span><span>metadata</span><span>.</span><span>create_all</span><span>(</span><span>engine</span><span>)</span>
<span># Querying JSONB data </span><span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>Session</span>
<span>session</span> <span>=</span> <span>Session</span><span>(</span><span>engine</span><span>)</span>
<span># Find products with a specific color </span><span>products</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>Product</span><span>).</span><span>filter</span><span>(</span><span>Product</span><span>.</span><span>details</span><span>[</span><span>'</span><span>color</span><span>'</span><span>].</span><span>astext</span> <span>==</span> <span>'</span><span>red</span><span>'</span><span>).</span><span>all</span><span>()</span>
<span># Find products within a price range </span><span>products</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>Product</span><span>).</span><span>filter</span><span>(</span>
    <span>Product</span><span>.</span><span>details</span><span>[</span><span>'</span><span>price</span><span>'</span><span>].</span><span>cast</span><span>(</span><span>Integer</span><span>).</span><span>between</span><span>(</span><span>10</span><span>,</span> <span>50</span><span>)</span>
<span>).</span><span>all</span><span>()</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span><span>,</span> <span>Column</span><span>,</span> <span>Integer</span><span>,</span> <span>JSON</span>
<span>from</span> <span>sqlalchemy.ext.declarative</span> <span>import</span> <span>declarative_base</span>
<span>from</span> <span>sqlalchemy.dialects.postgresql</span> <span>import</span> <span>JSONB</span>

<span>Base</span> <span>=</span> <span>declarative_base</span><span>()</span>

<span>class</span> <span>Product</span><span>(</span><span>Base</span><span>):</span>
    <span>__tablename__</span> <span>=</span> <span>'</span><span>products</span><span>'</span>
    <span>id</span> <span>=</span> <span>Column</span><span>(</span><span>Integer</span><span>,</span> <span>primary_key</span><span>=</span><span>True</span><span>)</span>
    <span>details</span> <span>=</span> <span>Column</span><span>(</span><span>JSONB</span><span>)</span>

<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>)</span>
<span>Base</span><span>.</span><span>metadata</span><span>.</span><span>create_all</span><span>(</span><span>engine</span><span>)</span>

<span># Querying JSONB data </span><span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>Session</span>
<span>session</span> <span>=</span> <span>Session</span><span>(</span><span>engine</span><span>)</span>

<span># Find products with a specific color </span><span>products</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>Product</span><span>).</span><span>filter</span><span>(</span><span>Product</span><span>.</span><span>details</span><span>[</span><span>'</span><span>color</span><span>'</span><span>].</span><span>astext</span> <span>==</span> <span>'</span><span>red</span><span>'</span><span>).</span><span>all</span><span>()</span>

<span># Find products within a price range </span><span>products</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>Product</span><span>).</span><span>filter</span><span>(</span>
    <span>Product</span><span>.</span><span>details</span><span>[</span><span>'</span><span>price</span><span>'</span><span>].</span><span>cast</span><span>(</span><span>Integer</span><span>).</span><span>between</span><span>(</span><span>10</span><span>,</span> <span>50</span><span>)</span>
<span>).</span><span>all</span><span>()</span>
from sqlalchemy import create_engine, Column, Integer, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSONB

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    details = Column(JSONB)

engine = create_engine('postgresql://user:password@localhost/dbname')
Base.metadata.create_all(engine)

# Querying JSONB data from sqlalchemy.orm import Session
session = Session(engine)

# Find products with a specific color products = session.query(Product).filter(Product.details['color'].astext == 'red').all()

# Find products within a price range products = session.query(Product).filter(
    Product.details['price'].cast(Integer).between(10, 50)
).all()

Enter fullscreen mode Exit fullscreen mode

This approach allows for flexible schema design while maintaining query efficiency.

  1. Implementing efficient connection pooling

Connection pooling is crucial for managing database connections efficiently, especially in high-concurrency scenarios. SQLAlchemy provides built-in connection pooling, but we can fine-tune it: 

<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>from</span> <span>sqlalchemy.pool</span> <span>import</span> <span>QueuePool</span>
<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>,</span>
                       <span>poolclass</span><span>=</span><span>QueuePool</span><span>,</span>
                       <span>pool_size</span><span>=</span><span>10</span><span>,</span>
                       <span>max_overflow</span><span>=</span><span>20</span><span>,</span>
                       <span>pool_timeout</span><span>=</span><span>30</span><span>,</span>
                       <span>pool_recycle</span><span>=</span><span>1800</span><span>)</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>from</span> <span>sqlalchemy.pool</span> <span>import</span> <span>QueuePool</span>

<span>engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@localhost/dbname</span><span>'</span><span>,</span>
                       <span>poolclass</span><span>=</span><span>QueuePool</span><span>,</span>
                       <span>pool_size</span><span>=</span><span>10</span><span>,</span>
                       <span>max_overflow</span><span>=</span><span>20</span><span>,</span>
                       <span>pool_timeout</span><span>=</span><span>30</span><span>,</span>
                       <span>pool_recycle</span><span>=</span><span>1800</span><span>)</span>
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

engine = create_engine('postgresql://user:password@localhost/dbname',
                       poolclass=QueuePool,
                       pool_size=10,
                       max_overflow=20,
                       pool_timeout=30,
                       pool_recycle=1800)

Enter fullscreen mode Exit fullscreen mode

This configuration creates a pool with a maximum of 10 connections, allowing up to 20 additional connections during peak loads. Connections are recycled after 30 minutes to prevent stale connections.

  1. Utilizing query profiling and optimization tools

Identifying slow queries is crucial for optimization. We can use SQLAlchemy’s event system to log and profile queries: 

<span>import</span> <span>time</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>event</span>
<span>from</span> <span>sqlalchemy.engine</span> <span>import</span> <span>Engine</span>
<span>@event.listens_for</span><span>(</span><span>Engine</span><span>,</span> <span>"</span><span>before_cursor_execute</span><span>"</span><span>)</span>
<span>def</span> <span>before_cursor_execute</span><span>(</span><span>conn</span><span>,</span> <span>cursor</span><span>,</span> <span>statement</span><span>,</span> <span>parameters</span><span>,</span> <span>context</span><span>,</span> <span>executemany</span><span>):</span>
    <span>conn</span><span>.</span><span>info</span><span>.</span><span>setdefault</span><span>(</span><span>'</span><span>query_start_time</span><span>'</span><span>,</span> <span>[]).</span><span>append</span><span>(</span><span>time</span><span>.</span><span>time</span><span>())</span>
<span>@event.listens_for</span><span>(</span><span>Engine</span><span>,</span> <span>"</span><span>after_cursor_execute</span><span>"</span><span>)</span>
<span>def</span> <span>after_cursor_execute</span><span>(</span><span>conn</span><span>,</span> <span>cursor</span><span>,</span> <span>statement</span><span>,</span> <span>parameters</span><span>,</span> <span>context</span><span>,</span> <span>executemany</span><span>):</span>
    <span>total</span> <span>=</span> <span>time</span><span>.</span><span>time</span><span>()</span> <span>-</span> <span>conn</span><span>.</span><span>info</span><span>[</span><span>'</span><span>query_start_time</span><span>'</span><span>].</span><span>pop</span><span>(</span><span>-</span><span>1</span><span>)</span>
    <span>print</span><span>(</span><span>f</span><span>"</span><span>Total query time: </span><span>{</span><span>total</span><span>}</span><span>"</span><span>)</span>
    <span>print</span><span>(</span><span>f</span><span>"</span><span>Query: </span><span>{</span><span>statement</span><span>}</span><span>"</span><span>)</span>
<span>import</span> <span>time</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>event</span>
<span>from</span> <span>sqlalchemy.engine</span> <span>import</span> <span>Engine</span>

<span>@event.listens_for</span><span>(</span><span>Engine</span><span>,</span> <span>"</span><span>before_cursor_execute</span><span>"</span><span>)</span>
<span>def</span> <span>before_cursor_execute</span><span>(</span><span>conn</span><span>,</span> <span>cursor</span><span>,</span> <span>statement</span><span>,</span> <span>parameters</span><span>,</span> <span>context</span><span>,</span> <span>executemany</span><span>):</span>
    <span>conn</span><span>.</span><span>info</span><span>.</span><span>setdefault</span><span>(</span><span>'</span><span>query_start_time</span><span>'</span><span>,</span> <span>[]).</span><span>append</span><span>(</span><span>time</span><span>.</span><span>time</span><span>())</span>

<span>@event.listens_for</span><span>(</span><span>Engine</span><span>,</span> <span>"</span><span>after_cursor_execute</span><span>"</span><span>)</span>
<span>def</span> <span>after_cursor_execute</span><span>(</span><span>conn</span><span>,</span> <span>cursor</span><span>,</span> <span>statement</span><span>,</span> <span>parameters</span><span>,</span> <span>context</span><span>,</span> <span>executemany</span><span>):</span>
    <span>total</span> <span>=</span> <span>time</span><span>.</span><span>time</span><span>()</span> <span>-</span> <span>conn</span><span>.</span><span>info</span><span>[</span><span>'</span><span>query_start_time</span><span>'</span><span>].</span><span>pop</span><span>(</span><span>-</span><span>1</span><span>)</span>
    <span>print</span><span>(</span><span>f</span><span>"</span><span>Total query time: </span><span>{</span><span>total</span><span>}</span><span>"</span><span>)</span>
    <span>print</span><span>(</span><span>f</span><span>"</span><span>Query: </span><span>{</span><span>statement</span><span>}</span><span>"</span><span>)</span>
import time
from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "before_cursor_execute")
def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault('query_start_time', []).append(time.time())

@event.listens_for(Engine, "after_cursor_execute")
def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    total = time.time() - conn.info['query_start_time'].pop(-1)
    print(f"Total query time: {total}")
    print(f"Query: {statement}")

Enter fullscreen mode Exit fullscreen mode

This code logs the execution time and SQL statement for each query, helping identify slow queries for optimization.

  1. Implementing database sharding and read replicas

For large-scale applications, database sharding and read replicas can significantly improve performance and scalability. While the implementation details can be complex, here’s a simplified example of how we might use read replicas: 

<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>sessionmaker</span>
<span>write_engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@master/dbname</span><span>'</span><span>)</span>
<span>read_engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@replica/dbname</span><span>'</span><span>)</span>
<span>WriteSession</span> <span>=</span> <span>sessionmaker</span><span>(</span><span>bind</span><span>=</span><span>write_engine</span><span>)</span>
<span>ReadSession</span> <span>=</span> <span>sessionmaker</span><span>(</span><span>bind</span><span>=</span><span>read_engine</span><span>)</span>
<span>def</span> <span>get_user</span><span>(</span><span>user_id</span><span>):</span>
    <span>with</span> <span>ReadSession</span><span>()</span> <span>as</span> <span>session</span><span>:</span>
        <span>return</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>User</span><span>).</span><span>get</span><span>(</span><span>user_id</span><span>)</span>
<span>def</span> <span>create_user</span><span>(</span><span>name</span><span>):</span>
    <span>with</span> <span>WriteSession</span><span>()</span> <span>as</span> <span>session</span><span>:</span>
        <span>user</span> <span>=</span> <span>User</span><span>(</span><span>name</span><span>=</span><span>name</span><span>)</span>
        <span>session</span><span>.</span><span>add</span><span>(</span><span>user</span><span>)</span>
        <span>session</span><span>.</span><span>commit</span><span>()</span>
        <span>return</span> <span>user</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>from</span> <span>sqlalchemy.orm</span> <span>import</span> <span>sessionmaker</span>

<span>write_engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@master/dbname</span><span>'</span><span>)</span>
<span>read_engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>'</span><span>postgresql://user:password@replica/dbname</span><span>'</span><span>)</span>

<span>WriteSession</span> <span>=</span> <span>sessionmaker</span><span>(</span><span>bind</span><span>=</span><span>write_engine</span><span>)</span>
<span>ReadSession</span> <span>=</span> <span>sessionmaker</span><span>(</span><span>bind</span><span>=</span><span>read_engine</span><span>)</span>

<span>def</span> <span>get_user</span><span>(</span><span>user_id</span><span>):</span>
    <span>with</span> <span>ReadSession</span><span>()</span> <span>as</span> <span>session</span><span>:</span>
        <span>return</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>User</span><span>).</span><span>get</span><span>(</span><span>user_id</span><span>)</span>

<span>def</span> <span>create_user</span><span>(</span><span>name</span><span>):</span>
    <span>with</span> <span>WriteSession</span><span>()</span> <span>as</span> <span>session</span><span>:</span>
        <span>user</span> <span>=</span> <span>User</span><span>(</span><span>name</span><span>=</span><span>name</span><span>)</span>
        <span>session</span><span>.</span><span>add</span><span>(</span><span>user</span><span>)</span>
        <span>session</span><span>.</span><span>commit</span><span>()</span>
        <span>return</span> <span>user</span>
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

write_engine = create_engine('postgresql://user:password@master/dbname')
read_engine = create_engine('postgresql://user:password@replica/dbname')

WriteSession = sessionmaker(bind=write_engine)
ReadSession = sessionmaker(bind=read_engine)

def get_user(user_id):
    with ReadSession() as session:
        return session.query(User).get(user_id)

def create_user(name):
    with WriteSession() as session:
        user = User(name=name)
        session.add(user)
        session.commit()
        return user

Enter fullscreen mode Exit fullscreen mode

This approach directs read operations to the replica and write operations to the master database, distributing the load.

These seven techniques can significantly improve the performance of database operations in Python applications. However, it’s important to remember that optimization should be based on actual performance measurements and specific application needs. Premature optimization can lead to unnecessary complexity.

In my experience, the key to efficient database querying lies in understanding the specific requirements of your application and the characteristics of your data. Start with a clear database schema and well-structured queries. As your application grows, monitor its performance and apply these optimization techniques where they provide the most benefit.

Remember, the goal is to strike a balance between query performance, code readability, and maintainability. Sometimes, a slightly less efficient query that’s easier to understand and maintain is preferable to a highly optimized but complex one. Always consider the long-term implications of your optimizations on the overall architecture of your application.

By applying these techniques judiciously and continuously monitoring your application’s performance, you can ensure that your Python application interacts with its database in the most efficient manner possible, providing a smooth and responsive experience for your users.

101 Books

101 Books is an AI-driven publishing company co-founded by author Aarav Joshi. By leveraging advanced AI technology, we keep our publishing costs incredibly low—some books are priced as low as $4—making quality knowledge accessible to everyone.

Check out our book Golang Clean Code available on Amazon.

Stay tuned for updates and exciting news. When shopping for books, search for Aarav Joshi to find more of our titles. Use the provided link to enjoy special discounts!

Our Creations

Be sure to check out our creations:

Investor Central | Investor Central Spanish | Investor Central German | Smart Living | Epochs & Echoes | Puzzling Mysteries | Hindutva | Elite Dev | JS Schools

We are on Medium

Tech Koala Insights | Epochs & Echoes World | Investor Central Medium | Puzzling Mysteries Medium | Science & Epochs Medium | Modern Hindutva

原文链接：7 Powerful Techniques to Boost Database Performance in Python Applications

展开阅读全文
© 版权声明
文章版权声明 1、本网站名称：拾光赋
2、本站永久网址：https://www.blogs.ink
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ：805375623进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
Python（EN）
# python# programming# softwareengineering# devto
喜欢就支持一下吧
点赞11 分享
We’d better struggle for the future rather than regret for the past.
如果后悔过去，不如奋斗将来
评论 抢沙发

请登录后发表评论

    暂无评论内容

文章目录
kity的头像-拾光赋
文章目录
今日剩余 15.6%
本周剩余 30.8%
本月剩余 63.9%
本年剩余 72.4%
最近评论
Vito的头像-拾光赋

Vito6个月前2

你就是我心中的那首忐忑，总是让我惊心动魄
Jason.Meng的头像-拾光赋钻石会员

Jason.Meng徽章-人气大使-拾光赋6个月前2

总是会在人生的不同阶段反复爱上大佬的文章~
每日一言
We’d better struggle for the future rather than regret for the past.
如果后悔过去，不如奋斗将来