How to optimize PostgreSQL for a 16GB RAM Quad-Core machine?

Posted on 2007-07-23
Last Modified: 2010-10-05
I would like to optimize postgres for the best possible performance for a single query. I am not going to use it in a multi-user environment, but performance of a single query is most important.
Could someone suggest correct config settings for PostgreSQL 8.2 for the following hardware:
Dell PowerEdge 2900, 1 Xeon 2.6GHz Quad-Core processor, 16GB memory, 4 RAID0 15000RPM SAS hard-disks, Windows 2003 R2 64Bit Server Edition (default configuration).
When I just use the default config file, I have a feeling postgres is not even using a 0.1% of the given resources, especially in terms of memory.
Question by:mersis
    LVL 10

    Accepted Solution


    First of all, you have Windows 2003 R2 64Bit Server Edition (default configuration), which means
    that you run PostgreSQL via Cygwin. I belive some of the performance will be lost in the Cygwin layer.

    That beeing said, I don't think that anyone will just give you such configuration for your server, because is quite new and because there aren't so many people running PostgreSQL in top of Cygwin in an Windows environment.

    To find the best you have to try several posibilities and see which one is the best.
    I would start with running EXPLAIN on your query and see how the query is planned, maybe you
    get some hints from there on what is going on in the server and what you can improve.

    Some pointers to what I would try first:
    1. Operating System Environment
        You can play with some of these setting if they are available in Cygwin.
    2. Server Configuration
        Start with: Resource Consumption
        - increase shared_buffers, temp_buffers
        - i would definitely increase work_mem and maintenance_work_mem
          if you say that you only have one query
    3. Play with WAL settings:

    You can use the runtime statistics to see which configuration is better:

    Also, some tips on performance are here:


    Author Comment

    Thanks adrpo. No, I am not using cygwin to run PostgreSQL. Why would I need it?
    I have just downloaded the windows installer for postgreSQL and used it. It worked fine and the database works well too.
    LVL 10

    Expert Comment


    My bad. I sow now that they made a MinGW-based version.
    Sorry about that. However, you can still play with points 2 and 3 from my last post.


    Author Comment

    Ok thanks adrpo, will have a look at those links and see if it makes things any better.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    postgreSQL COPY command and rights 8 995
    sql table merge 11 148
    Need help with a few queries in Access 16 295
    Postgres random sample 2 85
    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now