Best Practices for PostgreSQL Performance Tuning

Introduction

PostgreSQL is an open-source relational database server trusted for its performance and stability. However, performance can degrade over time as the data and workload grow. PostgreSQL performance problems often relate to underprovisioned resources, poor database design (such as unindexed data), or configuration settings that do not match the workload.

To minimize performance issues and quickly address any that arise, it is crucial to design the database's architecture and schema for lasting performance, then continuously monitor its performance. This article focuses on tuning and best practices that help PostgreSQL use its resources (CPU, memory, and disk) efficiently, support high-performance indexing, enable effective vacuuming and query monitoring, and maintain scalability as the workload and data volume grow. The advice here is aimed at system architects, database designers, and DevOps engineers who are either planning new database applications or seeking to maximize performance on existing ones.

The best practices described below will help you configure and operate PostgreSQL for high-performance workloads. However, architectural topics such as sharding, time-series optimizations, and advanced query planning are outside the scope of this article.

Summary of key PostgreSQL performance tuning best practices

Best Practice	Description
Create a performance baseline	Obtain performance information about your current set-up, to compare against after making any changes
Provision adequate hardware for the workload	Choose CPU, memory, and storage based on access patterns, concurrency, and dataset size
Tune key configuration parameters	Adjust settings such as work_mem, shared_buffers, effective_cache_size, and parallelism settings for your workload and hardware
Use efficient and minimal data types	Design tables to use smaller, precise types to reduce memory, disk, and cache usage
Design indices according to use	Select the right index types (B-tree, GIN, BRIN, etc.), avoid redundant or bloated indices, and regularly review and prune them
Monitor and tune autovacuum	Configure autovacuum to prevent bloat and query slowdowns by tuning thresholds and monitoring vacuum lag for large or frequently updated tables
Use a connection pooler	Make use of tools like PgBouncer to avoid wasting RAM on large numbers of idle connections and prevent overwhelming the server when demand spikes
Track function performance and volatility	Maintain proper levels of function volatility and use can have a surprising impact, especially on indices, views, and row-level security (RLS) policies
Continuously monitor system and query performance	Use tools such as pg_stat_statements, EXPLAIN, and OS-level metrics to detect memory pressure, I/O bottlenecks, and query lag

Create a performance baseline

When optimizing an established PostgreSQL database, it is a good idea to record information about the current usage and load. Establishing a baseline empirically defines what is usual in your database system. The baseline then serves as a starting point to spot regressions, verify improvements, and set thresholds for alerts. The baseline can be recorded in one or more files or placed directly in a table in the database so it can be easily queried, aggregated, and graphed.

The most basic information you will need is OS metrics, such as CPU, I/Os, memory utilization, and network throughput, which in Linux can be obtained in a shell script using the mpstat, iostat, and free commands and by reading from /proc/net/dev, respectively. Collecting metrics at regular intervals (e.g., every five seconds for a day) will provide essential insights into what hardware resources are currently being underutilized or overutilized.

Database-specific information can be obtained from the system tables pg_stat_activity and pg_locks; the former returns information about the state and current query for all current sessions in the database, while the latter provides detailed information about current locks. Recording how many of which locks are caused by which queries and which other queries they are blocking can provide important insights into the interplays of queries. That's especially true when transactions are long-lived (e.g., when they are started and committed by an application instead of being handled within functions and stored procedures). This information should also be stored in a file or table to establish a usage baseline to match against the OS metrics.

SELECT pid, usename, state, wait_event_type, wait_event, query 
FROM pg_stat_activity 
WHERE state <> 'idle';

Finally, obtaining caching and statistics information from PostgreSQL can also prove useful. For example, by querying the pg_stat_database system table, one can obtain buffer cache efficiency (blks_hit vs. blks_read), rows returned by sequential and index scans (tup_returned), index scans only (tup_fetched), workload intensity (tup_inserted, tup_updated, tup_deleted), and transaction throughput and conflicts (xact_commit and xact_rollback for how many transactions succeeded vs. failed and deadlocks for the count of deadlocks detected).

Once a baseline is established, it is a good idea to continue collecting these statistics to compare against the current usage patterns, alert when current metrics show anomalies compared to the baseline, and define new baselines after system changes.

