Method insert_comments_batch

core/postgres_database.py:965–1202

Insert multiple comments in optimized batches using PostgreSQL executemany. Args: comments: List of comment dictionaries with Reddit JSON structure progress_callback: Optional callback function(processed, total) for progress updates auto_tune_batch_size:

(
        self,
        comments: list[dict[str, Any]],
        progress_callback: Callable[[int, int], None] | None = None,
        auto_tune_batch_size: bool = True,
        initial_batch_size: int = 1000,
    )

    @retry_with_exponential_backoff(max_retries=5, initial_delay=2.0, max_delay=60.0)
    def insert_comments_batch(
        self,
        comments: list[dict[str, Any]],
        progress_callback: Callable[[int, int], None] | None = None,
        auto_tune_batch_size: bool = True,
        initial_batch_size: int = 1000,
    ) -> tuple[int, int]:
        """Insert multiple comments in optimized batches using PostgreSQL executemany.

        Args:
            comments: List of comment dictionaries with Reddit JSON structure
            progress_callback: Optional callback function(processed, total) for progress updates
            auto_tune_batch_size: Whether to auto-tune batch size based on performance
            initial_batch_size: Starting batch size (larger than SQLite)

        Returns:
            Tuple of (successful_inserts, failed_inserts)
        """
        if not comments:
            return 0, 0

        successful = 0
        failed = 0
        skipped = 0  # Track comments without valid IDs
        total_comments = len(comments)
        current_batch_size = initial_batch_size

        start_time = time.time()

        try:
            with self.pool.get_connection() as conn:
                # Disable synchronous_commit for bulk insert performance (10-20% faster)
                # Transaction safety is maintained - rollback still works correctly
                with conn.cursor() as cur:
                    cur.execute("SET LOCAL synchronous_commit = OFF")

                    # Defer foreign key constraint validation to prevent batch-wide failures
                    # This allows valid comments to succeed even if some reference missing posts
                    # Invalid comments will be rejected at COMMIT time (not during INSERT)
                    try:
                        cur.execute("SET CONSTRAINTS comments_post_id_fkey DEFERRED")
                    except Exception as e:
                        # Constraint might not be deferrable yet (will be after schema update)
                        print_warning(
                            f"Could not defer foreign key constraint (this is expected before schema update): {e}"
                        )

                for batch_start in range(0, total_comments, current_batch_size):
                    batch = comments[batch_start : batch_start + current_batch_size]
                    batch_size = len(batch)

                    batch_start_time = time.time()

                    try:
                        # Use PostgreSQL COPY protocol for true streaming (no buffering)
                        copy_buffer = StringIO()
                        records_prepared = 0