Offensive Security Researcher & Quantum Cryptography Analyst

How to Use the SHOW Command in PostgreSQL

Abdul Wahab Junaid
How to Use the SHOW Command in PostgreSQL

Every PostgreSQL installation is running with a huge number of configuration settings under the hood — things like memory limits, timezone settings, logging behavior, and connection limits. Most of the time you don’t need to think about these at all. But when you do need to check one, SHOW is the fastest way to peek at what your server is actually configured to do right now.

In this article, I’ll walk through what SHOW does, how to use it, what it returns, how it relates to other configuration commands like SET and ALTER SYSTEM, and how I use it day-to-day when troubleshooting or tuning a database.

What Is the SHOW Command?

SHOW is a PostgreSQL-specific SQL command (it’s not part of the SQL standard) that displays the current value of a runtime configuration parameter. PostgreSQL has hundreds of these parameters, covering everything from work_mem to timezone to max_connections. Rather than digging through postgresql.conf on disk, you can just ask the live server what value it’s currently using.

This matters because a parameter’s effective value can come from several layers: the config file, command-line startup options, environment-specific overrides, ALTER DATABASE or ALTER ROLE settings, or a session-level SET command. SHOW cuts through all of that and tells you what’s actually in effect for your current session.

Basic Syntax

SHOW parameter_name;

For example, to check your current timezone setting:

SHOW timezone;

This might return something like:

 TimeZone 
----------
 UTC
(1 row)

You can also request every parameter at once:

SHOW ALL;

This returns a full table with three columns: name, setting, and description, covering every configuration parameter PostgreSQL knows about — often 300+ rows depending on your version and what extensions are loaded.

Parameters and Variants

SHOW parameter_name

The most common usage. Just pass the exact name of the setting you want to inspect. Parameter names are case-insensitive and typically use snake_case, like shared_buffers, max_connections, or log_min_duration_statement.

SHOW shared_buffers;
SHOW max_connections;
SHOW log_min_duration_statement;

SHOW ALL

Displays every current parameter, its current setting, and a short description. This is useful when you’re auditing a server’s configuration or comparing two environments (say, staging vs. production) to spot discrepancies.

SHOW ALL;

Because this can return hundreds of rows, it’s common to pipe the output somewhere or filter it using psql‘s own tools, or query the underlying view directly (covered below) with a WHERE clause.

The Underlying View: pg_settings

Behind the scenes, SHOW is really just a convenient wrapper around the pg_settings system view. If you need more control — filtering, sorting, or joining with other data — query pg_settings directly instead:

SELECT name, setting, unit, context, short_desc
FROM pg_settings
WHERE name LIKE '%memory%';

This is something SHOW alone can’t do, since SHOW doesn’t support WHERE clauses. If you ever find yourself wanting to search or filter configuration parameters, pg_settings is the way to go.

Practical Examples

Example 1: Checking Memory Settings

When you’re troubleshooting slow queries or planning a memory tuning pass, these are usually the first things I check:

SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;

Each of these plays a different role: shared_buffers is Postgres’s own internal cache, work_mem governs how much memory a single sort or hash operation can use before spilling to disk, maintenance_work_mem affects operations like VACUUM and index builds, and effective_cache_size is a hint to the query planner about how much OS-level caching is realistically available.

Example 2: Verifying Connection Limits

SHOW max_connections;

If your application is throwing “too many connections” errors, this is the first thing to check, followed by actually counting current connections:

SELECT count(*) FROM pg_stat_activity;

Example 3: Checking Timezone and Locale Settings

SHOW timezone;
SHOW lc_collate;
SHOW lc_ctype;

These matter a lot for applications dealing with timestamps and sorting behavior across different locales — a mismatch here has caused more than a few confusing bugs around date handling.

Example 4: Logging Configuration

SHOW log_statement;
SHOW log_min_duration_statement;
SHOW logging_collector;

If you’re trying to figure out why you’re not seeing slow query logs, checking these three settings together usually explains it. For instance, log_min_duration_statement defaults to -1, meaning no statements get logged based on duration at all, until you explicitly set a threshold.

Example 5: Checking the PostgreSQL Version

SHOW server_version;

This returns something like 16.3, letting you confirm exactly what version you’re connected to, which is handy when scripting against multiple environments that might not all be on the same release.

Example 6: Searching for a Setting You Don’t Know the Exact Name Of

