PostgreSQL provides several configuration parameters that directly influence transaction behavior, consistency, safety, and performance. These settings are useful for database administrators, developers, and anyone who wants to tune PostgreSQL for production workloads, reporting systems, or high-concurrency applications.
In this article, we will explore five important transaction-related parameters:
- Transaction_buffers
- Transaction_deferrable
- Transaction_isolation
- Transaction_read_only
- transaction_timeout
A transaction is a group of SQL operations executed as a single unit. PostgreSQL guarantees transaction reliability through ACID properties:
- Atomicity
- Consistency
- Isolation
- Durability
The parameters discussed here help control how transactions are processed, how safely they read data, how long they can run, and how internal transaction status information is managed.
1. transaction_buffers
This parameter controls the size of the dedicated buffer pool used for the transaction status cache.
It belongs to the internal SLRU (Simple LRU) subsystem that stores transaction status information such as committed, aborted, and in-progress transactions.
Current Value Example
SHOW transaction_buffers;
Output:
transaction_buffers
---------------------
256kB
pg_settings Information
SELECT * FROM pg_settings WHERE name = 'transaction_buffers';
Result :
name | transaction_buffers
setting | 32
unit | 8kB
category | Resource Usage / Memory
short_desc | Sets the size of the dedicated buffer pool used for the transaction status cache.
extra_desc | 0 means use a fraction of "shared_buffers".
context | postmaster
vartype | integer
source | default
min_val | 0
max_val | 131072
enumvals |
boot_val | 0
reset_val | 32
sourcefile |
sourceline |
pending_restart | f
Source Code Definition Example
We can see this parameter definition in postgres source code in the file named guc_parameters.dat
Note: you can only see this file if you are using PostgreSQL version 19 (devel) branch
{ name => 'transaction_buffers',
type => 'int',
context => 'PGC_POSTMASTER',
group => 'RESOURCES_MEM',
short_desc => 'Sets the size of the dedicated buffer pool used for the transaction status cache.',
long_desc => '0 means use a fraction of "shared_buffers".',
flags => 'GUC_UNIT_BLOCKS',
variable => 'transaction_buffers',
boot_val => '0',
min => '0',
max => 'SLRU_MAX_ALLOWED_BUFFERS',
check_hook => 'check_transaction_buffers',
},Internal limit:
There is a limit for setting the maximum value of transaction buffers
#define SLRU_MAX_ALLOWED_BUFFERS ((1024 * 1024 * 1024) / BLCKSZ)
Here, the size of the block is 8192 bytes
#define BLCKSZ 8192
How to Change It
Because the context is postmaster, a restart is required.
ALTER SYSTEM SET transaction_buffers = '16MB';
Reload alone is not enough:
SELECT pg_reload_conf();
Restart PostgreSQL, then verify:
SHOW transaction_buffers;
Practical Use Case
This setting can help systems with heavy transaction activity where many sessions constantly update data.
Examples:
- Large ERP systems
- High-write OLTP systems
- Multi-user billing systems
- Frequent commit workloads
Important Note
Most users can keep the default value. Change it only after testing and measuring performance.
2. transaction_deferrable
This setting determines whether a read-only serializable transaction should wait for a safe snapshot before execution, reducing the chance of serialization failures.
Current Value Example
SHOW transaction_deferrable;
Output:
transaction_deferrable
------------------------
off
pg_settings Information
SELECT * FROM pg_settings WHERE name = 'transaction_deferrable';
Result :
name | transaction_deferrable
setting | off
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
extra_desc |
context | user
vartype | bool
source | override
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Enable It for Session
SET transaction_deferrable = on;
Full Query Example
SET transaction_isolation = 'serializable';
SET transaction_read_only = on;
SET transaction_deferrable = on;
BEGIN;
SELECT * FROM orders;
COMMIT;
Explanation:
1. Serializable
SET transaction_isolation = 'serializable';
Use the strongest isolation level.
Your query should behave as if transactions run one by one.
Useful when many users are changing data.
2. Read Only
SET transaction_read_only = on;
This transaction will only read data.
No:
Only SELECT
3. Deferrable
SET transaction_deferrable = on;
Now PostgreSQL gets special permission:
- if system is busy
- if concurrent writes may cause future conflict
Then:
- wait now
- choose safe snapshot
- run query safely
Real Use Case
This is useful for long-running reports that need consistent data without retry errors.
Examples:
- Financial reports
- End-of-day reports
- Inventory snapshots
- Business intelligence queries
Important Note
It only matters when both conditions are true:
- Isolation level is serializable
- Transaction is read only
3. transaction_isolation
This parameter defines the isolation level of the current transaction.
Supported values:
- read committed
- repeatable read
- serializable
- read uncommitted (treated as read committed in PostgreSQL)
Current Value Example
SHOW transaction_isolation;
Output:
transaction_isolation
-----------------------
read committed
pg_settings Information
SELECT * FROM pg_settings WHERE name = 'transaction_isolation';
Result :
name | transaction_isolation
setting | read committed
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Sets the current transaction's isolation level.
extra_desc |
context | user
vartype | enum
source | override
min_val |
max_val |
enumvals | {serializable,"repeatable read","read committed","read uncommitted"}
boot_val | read committed
reset_val | read committed
sourcefile |
sourceline |
pending_restart | f
Change Isolation Level
SET transaction_isolation = 'repeatable read';
read committed
This is the default isolation level in PostgreSQL. Each statement sees only data committed before that statement begins.
A later query in the same transaction may see newer committed changes made by other sessions.
SET transaction_isolation = 'read committed';
Query Example
Session 1:
BEGIN;
SELECT quantity FROM stock WHERE id = 1;
Session 2:
UPDATE stock
SET quantity = 50
WHERE id = 1;
COMMIT;
Session 1:
SELECT quantity FROM stock WHERE id = 1;
COMMIT;
The second SELECT may show the updated value because each statement gets a fresh snapshot.
repeatable read
All queries inside the transaction use the same snapshot taken at the start of the transaction.
Repeated reads return consistent results, even if another session commits changes later.
SET transaction_isolation = 'repeatable read';
Query Example
Session 1:
SET transaction_isolation = 'repeatable read';
Session 2:
UPDATE products
SET price = 999
WHERE id = 10;
COMMIT;
Session 1:
SELECT price FROM products WHERE id = 10;
COMMIT;
The second SELECT returns the same value as the first one because the snapshot does not change.
serializable
This is the strictest isolation level. PostgreSQL ensures the result is equivalent to running transactions one after another.
It prevents anomalies caused by concurrent access, but some transactions may fail and need to be retried.
SET transaction_isolation = 'serializable';
Query Example
Session 1:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;
Session 2 at the same time:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;
COMMIT;
One transaction may fail with a serialization error and should be retried.
read uncommitted
The SQL standard defines this level to allow dirty reads. PostgreSQL does not allow dirty reads, so this level behaves the same as read committed.
SET transaction_isolation = 'read uncommitted';
Query Example
BEGIN;
SELECT * FROM employees;
COMMIT;
Behavior is the same as read committed.
4. transaction_read_only
This parameter controls whether the current transaction can modify data.
When enabled, write operations fail.
Current Value Example
SHOW transaction_read_only;
Output:
transaction_read_only
-----------------------
off
pg_settings Information
SELECT * FROM pg_settings WHERE name = 'transaction_read_only';
Important fields:
name | transaction_read_only
setting | off
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Sets the current transaction's read-only status.
extra_desc |
context | user
vartype | bool
source | override
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Enable Read-Only Mode
SET transaction_read_only = on;
Query Example
If you are trying the normal alter command , it will not work
postgres=# alter system set transaction_read_only = 'on';
ERROR: parameter "transaction_read_only" cannot be changed
Try to directly modify the postgres configuration file
show config_file;
Output
config_file
-----------------------------------------
/etc/postgresql/18/main/postgresql.conf
(1 row)
Then change the value of this parameter like this:
default_transaction_read_only = on
Now, try to execute an insert or update command like this
BEGIN;
SELECT * FROM customers;
INSERT INTO customers(name)
VALUES ('David');
Expected result:
ERROR: cannot execute INSERT in a read-only transaction
Real Use Cases
- Reporting users
- Audit access
- Read replica sessions
- Prevent accidental data changes
Important Note
This protects data by blocking:
- INSERT
- UPDATE
- DELETE
- DDL operations
5. transaction_timeout
This setting defines the maximum allowed duration of any transaction in a session.
If a transaction exceeds the limit, PostgreSQL terminates it.
Current Value Example
SHOW transaction_timeout;
Output:
transaction_timeout
---------------------
0
Value 0 means disabled.
pg_settings Information
SELECT * FROM pg_settings WHERE name = 'transaction_timeout';
Result :
name | transaction_timeout
setting | 0
unit | ms
category | Client Connection Defaults / Statement Behavior
short_desc | Sets the maximum allowed duration of any transaction within a session (not a prepared transaction).
extra_desc | 0 disables the timeout.
context | user
vartype | integer
source | default
min_val | 0
max_val | 2147483647
enumvals |
boot_val | 0
reset_val | 0
sourcefile |
sourceline |
pending_restart | f
Set Timeout
SET transaction_timeout = '5s';
Query Example
SET transaction_timeout = '5s';
BEGIN;
SELECT pg_sleep(10);
Expected result after 5 seconds:
FATAL: terminating connection due to transaction timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Real Use Cases
- Prevent stuck transactions
- Avoid long locks
- Protect application performance
- Stop poorly written queries
Transaction-related configuration parameters in PostgreSQL provide fine-grained control over reliability, consistency, and performance. Some settings, such as transaction_isolation, directly affect how users experience concurrent workloads. Others, like transaction_timeout, protect the system from long-running sessions. Internal parameters such as transaction_buffers help optimize backend transaction state management. By understanding when and where to use these settings, you can build a PostgreSQL environment that is stable, efficient, and better suited to real-world workloads.