Provision adequate hardware for the workload

Properly provisioning adequate hardware for your PostgreSQL database starts with a good estimate of the pressures it will be subject to in production, e.g., the number of concurrent users, expected throughput, amount of data involved in typical queries, transaction processing pressures, and expected peak activity times. This will provide a sensible starting point in terms of the number of CPU cores, RAM allocation, and storage performance level required. However, chances are the estimate will result in over- or under-allocation of resources, so it is important to establish a performance baseline, then continuously monitor the system.

When PostgreSQL is allocated with insufficient CPU cores for a normal load, different simultaneous queries will have to share a CPU, resulting in both being significantly impacted and taking much longer to complete. Adding more cores can improve performance for queries that can be parallelized, and will help ensure the system scales effectively as more users query the database.

PostgreSQL uses memory for a variety of tasks, and the allocation can be fine-tuned via configuration parameters. The memory PostgreSQL uses is split between:

Shared memory, which is global and across all sessions
Per-backend memory, which is local to each session/process
OS cache, which is outside PostgreSQL but critical
Background processes, such as autovacuum workers, the checkpointer, the background writer, and parallel workers (which use their own local memory as well)

One cannot estimate how much RAM will be sufficient from expected data size alone; it will depend on workload access patterns. If adding more RAM later will be a complex process (e.g., if you have a bare-metal database server), consider provisioning as much RAM as you can afford; otherwise, have a good guess and provision extra RAM later if necessary. PostgreSQL can run with minimal amounts of RAM—in fact, the default setup allocates 128 MB for shared buffers and 4 MB for each worker, which are extremely small values. The server will work with those settings regardless of the size of the database or the complexity of the queries (i.e., the server won't fail), but performance will degrade drastically.

Storage is perhaps the easiest to estimate and anticipate (for size) if you know your data, but also potentially the most complicated to extend (depending on the OS and file system of choice) in the case of underprovisioning. Take into consideration other data that may be stored in the same server, such as database backups or ingestion files, that will be retained for some time.

PostgreSQL can allocate tablespaces on different drives, which can assist with distributing I/O through multiple drives and also allows for using a small, high-performance device, such as a solid-state drive (SSD), for current data, temporary tables, and often-used indices while employing slower and cheaper drives for historical or less frequently accessed data.

Consider also how transaction processing performance degrades progressively due to fragmentation when free space drops to below 25%.

Once a baseline is established (i.e., once access patterns, concurrency, and dataset sizes are identified from activity records), set alarms to fire promptly should the system begin performing outside expected parameters, adjusting provisioned hardware as needed.

Tune key PostgreSQL configuration parameters

PostgreSQL's default settings are highly conservative: they are safe, not fast, prioritizing correctness and stability over performance while avoiding out-of-memory crashes on small machines. PostgreSQL falls back to using disk storage for sorts, hashes, indices, etc., whenever there is insufficient memory for these tasks. Given PostgreSQL works effectively out of the box, production systems left with default settings are strikingly common. Tuning configuration settings generally improves performance characteristics for production workloads.

Here are some important settings to configure:

shared_buffers: This is the amount of memory the database server uses for shared memory buffers and is recommended to be set at 25-40% of available memory to optimize caching. If PostgreSQL shares the server with other services, such as Redis or an application server, care must be taken to help ensure PostgreSQL is not allocated too much memory, potentially starving the other services for memory.
max_connections: This is the maximum number of connections that PostgreSQL will accept. RAM gets preallocated based on this value (roughly 10 KB * max_connections), so keep an eye on this parameter to help ensure incoming connections will consistently succeed up to the number configured. For example, setting this value to 100,000 causes PostgreSQL to preallocate 1 GB for connections. Keep this number low and realistic so you don't waste memory needlessly, and use a connection pooler whenever expecting thousands of connections.
wal_buffers: This is the amount of shared memory used for write-ahead logging (WAL) data that has not yet been written to disk. Tune this value when expecting write-heavy online transaction processing workloads, as too frequent flushing to the WAL files can become a bottleneck and increase latency. The default value is -1, i.e., the value is auto-tuned according to shared_buffers, with a minimum of 64 KB and a maximum of 16 MB. When applicable, consider setting this to 32 MB or higher. Monitor this with SELECT checkpoints_req, buffers_checkpoint FROM pg_stat_bgwriter, with high buffers_checkpoint or frequent WAL writes indicating this setting should be increased.
work_mem: This is the base maximum amount of memory used by a query operation (such as a sort or hash table) before writing to temporary disk files. Increase this to suit the server's available RAM and the expected concurrent connections to avoid spilling sorts and hash joins to disk. This setting establishes the maximum amount of RAM that can be allocated for all operations, which are sequential for every connection. The calculation of the allowance should be performed considering the maximum number of connections allowed and the expected intensity of activity per connection. Setting this value too high may cause some concurrent queries to exhaust all available RAM and cause others to rely on disk-only sorting and hashing, while setting it too low may limit overall performance by underutilizing the server's available RAM.
effective_cache_size: This sets the planner/optimizer's assumption about the effective size of the disk cache that is available to a single query. PostgreSQL does not cache file contents, leaving this task to the operating system. However, setting effective_cache_size to how much RAM the OS is dedicating to file caching can assist the planner/optimizer in optimizing queries (this setting is a planner/optimizer hint). The default value is 4 GB, and the parameter should be set to the total RAM less the value of shared_buffers (subtract from that any memory used by other services in the same server).
max_parallel_workers, max_parallel_workers_per_gather, parallel_setup_cost, etc.: These relate to the number of workers that can be started by a single Gather or Gather Merge node. If your server has more than four CPU cores, enable and tune parallel queries with these metrics and others. Parallelism does not guarantee queries will perform better, but results may be obtained more quickly if a query can distribute its load and there are enough idle CPU cores. The setting max_parallel_workers limits the total number of cores that can be used for parallelism (default 8), and max_parallel_workers_per_gather limits how many cores can be used for parallelism per query. Lowering parallel_setup_cost (default 1,000) encourages parallelism for smaller datasets, especially on SSDs, where startup is faster. Setting it too low risks inefficient plans for small tables. There are several more settings related to parallelism; consult the documentation for further details. These settings can be updated per session (via SET command), which allows you to use EXPLAIN ANALYZE on a given query using varying configuration settings to compare performance.
effective_io_concurrency: This setting adjusts the parallelism of IO requests on the storage subsystem. Faster storage (e.g. SSDs) can support many more concurrent threads that slow storage (e.g. HDDs). On systems with SSDs, set this value to 100-200 (default 1), as SSDs can handle multiple concurrent requests. Also, reduce random_page_cost to 1.0-2.0 (default 4.0) as random scans are as fast as sequential scans in SSDs.

Use efficient and minimal data types

When designing a database schema for performance, it is advisable to be conscientious about data types in columns that are expected to have hundreds of thousands or millions of rows, since every byte wasted can result in megabytes of data needlessly being written, stored, and read or scanned in tables and indices. For example, using an int to store values 0 and 1 instead of using a boolean value would waste 3 bytes per value. So in a table with ten million rows, it would be wasting almost 100 MB, and would double again for each additional index on the column.

PostgreSQL has multiple data types in four broad categories:

Primitive fixed-length types (fixed 1, 2, 4, or 8 bytes per value): boolean, smallint, int, bigint, date, timestamp, float, double, and enum
Variable-length types: text/char/varchar, bytea, decimal/numeric, JSON/JSONB, arrays, ranges, and rangelists
Specialized fixed-length types: UUID, point
Composite types: row, which has at least 23 bytes of header overhead

PostgreSQL's rich list of data types allows database designers to fine-tune how the data is stored for maximal storage, processing, search, and retrieval efficiency. Choosing the right data type to store your data efficiently speeds up transaction processing, uses less memory and fewer I/Os, and keeps tables and indices smaller. Take special care when using object-relational mapping, as these tend to use generic data types, prioritizing portability instead of PostgreSQL-specific performance.

boolean

Use the boolean type for true/false values instead of 0/1 or T/F strings. Unlike most other database engines, in PostgreSQL, all comparisons return a boolean value, so boolean values do not need to be compared, i.e., write SELECT * FROM mytable WHERE myflag, instead of WHERE myflag = True.

"char" (with double quotes)

This type uses a single byte (an ASCII character) and is ideal for storing categories, states, flags, etc.

date and time

Choose date (4 bytes), time (8 bytes), timestamp/timestamp with time zone (8 bytes), or time with time zone (12 bytes), depending on what you need. date, time, and timestamp values are time zone agnostic, while timestamp with time zone is typically stored in UTC/GMT and will perform automatic transformations to/from the current session's defined time zone (i.e., these values do not store a specific time zone, unlike time with time zone). The time zone automatic conversion to/from UTC happens exclusively at encoding and decoding to/from string representation, not when comparing two values or passing them as parameters to a function, operation, or stored procedure.

enum

Each database has an internal registrar of enum types and the ordered list of values in each enum type, each assigned a unique per-database int4 identifier and stored in tables using this identifier instead of the string they represent. Using enum values can result in significant data savings when columns use string values longer than three characters. Similar to UUID, when the library/driver requests binary mode, the values are sent as integers, and the client converts them to strings using its own enum dictionary copy, which could mean significantly smaller network packets and faster transfers.

floating point

Choose real/float4 (4 bytes), float/float8 (8 bytes), money (8 bytes; always 2 decimals), or decimal/numeric (typically 8-12 bytes, but can be much more), depending on the precision required. Avoid using these types for columns storing integer-only values. Values of the money type may require conversion to numeric for certain operations (e.g., a money value cannot be divided by another money value).

integer

Choose smallint/int2 (2 bytes, values -32,768 to 32,767) for data elements such as temperatures, ages, or number of children. Choose integer/int/int4 (4 bytes, up to approximately 2 billion) for common identifiers. Choose bigint/int8 (8 bytes) for values that could be larger than 2 billion (e.g., a unique identifier for a log table).

JSON/JSONB/composite

JavaScript Object Notation (JSON) values are effectively strings that are encoded to JSON on the fly for JSON operations (like extracting a value) but are stored as plain strings. They perform well in cases when JSON values will be stored and retrieved as is, without any transformation, and will rarely be filtered or processed. JSON values are parsed when ingesting solely to verify they contain no errors, so this parsing is fast and cheap. On the other hand, JSONB values are stored as a tree of name-value pairs, so they are slower to ingest and encode back as a string but much faster to process (e.g., add or extract a value or perform a type test).

Composite values (i.e., row types) have a 23-byte header overhead per value but do not contain the names of the columns for every value the way JSON/JSONB do. This means storing an array of addresses as an array of an address composite type (defined via CREATE TYPE address AS (line1 text, line2 text, state text, postcode text…)) can be more efficient than using JSON/JSONB.

text

Unlike other database engines, text, char, varchar, and bytea are all stored using the same mechanism (C structures called varlena and short varlena), and there is no penalty for using the text data type for all normal strings, i.e., a text value can handle a single character and a 2 GB string equally efficiently. Also, all text data in PostgreSQL is treated by default as UTF-8 (for other encodings, use bytea data type, and encode/decode functions).

A varlena consists of a 4-byte header followed by however many bytes there are in the string or binary data (if any), and a short varlena consists of a single byte header followed by up to 126 bytes of data. Therefore, an empty string uses a single byte, and a 2-character string uses 3 bytes. The qualifier in char and varchar (e.g., the number 10 in char(10)) simply constrains longer values to be truncated to the specified number of characters (not bytes). The difference between char and varchar is char is whitespace-padded on output: whitespace padding is not stored but added for display or when passed as a parameter to a function, e.g., when values are concatenated.

While text and char/varchar without a qualifier are treated the same—they are synonymous—some drivers (such as older Windows ODBC ones) may identify these as binary large objects (known as BLOBs) / character large objects (known as CLOBs) and reduce efficiencies such as memory preallocation based on expected row numbers on the client side.

UUID

This type encodes universally unique identifiers (UUIDs) into 16-byte fixed-length strings, so it is smaller and much more efficient for scanning and indexing than a string (which would use 33-37 bytes). It will encode any valid UUID (with or without curly braces and/or dashes, ignoring the case) and will typically output as strings with dashes and in lowercase.

By default, PostgreSQL decodes 16-byte UUID values to strings, and these travel as strings to the client/driver/library. However, the driver/library (e.g., libpq, psycopg2/psycopg3, or JDBC) can ask to set up the session in binary format, receive the raw 16-byte UUID values, and perform the conversion to string locally. This approach significantly reduces the conversion overhead on the server side and the size of the network packets transferred.

NULL values

Use NULL values whenever suitable, instead of using empty strings for undefined values. NULL values consume space in the row header, 1 byte per each group of 8 columns (i.e., one bit per column, whether nullable or not) when at least one of the columns is nullable.

Design indices according to use

Indices have a reputation for speeding up transactions, but that is true for read operations, e.g., SELECT statements. For write operations, indices need to be updated for each row in any write operation (insert, update, or delete) in addition to the base table, which can be costly for large tables that have several indices. They are often added preemptively in anticipation of potential uses and end up never or rarely being selected by the database engine, adding overhead with no tangible benefit. Ideally, indices should be created to address specific queries that are required to perform optimally.

Indices are used for:

Locating records identified by a key
Filtering records by a given value, a list of values, or a range of values
Enforcing foreign keys
Assisting with grouping and sorting outputs

Create indices for:

Identifiers: These need to be indexed, ideally via a PRIMARY KEY or UNIQUE constraint.
Columns referencing foreign keys: Columns referencing other tables' identifiers should ideally also be indexed so that when deleting entries in the foreign key, the database can quickly find referencing rows to block or cascade the operation. Multi-column foreign keys may be fine with indexing the first column, provided that the number of rows identified by the first column is a few dozen or hundreds of rows.
Other use cases: Columns that are included in common queries and are the shortest and surest path to identify a specific row, or drastically reduce the number of matches for further filtering (e.g., last_name and first_name, or date and client_id).

Order of columns matters in an index

When creating multi-column indices, the order matters. Columns that will be filtered by equality should go first, and those filtered by range will ideally be last. Among the columns to filter by equality (or IN a set of values), columns should be ordered by decreasing cardinality (the number of unique values in a given column). For instance, the cardinality of a boolean column is 2, the cardinality for a status column with ORDERED/PAID/CANCELLED values is 3, and the cardinality of a unique int identifier is the number of rows in the table.

Say a table is defined with a column name and data type containing id serial, date date, client_id uuid, total_amount money. Chances are that for this table, you will be filtering by either a specific client_id and date or by a range of dates. Both columns, client_id and date, should be indexed, but given that date will likely be involved in ranges, it is best for it to be the final indexed column, e.g., two indices as in (date) and (client_id, date). Filtering by date alone (specific or range) will use the first index. Filtering by a specific client will use the second query, and if a specific date or range is also specified, it will use the same index to further drill down to produce one or a few rows. When including columns with extremely high cardinality in an index, they should be indexed individually or specified last in a multi-column index, since there will be nothing to drill down to. Examples of high-cardinality columns are the table's own identifier, a timestamp, and float/numeric values.

Analyze index usage

Finally, make sure there are no indices with the exact same columns or one column included in another. For example, if there is an index for (date) and another for (date, client_id), consider keeping the second one or the one that is selected from most often. Use EXPLAIN ANALYZE to determine which one is selected in the queries that your applications make.

The database engine processes queries in steps (subplans), which can be seen for any query via EXPLAIN ANALYZE. For each step that involves direct access to a table, the engine relies on built-in strategies and collected statistics to select which index is likely to be the most effective, whether it's for filtering, sorting, or both. When a table has several columns indexed (individually or multi-column), if a query has a WHERE clause filtering by some of these columns, the engine will choose one (and at most one) index to assist with the filtering, choosing the one likely to produce the smallest set.

If a table is often queried with a filter by an indexed column, that index may rarely be selected as the most efficient, so it may cost more to maintain than the benefit it provides in accelerating queries. Use the view pg_stat_user_indexes to determine which of your indices are being used, and consider deleting the ones that are not.

SELECT 
    schemaname, 
    relname AS table_name, 
    indexrelname AS index_name, 
    idx_scan,      -- number of index scans 
    idx_tup_read,  -- index entries read 
    idx_tup_fetch  -- table rows returned by those index scans 
FROM pg_stat_user_indexes 
ORDER BY idx_scan; -- list first the indices least often used

List your indices, including usage statistics, to help identify any superfluous indices.

Consider using partial indices for highly selective patterns. For example, if your invoices table has a status field with possible values PENDING, PAID, and CANCELLED, indexing by (client_id) WHERE (status = 'PENDING') will keep a high-performance, small, fast index dedicated to pending invoices.

Choose the right index.

EXPLAIN ANALYZE output comparison before and after adding a suitable index.

Monitor and tune autovacuum

To allow reading and writing at the same time without user transactions blocking each other, PostgreSQL uses a technique called multiversion concurrency control (MVCC). MVCC is a concurrency technique that reduces locking and blocking in multi-user applications, since it does not make changes directly to the records affected. Instead, it creates new versions for updated records and marks deleted rows as no longer visible. The obsolete rows (those marked as no longer visible) remain in the table until there are no longer any transactions referencing them; once this happens, those rows become dead tuples.

The VACUUM operation scans tables and indices, identifying dead tuples, deleting index entries that reference them, and freeing the space they use. PostgreSQL automatically performs VACUUM operations via the autovacuum process, which can be fine-tuned to the specifics of your database in terms of frequency (autovacuum_naptime), CPU and memory allocation (autovacuum_max_workers and autovacuum_work_mem), and minimum percentage of rows updated required to trigger a vacuum or analyze (autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor), among others. A VACUUM FULL operation blocks the table and completely rewrites it and all its indices.

Busy tables tend to develop bloat because default values are unsuitable for large tables. Autovacuum ignores tables where the number of dead tuples is less than the following formula: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * estimated_row_count. Considering that the default threshold is 50 and the default scale factor is 0.2 (i.e., 20%), this means that in a table with 20 million rows, the number of dead tuples must exceed 50 + 0.2 * 20,000,000, i.e., at least 4,000,050 dead tuples for autovacuum to kick in.

These tables can have autovacuum settings configured individually, for example:

ALTER TABLE my_large_table SET ( 
 autovacuum_vacuum_scale_factor = 0.01, 
 autovacuum_analyze_scale_factor = 0.02,
 autovacuum_vacuum_threshold = 1000, 
 autovacuum_analyze_threshold = 500);

Example of autovacuum settings for my_large_table.

Autovacuum also needs certain locks to operate, and if it cannot obtain them promptly, it will cancel itself to avoid blocking user queries. Also, if vacuum_cost_delay/limit is too strict, vacuum can make slow progress and never catch up with the rate of updates. Tables that are frequently updated via complex operations (e.g., cascading changes via triggers) may cause autovacuum to abort to prevent the risk of deadlocks. In these cases, you will see log messages like this one:

LOG: automatic vacuum of table "public.mytable": interrupted by deadlock.

Example of autovacuum failure due to deadlocks.

Since VACUUM does not shrink table or index files, it is important that it run to completion often enough to free the space used by dead tuples and avoid table and index bloat. Take these steps:

Monitor pg_stat_user_tables to find vacuum lag and identify bloated tables.
Consider manually running VACUUM or VACUUM FULL for critical tables.
Run VACUUM FULL after dropping columns with bulky data, since dropping columns hides them from future queries but does not delete that data. Be aware this operation completely rewrites a table and its indices, and therefore, it requires an exclusive block.

Avoid disabling autovacuum unless you have a scheduled replacement process.

https://p1.aprimocdn.net/solarwinds/eabf6867-4dad-4e2e-a899-b47a00fd3935/postgres_performance_tuning_Img1_Original%20file.png

Autovacuum settings.

Use a connection pooler

PostgreSQL doesn't scale well for large numbers of clients, as it forks a process for each connection that uses memory when idle. Application servers often use their own connection poolers, but there are specialized applications like pgBouncer that sit between clients and PostgreSQL, reusing a small pool of PostgreSQL connections. Pooling also reduces memory and context-switch overhead under high concurrency.

pgBouncer operates in transaction pooling mode by default, which supports atomic operations and resets all session variables and drops all temporary tables after every operation. However, it can also listen in session mode, using either a database name alias or a separate port, allowing clients to perform complex processes as if they were connected directly to the PostgreSQL server.

pgBouncer has the added benefit of handling client pressure by allowing incoming connections despite the server having reached the maximum number of allowed connections, resulting in a delayed response instead of a connection failure. It also allows for the database server to be restarted without disconnecting the clients (graceful restarts for maintenance, upgrades, or failover). Connecting to pgBouncer has a quicker handshake and uses fewer resources than a PostgreSQL connection.

When using pgBouncer, set a sensible max_connections setting on PostgreSQL, and allow a higher number of connections in pgBouncer.

Connection pooler architecture.

Track function performance (triggers, indices, and RLS policies)

PostgreSQL allows creating user-defined functions via the CREATE FUNCTION statement, and their use can significantly simplify the logic of your queries. Functions can also be configured for parallelism and can be used in indices, allowing for an increase in performance when defined correctly. However, using functions incorrectly can be a source of significant performance drain. For PostgreSQL, a function is a function, whether it is part of the ANSI/ISO SQL standard, is PostgreSQL-specific, is included in an extension, or is user-defined in one of the supported languages.

When used correctly in indices, functions can greatly increase performance. For example, creating an index by lower(email) stores the lowercase value of email in the index (instead of the original email value), and therefore can accelerate a query like SELECT * FROM clients WHERE lower(email) = 'abc@mail.com' as the index exactly matches the filter condition and the values stored in the index are all lowercase already. Without that index, the same query would require scanning every row in clients and obtaining the result of each lower(email) for the comparison. In short, functional indices execute the function on insert and update operations, storing the function's result; therefore, IMMUTABLE functions must be used in functional indices, i.e., functions that are guaranteed to return the same result for any given arguments. This example uses the standard lower() SQL function, but the example is applicable to any other function, including extensions and user-defined functions.

Functions are created with parallelism disabled by default (including IMMUTABLE and STABLE ones). When a function is enabled for parallelism (via the PARALLEL SAFE attribute), PostgreSQL can distribute execution through multiple cores, which may produce results faster, especially when the function involves a database lookup.

In setups with multiple cores, make sure to configure suitable values as shown in the table below.

Setting	Default Value	Adjustment Considerations
max_parallel_workers	8	Increase if your server has many cores
max_parallel_workers_per_gather	2	Increase to have each query use more than two cores
parallel_setup_cost	1,000	Lower to encourage parallelism
min_parallel_table_scan_size	8 MB	Lower to enable parallelism on smaller data sets

Sometimes a trigger does not need to run. For example, a trigger may be created on UPDATE for the table invoices to verify if a status change is valid according to company business rules. Make sure to take advantage of trigger attributes such as FOR UPDATE OF col_name [, ... ], and/or WHEN (condition) so the trigger is bypassed entirely if conditions to justify executing it are not met.

Row-level security (RLS) policies can be a major source of I/O operations and CPU usage if not carefully tuned. Reliance on RLS policies is common on REST-enabled databases using JSON Web Tokens (JWT) authorization, where all users connect to the database with the same user (e.g., authorized) and can be identified by running queries by executing functions such as auth.uid(). An example of RLS on table messages in this scenario can be defined as CREATE POLICY messages_user_access ON messages FOR SELECT USING (user_id = (SELECT auth.uid())), which allows users to see their own messages and no one else's. The comparison to user_id is the subquery (SELECT auth.uid()). This is important because it tells the planner/optimizer to run this query once as an independent initial plan and then use its result to filter by the indexed field user_id; setting the policy as USING (user_id = auth.uid()) creates a correlation between each row's user_id and the function auth.uid(). Doing this causes the planner/optimizer to execute it for each row in the table and forces a scan, although user_id is indexed. If unsure, use EXPLAIN ANALYZE to help ensure the planner/optimizer is using the expected strategy.

When the planner/optimizer is evaluating filter and join conditions, if functions are involved, it uses the functions' cost to determine the order in which conditions will be evaluated. Functions have different default costs depending on the function's language; for example, SQL functions have a default cost of 1, while PL/pgSQL functions have a default cost of 100. Tuning the cost of user-defined functions can assist the planner/optimizer with making better choices about which functions to filter by whenever there is more than one.

Use the view pg_stat_user_functions to track which functions are being executed excessively or using too much CPU. Functions are tracked when the setting track_functions is on (turn it on via SET track_functions = 'all'). To reset these statistics, use SELECT pg_stat_reset(). For example:

SELECT schemaname, funcname, calls, 
       round(total_time::numeric,2) AS total_ms, 
       round(self_time::numeric,2)  AS self_ms, 
       round(total_time/calls,2)    AS avg_ms_per_call
FROM pg_stat_user_functions 
ORDER BY total_time DESC;

Example SQL to query usage of functions.

Function volatility, cost, and parallelism.

Continuously monitor system and query performance

Identifying slow queries is an indispensable tool to assist with database performance. The simplest and most common method is to configure the setting log_min_duration_statement with a milliseconds value (defaults to -1, which disables it). If this value is set to 1,000, then all queries taking one second or longer will be logged in PostgreSQL's main log file.

Parsing the standard log file can be challenging, especially if queries are incredibly large (tens or hundreds of lines or long lists of values). An alternative to this is setting log_destination to csvlog, which produces a CSV file, rather than a flat file, for easier parsing or importing into the database regularly via a scheduled job.

A better alternative to the log_min_duration_statement setting is to use the extension pg_stat_statements, which provides a view with the same name. This extension provides all of the information about logging, with lots of valuable information about the SQL statements running on the PostgreSQL instance.

The extension needs to be installed with CREATE EXTENSION pg_stat_statements, and configured with shared_preload_libraries = 'pg_stat_statements' in the configuration file (postgresql.conf) before it can be used. Once it is operational, it provides invaluable insights such as identifying slow or frequently executed queries, obtaining the total execution time and average time per call for each normalized query, or tracking how often queries hit the cache vs. disk. It also tracks SQL code with constants replaced by placeholders, aggregating all logically identical queries. Counters can also be reset by executing the function pg_stat_statements_reset(). For example, this code lists the 10 slowest query patterns:

SELECT query, calls, total_exec_time, mean_exec_time, rows 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

Example of SQL to discover the 10 slowest query on the PostgreSQL instance.

Once slow queries are identified, use EXPLAIN ANALYZE to try to understand why the planner/optimizer is choosing a strategy that produces poor results. This can easily help with spotting when sequential scans are used instead of indices, or filters use the wrong order of priorities.

Along with monitoring pg_stat_activity and pg_locks as mentioned earlier for creating a performance baseline, also track and regularly monitor results from the views pg_statio_all_tables, pg_stat_database, and pg_stat_io, which provide insights into memory use, cache hit ratio, I/O latency, and CPU wait time. An alternative to collecting statistics in files and monitoring them manually is to use specialized monitoring tools such as SolarWinds^® Database Performance Analyzer (DPA), which can collect, visualize, and analyze metrics over time.

Collecting telemetry and visualizing anomalies can assist with identifying problems and accelerating root-cause analysis (RCA), but problems are unlikely to happen while someone is watching. It is important to set alerts for resource consumption spikes, autovacuum lag, replication delay, and long-running queries so extraordinary events such as a DDoS attack, a drive running out of space, or a rogue process eating all the CPU can be addressed before it is too late.

Monitoring PostgreSQL alerts with DPA.

Conclusion

PostgreSQL performance depends as much on environment, configuration, and workload as it does on schema or query logic. A default install is most often unsuitable for production workloads. Significant performance gains are achieved by addressing a few key areas: hardware provisioning, memory settings, database design, including data type usage and indexing, autovacuum, and query monitoring.

Database design choices, including the data types and indexing, segregating data volumes, and partitioning approaches, can have a long-term impact, especially as data volumes grow. Choosing your data types and indexing method carefully can help you keep a fast and lean database.

Continuous observability and tuning are essential, as conditions tend to change; sometimes, the database size is the sole aspect changing over time. Tools such as SolarWinds Database Performance Analyzer enable continuous performance monitoring, helping you to identify issues quickly and optimize the system for performance.

Stop guessing. Start optimizing.

Try DPA for Free