If SHOW is how you read a PostgreSQL configuration value, SET is how you change one — at least temporarily. It’s a command I end up using constantly, whether I’m adjusting memory limits for a single heavy query, switching timezones for a session, or tweaking search paths while debugging a schema issue. It looks simple, but there are enough nuances around scope and persistence that it’s worth understanding properly.
This guide covers what SET does, its full syntax, the different variants (SET, SET LOCAL, SET SESSION), practical examples, and the mistakes people commonly make with it.
What Is the SET Command?
SET changes the value of a PostgreSQL runtime configuration parameter for the duration of your current session, or optionally just for the current transaction. It does not permanently change the server’s configuration — for that, you’d want ALTER SYSTEM, or editing postgresql.conf directly. Think of SET as a temporary override that only applies to the connection you’re currently using.
This distinction trips people up constantly: someone runs SET work_mem = '256MB';, closes their connection pool, reconnects, and wonders why the change disappeared. That’s expected behavior — it was never meant to be permanent.
Basic Syntax
SET [ SESSION | LOCAL ] parameter_name { TO | = } value | DEFAULT;
A few equivalent examples:
SET work_mem = '64MB';
SET work_mem TO '64MB';
SET SESSION work_mem = '64MB';
All three of the above do the same thing by default, since SET without a qualifier behaves like SET SESSION.
To reset a parameter back to its default:
SET work_mem TO DEFAULT;
or equivalently:
RESET work_mem;
SET vs SET LOCAL vs SET SESSION
This is the part that actually matters for correctness in application code, so it’s worth spelling out clearly.
SET SESSION (the default)
SET SESSION statement_timeout = '30s';
This changes the parameter for the rest of the current session — meaning until you disconnect, or until you explicitly reset it. If you’re running this manually in psql while debugging, this is usually what you want.
SET LOCAL
SET LOCAL statement_timeout = '30s';
This only applies within the current transaction block. Once the transaction commits or rolls back, the setting automatically reverts to whatever it was before. This is extremely useful inside functions or scripts where you want a temporary override without any risk of it leaking into subsequent queries on the same connection (which matters a lot with connection pooling, where the “session” might be reused by completely different logical requests).
BEGIN;
SET LOCAL work_mem = '512MB';
-- run your heavy query here
SELECT * FROM large_table ORDER BY some_column;
COMMIT;
-- work_mem is back to its previous value here
Why This Matters With Connection Pooling
If you’re using something like PgBouncer in transaction pooling mode, a “session” from the application’s perspective doesn’t map cleanly to a database session at all — connections get reused across different client requests. In that context, using plain SET (session-level) can leak settings into unrelated queries from a different logical request. SET LOCAL inside an explicit transaction avoids this problem entirely, since it’s automatically cleaned up at transaction end regardless of connection reuse.
Practical Examples
Example 1: Increasing Work Memory for a Heavy Query
SET work_mem = '256MB';
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;
RESET work_mem;
This gives a memory-hungry sort or aggregation more room to work in memory instead of spilling to disk, without permanently raising memory usage for every connection on the server.
Example 2: Adjusting Timezone for a Session
SET timezone = 'America/New_York';
SELECT now();
Useful when you need to see timestamps rendered in a specific timezone context without touching the server-wide default.
Example 3: Changing the Search Path
SET search_path TO app_schema, public;
SELECT * FROM users;
This tells PostgreSQL to look in app_schema first, then public, when resolving unqualified table names — handy when working with multi-schema databases without typing the schema prefix every time.
Example 4: Setting a Statement Timeout to Prevent Runaway Queries
SET statement_timeout = '5000'; -- milliseconds
SELECT * FROM huge_table WHERE unindexed_column = 'value';
If the query takes longer than 5 seconds, PostgreSQL will cancel it automatically. This is a great safety net for exploratory queries against large, unfamiliar tables.
Example 5: Using SET LOCAL Inside a Function
CREATE OR REPLACE FUNCTION heavy_report()
RETURNS TABLE(customer_id INT, total NUMERIC) AS $$
BEGIN
PERFORM set_config('work_mem', '512MB', true); -- true = local
RETURN QUERY
SELECT o.customer_id, SUM(o.amount)
FROM orders o
GROUP BY o.customer_id;
END;
$$ LANGUAGE plpgsql;
Here, set_config() is the functional equivalent of SET LOCAL, and it’s often used inside PL/pgSQL functions where the SET statement syntax itself isn’t directly usable in that form.
Example 6: Disabling a Specific Planner Feature Temporarily
SET enable_seqscan = OFF;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
SET enable_seqscan = ON;
This is a diagnostic trick — turning off sequential scans temporarily to see whether the planner will pick an available index instead, which helps confirm whether an index is even being considered as a viable option.
Example 7: Setting a Role’s Default Configuration (Different From SET, But Related)
While not strictly SET, it’s worth mentioning ALTER ROLE ... SET, since people often reach for SET when they actually want a persistent per-role default:
ALTER ROLE reporting_user SET statement_timeout = '60s';
This makes the timeout apply automatically every time reporting_user connects, without needing to run SET manually each session.
Common Use Cases
- Tuning memory for specific heavy operations like large sorts, joins, or aggregations.
- Setting statement timeouts to protect against runaway or accidental full-table scans.
- Adjusting timezone or date style for session-specific reporting needs.
- Changing the search path when working across multiple schemas.
- Testing planner behavior by toggling settings like
enable_seqscan,enable_nestloop, orenable_hashjoin. - Setting isolation levels with
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;for specific transactions that need stricter consistency guarantees. - Adjusting
client_encodingwhen dealing with legacy applications or specific character set requirements.
Troubleshooting Common Issues
The Setting Doesn’t Seem to Take Effect
Double-check whether you used SET LOCAL inside a transaction that has since committed — in that case, the change is expected to revert. Also verify you’re checking the value on the same connection where you ran SET; because SET is connection-scoped, checking from a different session (or a different pooled connection) will show the old default.
“Permission Denied to Set Parameter” Error
Some parameters are restricted to superusers only, and cannot be changed even at the session level by regular users — for example, certain low-level settings related to write-ahead logging. Check the context column in pg_settings to see which category a parameter falls into.
SELECT name, context FROM pg_settings WHERE name = 'work_mem';
If context shows something like superuser, only a superuser role can adjust it.
Setting Persists Longer Than Expected
If you meant to scope a change to a single transaction but it’s leaking into later queries, you probably used plain SET instead of SET LOCAL. Remember: plain SET (or SET SESSION) persists for the whole connection until reset or the connection closes.
Confusing SET With ALTER SYSTEM
SET never modifies postgresql.conf or postgresql.auto.conf. If you need the change to survive across all future connections and sessions, you want ALTER SYSTEM SET parameter_name = value; followed by SELECT pg_reload_conf(); (or a full restart for parameters that require one).
Best Practices
- Prefer
SET LOCALinside transactions and functions, especially in pooled connection environments, to avoid settings leaking across logically unrelated requests. - Always pair a temporary
SETwith aRESET(or wrap it in a transaction usingSET LOCAL) so you don’t forget you changed something mid-session. - Use
statement_timeoutliberally during ad hoc exploration of unfamiliar or large tables, to avoid accidentally locking up a shared server. - Don’t use
SETas a substitute for proper server tuning. If you find yourself setting the same parameter in every session, it probably belongs inpostgresql.conf,ALTER DATABASE, orALTER ROLEinstead. - Document any non-obvious
SETusage in application code, especially around search paths or isolation levels, since future maintainers won’t necessarily know why it’s there. - Check
pg_settings.contextbefore assuming a setting is changeable at the session level — some require a restart, and no amount ofSETwill touch them.
Setting Multiple Parameters Together
When you need to adjust several settings at once for a session or transaction, there’s no single combined syntax — you issue separate SET statements — but it’s common to group them logically at the top of a script or function for readability:
BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL statement_timeout = '10min';
SET LOCAL enable_seqscan = OFF;
-- run your analytical query here
COMMIT;
This pattern is especially common in reporting or analytics scripts where you want to temporarily loosen resource limits and nudge the planner, without affecting the rest of the application’s normal query behavior on the same database.
The set_config() Function as an Alternative
Everywhere you can use SET, there’s also a functional equivalent: set_config(setting_name, new_value, is_local). This is particularly useful inside PL/pgSQL functions or contexts where the SET statement’s syntax isn’t directly usable, or where you want to build the parameter name or value dynamically.
SELECT set_config('work_mem', '128MB', false); -- false = session-level, like SET SESSION
SELECT set_config('work_mem', '128MB', true); -- true = transaction-level, like SET LOCAL
The companion function current_setting(setting_name) lets you read a value back, functioning similarly to SHOW but usable directly inside SQL expressions and function bodies:
SELECT current_setting('work_mem');
This pairing of set_config() and current_setting() is genuinely useful when you need to save a parameter’s current value, temporarily change it, and then restore the original — a common pattern inside more complex procedural functions.
Application-Level Patterns for Using SET
In real applications, SET often shows up in a few recurring patterns worth knowing about:
Per-request timeout enforcement: Many web frameworks configure a connection pool to run SET statement_timeout = '...' immediately after checking out a connection, ensuring no single request can accidentally run an unbounded query against the database.
Multi-tenant schema switching: In a multi-tenant architecture using PostgreSQL schemas to separate tenant data, it’s common to see SET search_path TO tenant_schema, public; run at the start of each request, ensuring queries resolve against the correct tenant’s tables without needing to fully qualify every table reference.
Read-only enforcement for reporting connections: SET default_transaction_read_only = ON; (or configuring it at the role level) is sometimes used to add a safety net for connections that should never perform writes, such as those used by reporting tools or BI dashboards.
Session Variables Versus True Application Variables
It’s worth clarifying a common point of confusion: PostgreSQL’s SET mechanism is designed for configuration parameters, not general-purpose application variables. While some people creatively repurpose custom configuration parameters (using a namespaced, unrecognized parameter name, which PostgreSQL allows as a “custom” GUC) to pass small values between application code and functions, this isn’t the same thing as a first-class variable system, and abusing it too heavily for application logic can make code harder to follow.
SET myapp.current_user_id = '42';
SELECT current_setting('myapp.current_user_id');
This pattern is genuinely useful in specific cases — for example, passing the current application user’s ID into row-level security policies — but it’s worth using deliberately rather than as a general substitute for proper application state management.
Setting Transaction Characteristics
Beyond generic configuration parameters, SET also covers transaction-specific characteristics through a related but distinct syntax:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
-- your queries here
COMMIT;
This is functionally similar to SET LOCAL default_transaction_isolation = 'serializable';, but expressed using the SQL-standard SET TRANSACTION syntax, which some teams prefer for portability reasons or because it reads more clearly as transaction-scoped rather than a general configuration override. Both approaches only affect the current transaction and automatically revert once it ends.
You can also set the defaults for every future transaction in a session:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
This is useful for connections dedicated to a specific purpose — for example, a reporting connection that should always run under REPEATABLE READ for consistent snapshot reads across multiple queries within the same logical report generation.
Persisting SET Behavior at the Role or Database Level
While SET itself is always temporary, it pairs naturally with ALTER ROLE and ALTER DATABASE, which let you establish a persistent default that then gets applied automatically as though a SET had been run at the start of every relevant session:
ALTER ROLE etl_user SET work_mem = '512MB';
ALTER ROLE reporting_user SET default_transaction_read_only = ON;
ALTER DATABASE analytics_db SET search_path TO analytics, public;
This is a genuinely useful pattern for establishing sane, purpose-specific defaults without needing every application or script connecting under that role to remember to run the equivalent SET command manually every single time. You can verify what’s configured with:
SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'etl_user';
A Practical Debugging Workflow Combining SET and SHOW
In practice, SET and SHOW are almost always used together as a pair during troubleshooting: check the current value, temporarily change it, observe the effect on a query’s behavior via EXPLAIN ANALYZE, then decide whether the change is worth making permanent through ALTER SYSTEM, ALTER ROLE, or ALTER DATABASE, or whether it should just remain a one-off session-level adjustment.
SHOW random_page_cost;
SET random_page_cost = 1.1;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
RESET random_page_cost;
This lets you directly compare how a planner cost parameter change affects the chosen query plan, without any risk of accidentally leaving the change in place for other sessions or future connections.
A Quick Reference for Common SET Targets
For everyday use, a handful of parameters account for most SET usage in practice: work_mem for tuning memory available to sorts and hashes, statement_timeout for guarding against runaway queries, search_path for controlling schema resolution, timezone for session-specific time display, and the enable_* family (enable_seqscan, enable_nestloop, enable_hashjoin, and similar) for diagnostic planner experiments. Keeping this short list in mind covers the overwhelming majority of situations where reaching for SET actually solves the problem at hand, with more exotic parameters becoming relevant only in fairly specific tuning or debugging scenarios.
Wrapping Up
SET gives you fine-grained, temporary control over PostgreSQL’s behavior without touching global server configuration. The key distinction to internalize is scope: plain SET (or SET SESSION) lasts for your whole connection, while SET LOCAL is scoped tightly to the current transaction and cleans up after itself automatically.
Used well — especially with connection pooling in mind — SET is one of the safest and most flexible tools you have for tuning behavior on a per-query or per-session basis, without risking unintended side effects on the rest of your database.
