Enter the terms you wish to search for.

How to use postgresql.conf to configure and tune the PostgreSQL server

Tuning your PostgreSQL server plays an important role in making sure you get the most out of your server resources, and running with default parameters is not always enough. Using the PostgreSQL server configuration file postgresql.conf, we can tune the right area and make the most out of the server resources. The postgresql.conf file tuning parameters are classified into different categories including database connections, memory, optimizers, and logging.

What you will learn in this webinar:

  • A basic understanding of postgresql.conf
  • The categories and parameters of postgresql.conf
  • How to adjust parameters
  • Expert tuning recommendations

 

Webinar video

Webinar slides

 

Webinar Q&A:

  • For auth, will EnterpriseDB allow us to use ssh keys or certs to ensure that the identity connecting is authenticated correctly?
    • Yes, you use the ‘cert’ authentication method.

  • In the Resource Usage section, which of these parameters do you usually tune in a medium size database workload?

    • shared_buffers and work_mem.

  • How do you rotate the log files and delete older than x days?

    • There are configuration settings to rotate on data/time or log file size.

  • What happens if I have a typo in postgresql.conf file and the DB fails to start up - will it say which parm is typed erroneously?

    • Yes, in the log file.

  • If we use pooler, do we need to use local database user accounts instead of doing AD authentication?

    • It is hard to pass user authentication through a pooler, except for password-based authentication methods.

  • Can we pin objects in memory for faster access?  Setting the increased memory requirement in postgresql.conf and then issuing the pin to memory with a database boot trigger?

    • No, you cannot pin items, but there is code to prevent cache wiping from removing frequently-access data.

  • Could you recommend some open source bench marking tool for postgres?

    • Postgres comes with pgbench, which is a TCP-B workload.

  • Is there a way to see the actual explain plans for all sub SQL queries in a function?

    • Good question --- I don’t know of a way to do that.  You can run EXPLAIN in a function though.

  • Is there a utility/function to recommend indexes for a SQL query like DB2 has db2advis?

    • EDB’s Postgres Enterprise Manager has that, and there is this:  https://pgxn.org/dist/pg_idx_advisor/

  • For Vacuum ops, can an EnterpiseDB cluster remain in op while doing a backgrund Vacuum without affecting performance? 

    • Yes, everything is read/write during vacuum.

  • Can huge pages be enabled to eliminate swapping altogether?

    • Huge pages help reduce the overhead of virtual memory mapping.  It doesn’t affect swapping.

  • How would Azure/AWS configure these when you are using DAAS (PostgreSQL option). Do they have default values set or are they configurable?

    • Yes, for most cloud-provider-managed services, some settings are fixed.

  • How can one log queries in a log file where the queries take more than some time range period?

    • log_min_duration_statement

  • Is there a plan for native master replication?

    • No because there is much external tooling required for multi-master.

  • Some queries perform better, others worse, when changing the seq_page_cost + random_page_cost, suggestions on finding a balance that helps without hindering.

    • No, just testing.

  • Is Postgres more secured with data than Oracle?

    • Oracle has a few more security features than Postgres, like TDE and native data masking/redaction.  For Postgres, EDB’s EPAS has masking/redaction, and TDE is accomplished usually at the operating system level, or using external tools.

Other Resources