SET vs ALTER in PostgreSQL

In PostgreSQL, SET and ALTER are two SQL commands that can be used to modify configuration settings, but they differ in their scope, persistence, and use cases.

SET Command

The SET command is used to temporarily change a configuration parameter within a session. When you use SET, the change will only apply to the current session and will not persist beyond it. This means that once the session ends, the configuration value will revert back to its default or the value set in the postgresql.conf file.

Use Cases

• Temporary Session Changes: Ideal for adjusting parameters on the fly for a particular session or user without affecting the global configuration.
• Testing or Debugging: You can use SET to experiment with different settings without permanently modifying the server configuration.

Example

SET work_mem = '64MB';

In this example, the work_mem setting is adjusted for the current session to 64MB. Once the session ends, the change will be discarded.

Important Points

• Changes made with SET only affect the current session.
• SET is typically used for session-level settings.
• Changes are not persistent across server restarts.
• Syntax: SET parameter_name = value;

ALTER Command

The ALTER command is more powerful and can be used to modify the server configuration in two main ways:

• ALTER SYSTEM: Used to change persistent configuration parameters at the system level, which affect the entire database instance. The changes made using ALTER SYSTEM are stored in the postgresql.auto.conf file and persist across server restarts.
• ALTER DATABASE / ALTER ROLE: These variations of the ALTER command allow you to modify database-specific or role-specific settings.

Use Cases

• Persistent Configuration Changes: If you want to make configuration changes that persist across server restarts, you would use ALTER SYSTEM.
• Modifying Role or Database-Level Parameters: You can use ALTER to change settings related to specific roles or databases.

Example: ALTER SYSTEM

ALTER SYSTEM SET work_mem = '128MB';

This command will modify the work_mem parameter globally for the entire PostgreSQL instance, and the change will be stored in the postgresql.auto.conf file.

The change will remain in effect even after a server restart.

Example: ALTER ROLE

ALTER ROLE myuser SET work_mem = '128MB';

This command sets the work_mem for a specific role (myuser), and the change will apply every time myuser connects to the database.

Example: ALTER DATABASE

ALTER DATABASE mydb SET work_mem = '128MB';

This command sets the work_mem for a specific database (mydb), so any user connected to this database will have this setting applied.

Important Points

• ALTER SYSTEM modifies configuration parameters persistently, while ALTER ROLE and ALTER DATABASE modify settings for specific users or databases.
• Changes made with ALTER SYSTEM are stored in the postgresql.auto.conf file and survive server restarts.
• ALTER SYSTEM changes are usually applied by reloading the configuration (using pg_reload_conf() or sending a SIGHUP signal).
• ALTER can be used to adjust user- or database-specific settings without modifying the global server configuration.

Key Differences Between SET and ALTER

When to Use Each Command

• Use SET when:
  • You need to temporarily adjust a configuration parameter for the current session (e.g., debugging, testing, or fine-tuning a query for a session).
  • You do not want to affect the global configuration or other users.
• Use ALTER SYSTEM when:
  • You need to make persistent changes to configuration parameters that should apply to all sessions or across server restarts.
  • You are an administrator and need to adjust global settings for performance, memory management, logging, etc.
• Use ALTER ROLE / ALTER DATABASE when:
  • You want to apply specific configurations for a particular role or database (e.g., changing memory settings or timeouts for a specific user or database).

Conclusion

In PostgreSQL, both SET and ALTER serve important roles when it comes to modifying server configurations. SET is best suited for temporary changes at the session level, while ALTER SYSTEM is used for permanent, global configuration changes.

When making persistent changes, it’s crucial to choose the appropriate command based on whether the changes should be session-based, role-based, database-based, or global across the entire instance.

For permanent system-wide changes, ALTER SYSTEM is typically the right choice, but always ensure that you understand the scope and implications of your changes to maintain optimal performance and avoid unintended consequences.