Client Connection Defaults / Shared Library Preloading

jit_provider

• What it does: Specifies the Just-In-Time (JIT) compilation provider used by PostgreSQL.
• Why it matters: Significantly accelerates the execution of complex queries, especially those involving computationally intensive operations, aggregates, and WHERE clauses, by compiling them into native machine code. This leads to faster data processing and improved performance for analytical workloads.
• Ideal value & Best Practice: The default and recommended value is llvm. Ensure LLVM is installed on your server for this to work. If you encounter issues or your workload doesn't benefit from JIT compilation, you can set it to none. For most performance-critical databases, using llvm is highly advised.

local_preload_libraries

• What it does: Lists shared libraries that are preloaded into each backend process for unprivileged users.
• Why it matters: Allows non-superusers to benefit from specific extensions and custom functions immediately upon connection. This is crucial for security-conscious environments where you want to provide enhanced functionality without granting full superuser privileges, ensuring a balance between performance and security.
• Ideal value & Best Practice: Default is empty. Only specify libraries that are necessary for your application's functionality (e.g., auto_explain). Avoid preloading unnecessary libraries to minimize memory overhead and connection startup time.

session_preload_libraries

• What it does: Lists shared libraries to be preloaded at the start of each session. Unlike local_preload_libraries, this can be set by individual users for their own sessions.
• Why it matters: Offers flexibility by allowing user-specific or application-specific library loading without a server restart. This is ideal for debugging, monitoring, or loading utilities (like pg_stat_statements) for specific sessions, providing targeted insights without global impact.
• Ideal value & Best Practice: Default is empty. Perfect for loading diagnostic tools (e.g., pg_stat_statements) for monitoring performance in specific sessions. Changes require a session reconnect but not a server restart, making it highly flexible for dynamic environments.

shared_preload_libraries

• What it does: Lists shared libraries that must be preloaded at server startup into the shared memory.
• Why it matters: Essential for major extensions that deeply integrate with PostgreSQL's core, such as pg_stat_statements (for query tracking), timescaledb (for time-series data), or pg_cron (for job scheduling). These libraries cannot be loaded on the fly and are fundamental for adding advanced functionality to your database.
• Ideal value & Best Practice: Default is empty. Configure this based on the extensions you plan to use (e.g., shared_preload_libraries = 'pg_stat_statements, timescaledb'). Critical: Any change to this parameter requires a full server restart (not just a reload) to take effect. Plan modifications during maintenance windows.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial