Choose Between PSQL, Wasm-based, and External Functions

SingleStore recommends using PSQL functions to implement database logic and Wasm-based functions for complex or existing C/C++ or Rust code. Use external functions to run functions in languages that are not supported by Wasm, implement highly complex C/C++ functions which require native code, or interact with external services or APIs.

Using PSQL Functions

For standard SQL operations, procedural logic, and performance-critical implementations, SingleStore recommends using PSQL functions. PSQL functions are run within the SingleStore engine and offer high performance for operations that can be expressed in SQL or Procedural SQL. PSQL functions are ideal for encapsulating reusable business logic that can be expressed in SQL (or PSQL) and run within the database engine, thereby avoiding the use of external compute resources.

Using Wasm-based Functions

Use Wasm-based functions to implement complex logic, data structures, or existing C/C++ or Rust code. Wasm-based functions leverage existing code libraries to perform computational tasks within the database engine that may be challenging to express in SQL. In many cases, Wasm-based functions offer significant performance improvements over external functions.

Wasm-based functions in SingleStore do not support runtime capabilities that the database does not provide out of the box, such as making system calls, opening files, opening sockets, sending network messages, creating processes, or creating threads. SingleStore does not support these operations to protect the security and integrity of the system.

Using External Functions

Use external functions to execute logic that cannot be implemented within the database, when the language isn't supported by Wasm (C#, Java, Python, etc.), or where only native code assures high performance. You can also use external functions to integrate with external services or APIs, implement runtime capabilities, or perform computational tasks using libraries or resources that are not available within the SingleStore engine. For example, use external functions to implement a large C/C++ application with multiple dynamic link libraries (DLLs) that cannot be compiled using Wasm.

External functions may have a higher overhead (higher resource costs or lower performance) than PSQL or Wasm-based functions because of network latency and the resource costs associated with serializing and deserializing data between SingleStore and external services. Conversely, external functions written in native C/C++ or Rust environments may provide performance improvements over Wasm for computation-intensive applications (such as mathematical software) because they can run directly as machine code instead of being interpreted by the Wasm runtime.

Summary

The following table summarizes the recommended use case and limitation of each function type:

Function Type	Description	Recommended Use Case	Limitation
Procedural SQL (PSQL)	Runs SQL or PSQL code within the SingleStore engine	Standard SQL operations, procedural logic, and performance-critical implementations	Implementing complex logic or data structures is not efficient Cannot run code from other languages
Wasm-based	Imports existing C/C++ or Rust code and runs the code within the SingleStore engine	Implement complex data structures using C/C++ or Rust	Does not have runtime capabilities, such as, making system calls, opening files, opening sockets, etc. Only supports C/C++ and Rust languages currently
External	Calls a function that runs outside of the SingleStore engine	Native code or environment delivers performance improvements Run functions in languages not supported by Wasm Connect with external APIs and services	Higher overhead, resource costs Lower performance because of network latency