Since SHOW requires an exact parameter name, if you’re not sure what it’s called, query pg_settings instead:

SELECT name, setting, short_desc
FROM pg_settings
WHERE name ILIKE '%vacuum%';

This returns every vacuum-related setting, along with a description, which is much friendlier than guessing exact parameter names.

How SHOW Relates to SET and ALTER SYSTEM

It helps to understand the full picture of how configuration works in PostgreSQL, since SHOW is really just the read side of it:

A common workflow looks like this:

SHOW work_mem;              -- check current value
SET work_mem = '64MB';      -- change it for this session
SHOW work_mem;              -- confirm the change took effect

If you want the change to persist beyond your session, you’d use ALTER SYSTEM SET work_mem = '64MB'; followed by SELECT pg_reload_conf();, and then verify with SHOW work_mem; in a fresh session.

Common Use Cases

Troubleshooting Common Issues

“unrecognized configuration parameter” Error

This happens when you mistype a parameter name, or reference a setting that belongs to an extension that isn’t currently loaded. For example, SHOW pg_stat_statements.max; will fail unless the pg_stat_statements extension has actually been loaded via shared_preload_libraries. Double-check spelling and confirm the relevant extension is active.

The Value Doesn’t Match What’s in postgresql.conf

This is one of the most common points of confusion. The file on disk isn’t necessarily what’s in effect. Session-level SET commands, role-level or database-level overrides, and even command-line startup flags can all take precedence over the file. SHOW always tells you the truth about what’s currently active — trust it over the file.

SHOW ALL Output Is Overwhelming

Since SHOW ALL can return several hundred rows, it’s often more useful to query pg_settings with a WHERE clause targeting the area you actually care about, rather than scrolling through everything.

Changes Made with SET Don’t Persist

This is expected behavior, not a bug. SET only affects your current session (or current transaction if you used SET LOCAL). Once you disconnect, the setting reverts. If you need a permanent change, use ALTER SYSTEM, ALTER DATABASE, or edit postgresql.conf directly, then reload or restart as appropriate.

Best Practices

  1. Use SHOW before making tuning changes, so you have a documented baseline to compare against afterward.
  2. Prefer pg_settings over SHOW ALL when you need to filter, sort, or programmatically process configuration data.
  3. Check context in pg_settings before attempting a change — some parameters require a full server restart (context = 'postmaster'), while others can be changed with a simple reload or even per-session.
  4. Don’t assume the config file reflects reality. Always verify with SHOW on a live connection, especially in environments where multiple people might have run ad hoc SET or ALTER SYSTEM commands.
  5. Script comparisons between environments using pg_settings, exporting the results from both staging and production, then diffing them to catch configuration drift early.
  6. Combine SHOW with pg_stat_activity when diagnosing connection or resource-related issues, rather than relying on configuration values alone.

Understanding Parameter Context and Where Values Come From

One thing that trips people up with SHOW is not realizing that a single parameter’s effective value can be layered from several different sources, each with different precedence. Roughly from lowest to highest priority:

  1. The compiled-in default (baked into the PostgreSQL binary itself).
  2. postgresql.conf on disk.
  3. postgresql.auto.conf (written by ALTER SYSTEM).
  4. Command-line arguments passed when the server was started.
  5. Per-database settings, set via ALTER DATABASE ... SET.
  6. Per-role settings, set via ALTER ROLE ... SET.
  7. Settings from the connecting client/application (some drivers set things like client_encoding automatically).
  8. Session-level SET commands.
  9. Transaction-level SET LOCAL commands, which take the highest precedence but only last for the current transaction.

SHOW always reflects the final, resolved value after all these layers have been applied. This is genuinely useful when debugging — if a setting isn’t behaving as expected, it’s often because something further down this list (a per-role setting nobody remembered, for instance) is silently overriding what you’d expect from the config file alone.

To check for role- or database-level overrides directly, rather than relying on SHOW alone:

SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL;
SELECT datname, datconfig FROM pg_database WHERE datconfig IS NOT NULL;

These queries reveal any persistent per-role or per-database overrides that might explain unexpected SHOW output.

Using SHOW From the Command Line With psql

If you’re scripting against PostgreSQL and want a single value without the decorative table formatting psql normally adds, you can use psql‘s tuples-only mode:

psql -t -c "SHOW work_mem;" mydb

