In Oracle world there are two different files associated with param changes – SPFILE and PFILE . PFILE is a text file and can be edited with an editor like vi and SPFILE is a binary file . SPFILE allows dynamic changes in Oracle

In postgres we have two files associated with parameter changes – postgresql.conf and postgresql.auto.conf . We generally edit postgresql.conf to make permanent changes and use ALTER SYSTEM for dynamic changes followed by a pg_reload_conf() . Any changes made by ALTER SYSTEM are written to postgresql.auto.conf

In Oracle you can check the location of param file using a command like below(if you are using spfile)

show parameter spfile ;

In Postgres you can check the location of param file using a command like below

SHOW config_file;

Below is the  comparison diagram (for quick reference)

In Oracle we generally use v$parameter to check the value of a parameter and  its properties . Pg_settings is the equivalent in Postgres

In Oracle you can check the value of a parameter using a command like below

SELECT name,value FROM v$parameter WHERE name like ‘%sga%’;

In Postgres you can check the value of a parameter using a command like below

SELECT name,setting,short_desc from pg_settings where name like ‘%shared%’ ;

Below is the  comparison diagram (for quick reference)

We can also use show commands to check the value for a particular parameter

In Oracle you can use a command like below to check param value(Below command would check for parameters that contain ‘sga’ in their name)

SHOW PARAMETER <parameter name>;

In Postgres you can use a command like below to check a parameter

SHOW <parameter name> ;

Below is the  comparison diagram (for quick reference)

In Oracle you can use a command like below to check whether a parameter is dynamic or static . If ISYSYS_MODIFIABLE is set to IMMEDIATE it indicates that this parameter can be changed dynamically

 SELECT name, type, value, issys_modifiable FROM v$parameter WHERE name = ‘<parameter name>’;

In Postgres you can check the same using a command like below

SELECT name,setting FROM pg_settings WHERE context = ‘postmaster’ AND name = ‘<setting name>’;

When the context is postmaster for a particular parameter it needs a restart . Other context values like sighup,superuser,etc.. can be changed dynamically . Please check https://www.postgresql.org/docs/14/view-pg-settings.html for different types of context 

Below is the  comparison diagram (for quick reference)

In Oracle you need to run a command like below followed by a restart –

ALTER SYSTEM SET <parameter name>=<new value> SCOPE=SPFILE;

Restart Oracle

In Postgres you can run a command like below followed by a restart . You can also directly edit the conf file and restart postgres

ALTER SYSTEM SET <parameter name>=’<value>’;

Restart Postgres

Below is the  comparison diagram (for quick reference)

In Oracle you need to run a command to change dynamic parameter (note – you can also use scope=memory for dynamic changes depending on your use case)

ALTER SYSTEM SET <parameter name>=<new value> SCOPE=BOTH;

In Postgres you can run a command like below to change dynamic parameter. NOTE – After you run ALTER SYSTEM , a reload of config is needed to change

ALTER SYSTEM SET <parameter name>=’<value>’;

Select pg_reload_conf() ;

Below is the  comparison diagram (for quick reference)

In Oracle you can use ALTER SESSION command to modify a parameter at session level . Not all params are modifiable at session level . You can use a command like below to know if some param is modifiable at session level

SELECT NAME, VALUE FROM V$PARAMETER WHERE ISSES_MODIFIABLE=’TRUE’;

In Postgres you can use below command to check if a particular parameter can be modified at session level

SELECT * FROM pg_settings where context = ‘user’ and name=<param name>

You can make a session level change in oracle using below command

alter session set <paramname>=’value’;

In Postgres you can achieve the same using a command like below

set session <paramanme> to <value> ;

In Postgres you can make parameter changes at different levels – Cluster level , DB level , User level , Session etc.. (Example below)

Cluster level :

ALTER SYSTEM SET work_mem TO ‘128MB’;

Database level :

ALTER DATABASE database_name SET work_mem TO ‘128MB’;

User level :

ALTER ROLE user_name SET work_mem TO ’64MB’;

Transaction level:

 BEGIN;

SET LOCAL work_mem TO ’64MB’;

In Postgres you need to change default values for multiple parameters – depending on your workload and use case . Below are some params that are changed during build time (Only a sample list – not complete list)

Shared_buffers

Max_connections

Log_line_prefix

Work_mem

More..

You can use a third party tool like PGTUNE to arrive at your values https://pgtune.leopard.in.ua/

In PG world people generally use one or more extensions . You need to modify the parameter shared_preload_libraries during your build time to avoid unnecessary restart issues in the future

For example if you are using timescaledb and pg_stat_statements, you can change shared_preload_libraries as shown below

shared_preload_libraries=’pg_stat_statements,timescaledb’

In previous sections we saw how pg_settings view can be used to check the value of a parameter , check whether a parameter is static/dynamic ,.. What is the significance of pg_file_settings then ?

Listed below are the columns in pg_file_settings. This  can prove to be quite useful when

 1) you have multiple config files(using include) , you can quickly check the contents of all files at one place 2) Detect syntax errors in config file (invalid parameter , incorrect setting etc..)

In addition to the postgres config changes , you may want to optimize kernel settings to suit your workloads(below are sample list – not a complete list)

swappiness

Overcommit_memory

Hugepages settings

Dirty_ratio

Shared memory segment related settings

File system mount options

etc..

• Pg_settings is equivalent to v$parameter in Oracle
• Set session and alter session are equivalents (to perform session level changes)
• There is a concept of two files PFILE and SPFILE . In PG we dont have exactly similar concept of binary type vs plain text type – Postgres conf file is in readable format and can be edited
• There are third party utilities like PGTUNE and Cybertec configurator which can be used to tune your config
• Oracle has hidden parameters and parameter history tracking using AWR snapshots – Equivalent is not available in PG

In part 6 of the series “Postgres for Oracle DBAs” we compared “parameter changes”. Also check our performance articles –Pg_fincore and pg_buffercache to troubleshoot performance issues , Pgbouncer multiple instances(How we increased our TPS using multiple instances) , Postgres and temporary files , Pgpool and performance tuning, How we improved Lambda performance by 130x etc..

Want to learn Postgres ? Register for webinar (link on homepage)

Looking for Postgres support ? Please reach us at support@klouddb.io