PostgreSQL Database Configuration

This page provides a detailed overview of how PostgreSQL is configured, optimized, and utilized within The Wall API.

Table of Contents

1. Overview
2. PostgreSQL Configuration
3. ORM Models
   3.1 ORM Models Diagram
   3.2 Models Overview
     3.2.1 WallConfig
     3.2.2 WallConfigReference
     3.2.3 Wall
     3.2.4 WallProgress
   3.3 Design Reasoning for ORM Architecture
4. Database Concurrency Control
   4.1 PostgreSQL Advisory Locks
   4.2 Transaction Management
   4.3 Row-Level Locking

1. Overview

• PostgreSQL is the database used in The Wall API, storing user configurations, wall construction data, and simulation results.

• It ensures data integrity, efficient indexing, and reliable transactional consistency, supporting concurrent request processing and caching strategies for optimized performance.

---

2. PostgreSQL Configuration

• PostgreSQL is configured via the DATABASES dictionary in settings.py:

  DATABASES = {
      'default': {
          'ENGINE': os.getenv('DB_ENGINE', 'django.db.backends.postgresql'),
          'HOST': os.getenv('DB_HOST', 'postgres'),
          'PORT': os.getenv('DB_PORT', 5432),
          'NAME': os.getenv('POSTGRES_DB'),
          'USER': os.getenv('POSTGRES_USER'),
          'PASSWORD': os.getenv('POSTGRES_PASSWORD'),
          'OPTIONS': {
              'connect_timeout': int(os.getenv('DB_CONNECT_TIMEOUT', 10)),
              'options': f'-c statement_timeout={int(os.getenv("DB_STATEMENT_TIMEOUT", 5000))}',
          }
      }
  }

  
  

• The values of these settings are loaded from .env files stored in the config/envs/ folder.

  Variable	Description
  DB_ENGINE	Database driver
  DB_HOST	PostgreSQL host (default: localhost or postgres)
  DB_PORT	Database port (default: 5432)
  POSTGRES_DB	Name of the PostgreSQL database
  POSTGRES_USER	Database user
  POSTGRES_PASSWORD	Database password
  DB_CONNECT_TIMEOUT	Connection timeout in seconds
  DB_STATEMENT_TIMEOUT	Maximum execution time for queries

  🗒️ For the Production v2 environment, the values of POSTGRES_DB, POSTGRES_USER, and POSTGRES_PASSWORD are stored in Docker secrets files instead of .env files.

  💡 config/envs/ folder breakdown → Environment Configuration Files Folder Structure.

  💡 For a detailed description of the DB environment variables, refer to the PostgreSQL Database Configuration section of the Environment Variables Index page.

Return to Top

---

3. ORM Models

3.1 ORM Models Diagram

The following diagram illustrates the application's ORM models, their attributes, and how they relate to each other. It shows how users interact with wall configurations, how configurations are used in wall simulations, and how progress is tracked over time.

View full image

Return to Top

3.2 Models Overview

The ORM models are implemented using Django's models.Model class.

3.2.1 WallConfig

This model is the foundation for all wall objects and stores the uploaded wall configurations.

• Attributes:

  • wall_config_hash (CharField): Unique hash of the wall configuration.
  • status (CharField): Tracks the status of the configuration (initialized, calculated, error, etc.).
  • deletion_initiated (BooleanField): Indicates whether deletion has been requested.
  • wall_construction_config (JSONField): Stores the actual wall configuration data.
  • date_created (DateTimeField): Timestamp for when the configuration was created.
  
  

• WallConfigStatusEnum
  Enumeration defining the possible statuses for a WallConfig.

  • Values:
    • initialized: Created and pending for processing; may be precomputed if it meets full-range caching criteria.
    • celery_calculation: The configuration is being processed asynchronously by Celery.
    • error: A failure occurred during processing, preventing simulation completion.
    • calculated: Fully computed for the full-range of all possible num_crews values and stored in the DB.
    • partially_calculated: Processed and stored in the DB for specific num_crews, but not precomputed for all possible cases.
    • ready_for_deletion: Any existing calculations related to the WallConfig are aborted, and the object is marked for deletion.
    
    

3.2.2 WallConfigReference

Tracks user uploads of wall configuration files and links them to a WallConfig instance.

• Behavior:

  • If a different user uploads the same configuration file, a new WallConfigReference instance is created, linking to the existing WallConfig.
  • Each user maintains an independent reference (config_id) to a WallConfig. This allows controlled access while avoiding data duplication.

• Attributes:

  • user (ForeignKey): The owner of the uploaded file. Cascade deletion is configured on User deletion.
  • wall_config (ForeignKey): Links to the associated WallConfig. Cascade deletion is configured on WallConfig deletion.
  • config_id (CharField): A user-defined identifier for their wall configuration.
  • status (CharField): Tracks the processing state (available, sync_calculation, etc.).
  • date_created (DateTimeField): Timestamp for when the file was uploaded.
  
  