The -t flag strips headers and row counts, giving you just the raw value, which is handy for shell scripts that need to capture a configuration value programmatically.

Comparing SHOW Output Across Multiple Servers

A common real-world task is confirming that a fleet of PostgreSQL servers (say, several read replicas, or staging vs. production) are configured consistently. Since SHOW ALL and pg_settings are both queryable, you can automate this comparison:

\copy (SELECT name, setting FROM pg_settings ORDER BY name) TO 'server_a_settings.csv' CSV HEADER

Running this against each server and then diffing the resulting CSV files is a straightforward way to catch configuration drift that might otherwise only surface as a mysterious performance difference or behavioral inconsistency between environments.

Category Grouping in pg_settings

pg_settings includes a category column that groups related parameters together, which is often more useful than an alphabetical list when you’re trying to explore a particular area of configuration:

SELECT DISTINCT category FROM pg_settings ORDER BY category;

This returns groupings like “Resource Usage / Memory”, “Write-Ahead Log / Checkpoints”, “Query Tuning / Planner Method Configuration”, and so on. Once you know the category you care about, filtering by it gives you a focused view:

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE category = 'Resource Usage / Memory';

This is a much friendlier way to explore unfamiliar configuration areas than scrolling through the full output of SHOW ALL.

SHOW Inside Functions and Scripts

If you need to reference a configuration value programmatically inside a PL/pgSQL function, the SHOW statement itself isn’t directly usable — instead, you’d use current_setting(), which is the functional equivalent:

CREATE OR REPLACE FUNCTION log_current_timezone()
RETURNS TEXT
AS $$
BEGIN
    RETURN 'Current session timezone is: ' || current_setting('timezone');
END;
$$ LANGUAGE plpgsql;

SELECT log_current_timezone();

current_setting() also accepts an optional second boolean parameter that, when true, suppresses the error normally raised for an unrecognized parameter name and returns NULL instead — useful defensive coding when checking for an optional custom setting that might not always be defined:

SELECT current_setting('myapp.feature_flag', true);

Resetting Parameters Back to Defaults

Closely related to SHOW is knowing how to reset a value once you’ve changed it, particularly useful after a debugging session where you’ve adjusted several settings and want a clean slate without disconnecting:

RESET work_mem;
RESET ALL;

RESET ALL reverts every session-level parameter you’ve changed back to whatever the session started with (the database/role/system defaults), which is a handy way to clean up after an investigative session where you’d changed several planner or memory settings and want to confirm your findings still hold under normal configuration.

Checking Settings That Require a Restart Versus a Reload

Not every configuration change takes effect immediately, and SHOW alone won’t tell you whether a value you’re seeing reflects a pending change that hasn’t been applied yet or the fully active configuration. The context column in pg_settings clarifies this:

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections', 'log_min_duration_statement');

Knowing this ahead of time saves you from editing postgresql.conf, running ALTER SYSTEM, or issuing a reload, and then being confused when SHOW still reports the old value because the parameter actually needed a full restart.

SHOW in Everyday Troubleshooting Habits

Over time, a handful of SHOW checks tend to become second nature for anyone regularly administering PostgreSQL. When a query behaves unexpectedly around dates, checking SHOW timezone; and SHOW datestyle; is often the fastest way to rule out a locale-related surprise. When connections start failing under load, SHOW max_connections; alongside a live count from pg_stat_activity quickly confirms whether you’ve simply hit a configured ceiling. When a report runs long, checking SHOW work_mem; and SHOW effective_cache_size; gives a quick sense of whether the planner has a realistic picture of available memory to work with.

None of these checks are complicated in isolation, but building the habit of reaching for SHOW early in a troubleshooting session — before diving into query rewrites or index changes — often saves time by ruling out configuration-level explanations before assuming the problem is something more structural.

Wrapping Up

SHOW is one of the simplest commands in PostgreSQL, but it’s also one of the most useful for day-to-day administration. It gives you a direct, no-nonsense way to check exactly what your server is doing right now, cutting through the layers of configuration files, session overrides, and role- or database-specific settings that can otherwise make troubleshooting confusing.

Get comfortable pairing SHOW with pg_settings for more advanced filtering, and you’ll have a solid foundation for diagnosing performance issues, verifying environment consistency, and confirming that your configuration changes actually took effect.

Exit mobile version