How can data consistency be ensured between Elasticsearch and PostgreSQL?

The following approaches are typically used to ensure data consistency between Elasticsearch and PostgreSQL:

1. Bidirectional synchronization through the application: when data is changed in PostgreSQL, the application updates the corresponding documents in Elasticsearch. This can be done within a single transaction or using message queues (e.g., Kafka, RabbitMQ) for asynchronous updates.

2. Using Change Data Capture (CDC): with tools that track changes in PostgreSQL (e.g., Debezium), change events can be received and applied to Elasticsearch.

3. Delayed indexing (batch update): periodic reindexing of data from PostgreSQL to Elasticsearch to eliminate desynchronization.

4. Transactional consistency: since Elasticsearch does not support transactions, a common approach is the "outbox pattern," where changes are first written to a special table and then asynchronously applied to Elasticsearch.

A simplified example of updating in Python:

import psycopg2
from elasticsearch import Elasticsearch

pg_conn = psycopg2.connect(...)
es = Elasticsearch(...)

with pg_conn:
    with pg_conn.cursor() as cur:
        # Update data in PostgreSQL
        cur.execute("UPDATE users SET name = %s WHERE id = %s", (new_name, user_id))
        # Update index in Elasticsearch
        es.update(index='users', id=user_id, body={'doc': {'name': new_name}})

It is important to handle errors and transaction rollbacks to maintain consistency.