• WallConfigReferenceStatusEnum
  Enumeration defining the possible statuses for a WallConfigReference:

  • Values:
    • available: No calculations are in progress for this reference.
    • celery_calculation: Processing via Celery is in progress.
    • sync_calculation: Processing is happening synchronously.

• Uniqueness Constraint:

  • unique_together: (user, config_id)

3.2.3 Wall

Each Wall instance represents a build simulation using a specific WallConfig>.

• Attributes:

  • wall_config (ForeignKey): Links to the associated WallConfig. Cascade deletion is configured on WallConfig deletion.
  • wall_config_hash (CharField): Stores the hash of the used configuration.
  • num_crews (IntegerField): Number of crews assigned to the construction.
  • total_ice_amount (BigIntegerField): Total ice required for the build.
  • construction_days (IntegerField): Number of days required to complete the wall.
  • date_created (DateTimeField): Timestamp for when the wall was created.

• Uniqueness Constraint:

  • unique_together: (wall_config_hash, num_crews)

3.2.4 WallProgress

Stores the daily progress of a Wall simulation.

• Attributes:

  • wall (ForeignKey): Links to the related Wall. Cascade deletion is configured on Wall deletion.
  • day (IntegerField): Day number in the construction timeline.
  • ice_amount_data (JSONField): Records the ice used on that day.
  • date_created (DateTimeField): Timestamp for when the progress was recorded.

• Uniqueness Constraint:

  • unique_together: (wall, day)

Return to Top

3.3 Design Reasoning for ORM Architecture

• Data Integrity & Consistency

  • WallConfig serves as a primary reference point, preventing duplicate configurations.
  • WallConfigReference ensures that each user maintains a unique reference to a configuration without creating redundant data.

  ✔ This approach minimizes database storage overhead while maintaining individual user access to shared configurations.

Return to Top

• Automatic Cleanup & Referential Integrity

  • Cascade deletion is enforced, ensuring that when an instance is removed, all related entries are deleted as well.
  • The on_delete=models.CASCADE rule is applied to WallConfigReference, Wall, and WallProgress objects.
  • In addition, during on-demand Delete File requests, if the last reference to a WallConfig is deleted, the WallConfig is deleted via the dedicated delete_unused_wall_configs_task.

  ✔ This approach enforces strict data integrity while keeping the database clean and efficient.
  It ensures no orphaned references remain after deletion and eliminates the need for explicit deletions of dependent objects.
  
  

  ⚠️ Exception: Cascade deletion is not applied to a WallConfig when a user is deleted, as multiple users may reference the same configuration.
  If the last user associated with a WallConfig deletes their account, without explicitly deleting all of their uploaded files,
  the files are deleted by the cascading deletion rule, but the related WallConfig objects remain orphaned.

  ✔ To handle such cases, a scheduled DB cleanup task runs via Celery Beat, ensuring orphaned configurations are periodically cleaned up.
  
  

  💡 For more details on the Celery delete_unused_wall_configs_task, refer to the dedicated Concurrent Tasks section in the Celery Asynchronous Task Processing page.

Return to Top

• Uniqueness Constraints

  • To reinforce data integrity, the following uniqueness constraints define strict model relationships, preventing redundancy and conflicts:

    • WallConfigReference (user, config_id)
      ✔ This ensures that each user maintains a single reference to a given WallConfig, preventing duplicate records under different identifiers.

    • Wall (wall_config_hash, num_crews)
      ✔ This prevents redundant wall simulations for the same configuration and crew count, ensuring efficient storage and retrieval of results.

    • WallProgress (wall, day)
      ✔ This guarantees that each day’s progress for a wall simulation is recorded only once, maintaining consistency in tracking construction progress.

Return to Top

• Efficient Querying & Relationships

  • ForeignKey relationships optimize data retrieval by allowing efficient joins.
  • Indexing wall_config_hash speeds up lookups for configuration-related operations.

  ✔ Efficient indexing allows rapid retrieval of wall configurations, ensuring fast user queries.

Return to Top

• Scalability & User Isolation

  • Users can independently upload identical configurations without duplicating database entries.
  • Queries are restricted to configurations explicitly linked to the requesting user.

  ✔ This separation ensures that users can run independent simulations while avoiding unnecessary storage duplication.

Return to Top

• Status Management for Processing Control

  • WallConfig.status differentiates between full-range precomputed (calculated) configurations and on-demand computed (partially_calculated) configurations.
    ✔ This ensures that reasonable-sized configurations are fully precomputed for immediate availability, while large configurations that are less likely to be queried are processed only when needed, preventing unnecessary storage and computation overhead.
    
    

  • WallConfigReference.status allows user-specific tracking of calculations, preventing conflicts in shared configurations.
    ✔ This ensures that different users can process the same configuration independently without interfering with each other’s results.
    
    

  • Celery processes calculations asynchronously, with ORM status fields WallConfig.status and WallConfigReference.status tracking progress at both configuration and user levels to ensure seamless task coordination.
    ✔ This structured status flow ensures that the ORM accurately represents computation progress, allowing a consistent transition from initialization to completion while keeping database queries in sync with ongoing processing.

