shared_buffers sets the size of the shared memory buffers used by the PostgreSQL server.
max_connections sets the maximum number of concurrent client connections allowed to the PostgreSQL server.
work_mem sets the maximum amount of memory used for maintenance tasks like VACUUM and CREATE INDEX.
Setting ssl to on enables SSL connections.
wal_level is a parameter that controls the level of information written to the WAL.
If a lock cannot be acquired within the time specified by deadlock_timeout, a deadlock is assumed to have occurred.
Adjusting the value of deadlock_timeout can help mitigate the occurrence of deadlocks.
Reducing the value of deadlock_timeout decrease the number of processes waiting for a lock, potentially leading to a reduction in CPU load.
Deadlocks should be avoided by devising ways to create applications, and it is desirable for the value of deadlock_timeout to be as large as possible.
With the default setting of deadlock_timeout, deadlock detection is not performed automatically.
Setting shared_buffers to an excessively large value caused performance degradation of queries during checkpointing.
Setting maintenance_work_mem higher than work_mem caused performance degradation of VACUUM processing.
Concurrent sessions issuing a high volume of INSERT statements caused write contention on the WAL files, resulting in degraded INSERT performance.
The absence of periodic tuple-level statistics collection using pgstattuple prevented PostgreSQL from generating optimal execution plans, leading to degraded query performance.
Response performance during data update may improve.
It may reduce the amount of WAL written during data updates.
Response performance does not change if the wal_level parameter is set to "minimal".
Unrecoverable data corruption may occur in the event of system crash.
Point-in-time recovery operations will become impossible.
Review slow-running SQL queries and optimize the code that is causing the performance issues.
Adjust the checkpoint_completion_target parameter to smooth out the load caused by checkpoints.
Decrease the autovacuum_vacuum_cost_limit or vacuum_cost_limit value to reduce the load caused by VACUUM processing.
Change to a streaming replication configuration to achieve load balancing.
This is a characteristic of PostgreSQL, and there are no countermeasure.
Reindexing must be done by stopping the service.
Since REINDEX does not lock reads on the underlying table, it has no impact on read operations when executed while the service is running.
CREATE INDEX CONCURRENTLY allows you to create an index without acquiring locks that conflict with concurrent inserts, updates, and deletes.
CREATE INDEX CONCURRENTLY can also be used to create primary keys.
Regularly re-creating indexes can help prevent index bloat.
The "Total runtime" value may increase.
The "Total runtime" value may decrease.
The total estimated cost of the top node may increase.
The total estimated cost of the top node may decrease.
The exact same execution plan may be selected.
If FILLFACTOR is omitted, the target table's FILLFACTOR is used as the default value.
If the UNLOGGED parameter is specified, WAL logging is skipped during index updates, which speeds up the update process.
If the PARALLEL parameter is specified, multiple processes may be used to create the index, reducing creation time.
If the CONCURRENTLY parameter is specified, the index is created without locking writes to the target table, but the creation process takes longer than a standard index build.
Functions and operators used in index definitions must be immutable.
ORDER BY
CREATE INDEX
Merge Join
VACUUM
Automatic VACUUM
Disabling enable_indexscan prevents the query planner from using index scans.
Disabling enable_seqscan prevents the query planner from using sequential scans.
Setting random_page_cost lower than seq_page_cost causes the query planner to favor index scans.
Setting random_page_cost higher than seq_page_cost causes the query planner to favor index scans.
Setting default_statistics_target to a smaller value can lead to the collection of more detailed statistics, potentially improving the quality of the planner's estimates.
It is displayed in the pg_stat_bgwriter view.
The value increases during write operations caused by checkpoints.
If the value of buffers_backend is large compared to buffers_alloc, you should consider tuning the value of shared_buffers.
The value increases during write operations by the background writer.
The value increases during write operations by backend processes.
When set to "on", the commit is considered successful as soon as the WAL record is written to the standby's disk.
When set to "off", the commit is considered successful even before the WAL has been written to either the primary or the standby server.
When set to "local", the commit is considered successful when the WAL is written to the buffer, before it is written to the standby's disk.
When set to "remote_apply", the commit is considered successful not only when the WAL is written to disk on the standby server, but also when the changes described in the WAL have been applied to the database.
© EDUCO (General Incorporated Association Educo) all rights reserved.