General Pipeline Best Practices

• Resource management: Use resource pools to isolate workloads. Pipelines waiting in the queue consume slots reserved for background pipelines, as defined by MAX_CONCURRENCY and MAX_QUEUE_DEPTH. Control the maximum number of pipelines that can run concurrently using the pipelines_max_concurrent engine variable.

• Aggregator pipelines: For sources with low parallelism (such as single file S3 loads or single-partition Kafka topics), use aggregator pipelines. Aggregator pipelines simplify load behavior and improve performance in these scenarios.

• Use the following engine variables to control pipeline limits and defaults:

  • pipelines_max_concurrent: Specifies the maximum number of pipelines that can run concurrently.

  • pipelines_batch_interval: Controls the default batch frequency (unless overridden at the pipeline level).

  • pipelines_stop_on_error: Determines whether a pipeline stops or continues after encountering errors.

  • pipelines_max_retries_per_batch_partition: Configures retries for batch partitions to improve resilience.

• Avoid using IGNORE or SKIP ALL ERRORS options unless necessary, because they can generate many warnings which can slow down pipelines.

• Avoid using ENCLOSED BY or OPTIONALLY ENCLOSED BY clauses in CREATE PIPELINE … LOAD DATA statement when performance is critical, since these options add parsing overhead.

• If the transform is slow, consider using a CREATE PIPELINE … SET … WHERE statement as an alternative to a transform.

Stored Procedures Best Practices

Avoid operations in stored procedures that force processing on the aggregator node. These operations increase memory usage and reduce performance.

• For Example, using COLLECT() with large result sets places heavy load on the aggregator.

• When inserting into reference tables, using auto-increment columns, or performing non-parallel operations, design the logic carefully to balance workload between leaf nodes and aggregators.

Refer to Writing Efficient Stored Procedures for Pipelines for more information.

Kafka-Specific Best Practices

• Configure secure connections with appropriate authentication options.

  • security.protocol (for example, SASL_SSL)

  • sasl.mechanism (PLAIN, SCRAM, etc.)

  • CA certificates for SSL validation

• Use the CONFIG clause in CREATE PIPELINE … KAFKA to tune throughput and latency.

  • Common options include fetch.max.bytes, fetch.min.bytes, and socket.timeout.ms.

• Use MAX_OFFSETS_PER_BATCH_PARTITION in the CREATE PIPELINE statement to limit the number of offsets processed per partition in each batch. This setting helps control batch size and manage resource utilization.

• If using a schema registry, install SSL certificates so the pipeline can validate and retrieve schema definitions.

• To ensure in-order ingestion (for example, with upserts), disable out-of-order optimization:

  • Add DISABLE OUT_OF_ORDER OPTIMIZATION in the CREATE PIPELINE statement, or

  • Use ALTER PIPELINE to apply the setting after creation.

• Define multiple Kafka brokers in the pipeline to increase availability and redundancy.

• Monitor Kafka pipeline errors, such as missing topics, missing partitions, or authentication failures.

• Verify that topics exist and the pipeline has the necessary permissions.

• Kafka pipelines do not check for the latest offsets when stopped. Files added after the pipeline stops do not appear in offsets. Running ALTER PIPELINE <pipeline_name> SET OFFSETS LATEST while the pipeline is stopped does not change the offset count.

S3-Specific Best Practices

• Ensure AWS credentials include the required permissions, such as s3:GetObject and s3:ListBucket.

• Use the CONFIG and CREDENTIALS clauses to specify parameters such as region, role_arn, or temporary credentials based on deployment requirements.

• Use wildcards or globbing in S3 paths to capture specific file sets.

  • Example: bucket/path/*.json for files in one directory, or bucket/folder/** to include files recursively.

• The suffixes parameter in the CONFIG clause provides an alternative way to specify the file extensions to load.

• By default, pipelines store metadata for all processed files, which can grow significantly over time. Enable the OFFSETS METADATA GC feature to automatically remove metadata for old files.

• Control pipeline parallelism with MAX_PARTITIONS_PER_BATCH to avoid overwhelming leaf nodes or consuming excessive memory.

• Adjust BATCH_INTERVAL to balance ingestion latency and resource usage. Short intervals increase overhead, while longer intervals reduce load.

• Use the file_time_threshold parameter in the pipeline configuration to load only recently added files instead of processing the entire archive. Refer to S3 Configurations for more information.

• Ensure that most files are approximately the same size and not too small. Files in the tens or hundreds of megabytes are acceptable. Large numbers of very small files create overhead as SingleStore tracks metadata for each file. A limited number of small files is acceptable, but having a few files significantly larger than the rest can reduce loading performance by limiting parallelism.