Return to Top

• Precomputed vs. On-Demand Simulation Handling

  • The system uses MAX_SECTIONS_COUNT_FULL_RANGE_CACHING to determine whether a WallConfig should be fully precomputed on upload or calculated on demand.
  • If the number of sections is within this limit, the system precomputes all possible simulations for the entire range of num_crews values and stores the results in the database for fast retrieval.
  • If the number of sections exceeds this limit, simulations are triggered only when requested for a specific num_crews value, and the results are cached in both Redis and PostgreSQL.

  ✔ This approach ensures that small to medium wall configurations benefit from rapid retrieval while larger configurations remain efficient by avoiding unnecessary precomputations.

Return to Top

• Optimized Storage with JSON Fields

  • JSONField in WallConfig and WallProgress efficiently stores dynamic and computed data.
  • Reduces excessive table joins while maintaining flexibility for structured data storage.

  ✔ This format allows the system to handle variable-sized wall configurations efficiently, reducing the need for complex relational structures while maintaining query flexibility.

Return to Top

---

4. Database Concurrency Control

In The Wall API, concurrent database access from user interactions and multiprocessing can occasionally cause race conditions, leading to inconsistent modifications of database objects. To prevent such conflicts, the system applies multiple database-level concurrency control mechanisms, ensuring data consistency and safe parallel execution.

4.1 PostgreSQL Advisory Locks

Show Detailed implementation breakdown

def generate_db_lock_key(cache_lock_key: str) -> List[int]:
    xxhash_64bit = xxhash.xxh64(cache_lock_key).intdigest()
    lock_id1 = (xxhash_64bit & 0x7FFFFFFF)
    lock_id2 = ((xxhash_64bit >> 32) & 0x7FFFFFFF)
    return [lock_id1, lock_id2]

def acquire_db_lock(wall_db_lock_key: List[int]) -> bool:
    with connection.cursor() as cursor:
        cursor.execute('SELECT pg_try_advisory_lock(%s, %s);', wall_db_lock_key)
        db_lock_acquired = cursor.fetchone()
        return bool(db_lock_acquired and db_lock_acquired[0])

def release_db_lock(wall_db_lock_key: List[int]) -> None:
    with connection.cursor() as cursor:
        cursor.execute('SELECT pg_advisory_unlock(%s, %s);', wall_db_lock_key)

• Key Generation (generate_db_lock_key)

  • Uses xxhash to generate a 64-bit integer digest, which is then split into two 31-bit integers (lock_id1, lock_id2) to fit within PostgreSQL’s signed 32-bit integer range.
  • Ensures that different processes derive the same lock key for the same object, maintaining synchronization across operations.

  🗒️ The input cache_lock_key is generated reusing the Redis Cache Keys Construction Logic.
  
  

• Lock Acquisition (acquire_db_lock)

  • Uses pg_try_advisory_lock(%s, %s) to attempt acquiring a lock without blocking execution.
  • Returns True if the lock is acquired, False otherwise.
  • Prevents simultaneous modifications of the same database object.

• Lock Release (release_db_lock)

  • Calls pg_advisory_unlock(%s, %s) to release the lock, allowing other processes to acquire it.
  • Prevents stale locks and blocked transactions by ensuring timely lock release.
  
  

The outined locking mechanism:

• ✔ Implements lightweight, database-level advisory locking for concurrency control.
• Ensures that only one process at a time can:
  • ✔ Create a new object with a given unique hash lock key, avoiding integrity errors due to concurrent insert attempts.
  • ✔ Delete an existing object with its corresponding unique hash lock key, ensuring safe cleanup without conflicting deletions.

4.2 Transaction Management

• transaction.atomic() is consistently used to ensure that database operations execute as a single unit.
• If an error occurs within the block, all changes are rolled back, preventing partial updates and preserving database integrity.

✔ Prevents partially created objects if an error occurs during execution.

4.3 Row-Level Locking

• select_for_update() is used to lock database rows during critical updates, preventing concurrent modifications.
• It ensures safe, sequential modifications when multiple processes interact with the same database objects.
• Main Use Cases:
  • Locks WallConfig and WallConfigReference objects before modifying their statuses.

✔ Prevents conflicting updates in Celery workers by ensuring that only one worker at a time modifies a given database row.

---

Next:   Redis - In-Memory Cache & Celery Message Broker
Back to: Environment Variables Index

Return to Top
DTS Index
Home Page

---