Sqlalchemy bulk update for Postgresql db takes longer on staging than on local

Viewed 19

I am a bit new to Sqlalchemy

My goal is to update a table using Sqlalchemy, the total number of entries in the live environment will be around 44k But I have tried to run my code on 2k entries for test purposes.

The process of bulk update for 2k takes around 2-3secs on local whereas on staging it takes more than 400 secs

for chunk in range(0, len(news_dictionary), 100):
    new = []
    for index, all_clusters in enumerate(
            news_dictionary[chunk:chunk + 100]):
        for news_id in all_clusters['clusters']:
            new.append({"id": news_id,
                        "cluster_id": uuids_list[index + chunk]})
    local_session.bulk_update_mappings(Feeds_class, new)
    local_session.commit()

whereas, The DB is Postgres replicated master, Feeds_class is a table defined using sqlalchemy , news_dictionary is a list of dictionaries, the goal of this for loop is to insert "cluster_id" wherever a news_id was found in the Feeds_class.

Feeds_class already has data but the cluster_id column is empty, hence the update command

here's the definition of my Feeds_class

class Feeds_class(Base):
    __tablename__ = 'feeds1'
    id = Column(INTEGER, nullable=False, primary_key=True)
    text = Column(Text, nullable=True)
    description = Column(Text, nullable=True)
    url = Column(Text, nullable=True)
    image = Column(Text, nullable=True)
    slug = Column(Text, nullable=True)
    jobid = Column(Text, nullable=True)
    source = Column(Text, nullable=True)
    source_url = Column(Text, nullable=True)
    logo = Column(Text, nullable=True)
    pagetype = Column(Text, nullable=True)
    htmlpath = Column(Text, nullable=True)
    favicon = Column(Text, nullable=True)
    md5 = Column(Text, nullable=True)
    is_headline = Column(BOOLEAN, nullable=False, default=False)
    download_time = Column(FLOAT, nullable=True)
    time = Column(FLOAT, nullable=True)
    last_parse_time = Column(FLOAT, nullable=True)
    expiry = Column(FLOAT, nullable=True)
    genre = Column(ARRAY(TEXT), default=[], nullable=False)
    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    cluster_id = Column(UUID(as_uuid=True), ForeignKey(f'{cluster_table_name}.id')

the following is the cluster class which have a foreign key relation to the feeds

class Clusters_class(Base):
    __tablename__ = cluster_table_name
    # app_uuid = Column(u'_uuid', UUID(), primary_key=True)
    id = Column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    created_at = Column(
        TIMESTAMP,
        nullable=True,
        default=datetime.utcnow())
    created_at_timestamp = Column(Float, nullable=True)
    clusters = Column(ARRAY(INTEGER),nullable=False)
    news = relationship('Feeds_class', backref=cluster_table_name,
                        lazy='dynamic')
Base.metadata.create_all(engine)

0 Answers
Related