Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    PostgreSQL Performance Resources


    I have noticed that a large percentage of the questions in the PostgreSQL forum deal with unsatisfactory performance of some specific query/function in PostgreSQL.

    PostgreSQL's default install configuration is quite conservative, and will even run well on an old Pentium II with 32 MB RAM, but most of us have more horsepower to play with, so let's see how to unleash a little (or a lot) more speed out of our databases :

    Performance Tuning Links:

    Tuning PostgreSQL for Performance -- varlena.com
    Annotated PostgreSQL configuration guide -- varlena.com
    pgsql-performance mailing list archive

    Also, before posting requests for help, consider the following:

    1. When in doubt, run ANALYZE or VACUUM ANALYZE (best done during low-traffic times) either on your table or on the whole database.

    2. Have you indexed the table/column/function in question? (yes, you can index the use of a function or expression to speed up views and queries)

    3. Are you using PostgreSQL on Windows, with the Cygwin Unix emulation toolkit? (There will be native Win32 releases of PostgreSQL soon, but meanwhile, do not expect full production-quality performance for PostgreSQL on Windows)

    4. Are you sure the problem is on the server side?

    a. Check your network, if you are making remote queries from the application server to the database server. If your network connection is dropping packets, or running at slow speed, it is going to take a lot longer to retrieve your recordsets.

    b. Quite a few times I have seen threads mentioning slow performance, where the developer is trying to run a query over ODBC to retrieve 20,000 records, and then wishes to paginate them or sort them in the client environment. Better to keep those records on the server side, and only retrieve small sets for display (especially when using notoriously slow protocols like ODBC). Just about any pagination, sorting, or other manipulation needed can be handled much more efficiently on the server side (perhaps with cursors or stored procedures, if necessary).

    c. Rather than start transaction->run query->retrieve results->run another query->retrieve results->run update query->commit transaction, it is often possible to put all these operations inside one stored procedure, meaning you now only need to do one database call from your programming environment, and aside from the savings in resources, there is much less likelihood of deadlocked transactions, or delayed commits. Also, stored procedures can be saved in a cached/compiled state, providing much better performance.

    Others, please feel free to provide more links to assist in PostgreSQL performance tuning.
    Last edited by rycamor; September 19th, 2006 at 11:44 PM.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    You got all the biggies, here's an oldie that may help out someone.

    Josh Berkus' Adventures in Postgres - it's mostly about effective use of indexes.
    PostgreSQL, it's what's for dinner...
  4. #3
  5. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    Fresh off the mailing list...


    Here's a
    powerpoint / powerpoint exported html which has some decent info. Highlights include comparison between PG, MySQL and Informix, quick installation guide, quick postgres.conf tweaks, query tips, tips on reading explain analyze output. It could have been a lot more in depth, but I believe that it's a decent start, and the PG guys will be taking this guy's presentation (once he frees it from any licensing restrictions) and will build off of it.

    Enjoy,
    -b
    Last edited by bcyde; October 8th, 2003 at 11:25 AM.
    PostgreSQL, it's what's for dinner...
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0
    Hi:
    I have a question. I know my problem is not on the server side. But I have very low performance,using ODBC driver and offline recordset of Visual Basic.

    Some tests:
    MSQL (ODBC) = less than 1 second
    pgsql (ODBC) = more than 8 minutes.

    pgsql(console) = less than 1 second.

    I think this Visual Basic component may be a problem, but I have to use it.

    I looking for a OLEDB provider for Postgres, but there is in development phase.

    I apreciate any help

    Thanks,
    Mauricio Fernández
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Hi mfernandez,

    We will do our best to help you, but can I ask you to put this question in it's own thread? This forum thread is a "sticky", meaning it is intended to stay at the top of the page, and is mainly intended for a place to put some tips about performance. Any question that involves further discussion would benefit much more by starting its own thread.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    A couple of articles I wrote myself. I'm not trying to toot my own horn, so if this is seen as an attempt at that feel free to delete this post (or just let me know and I'll delete it/edit it myself).

    Since I'm relatively new to actually administering a Postgres install in a production environment I decided to document my experiences and gear the articles towards linux/postgres newbies. No much new, but hopefully presented in a manner that is a little slower and easier to understand (at least I believe so).

    Upgrading a postgres install
    Tweaking the postgres conf
    PostgreSQL, it's what's for dinner...
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0
    Hi, this documents are very useful, thanks a lot.

    My main problem have been solved.
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Nice work, bcyde . Those articles should be posted on The TechDocs site also.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    Specify all tables WITHOUT OIDS


    Another small addition to PostgreSQL performance tweaks: I recommend creating all tables without object identifiers. (add WITHOUT OIDS at the end of every table creation statement, right before the final ";":

    CREATE TABLE example(
    col1 SERIAL NOT NULL,
    col2 whatever TEXT NOT NULL,
    ) WITHOUT OIDS;

    (See the documentation on CREATE TABLE, and search for "WITHOUT OIDS".)

    Basically, object identifiers are an additional "hidden" (actually, they are not really hidden, because you can access them in any query) unique identifier to every row in your table. This may sound like a good thing at first, but really, there are a couple logical problems with OIDs, as well as the additional performance implications.

    1. Having an additional automated identifier violates the Relational model. One of the basic tenets of the relational model is (stated informally) that the only way you query the system for data is through the explicit declared attributes of the database (any tuple is purely a combination of attribute values in the declared relation), not through any additional means, such as "row counters", pointers, hidden identifiers, etc... There are many reasons this is important, but in a nutshell, additional identifiers allow programmers to take shortcuts that can lead to serious data corruption later.

    2. As stated in the documentation, for extremely large tables, uniqueness cannot be guaranteed. Read that again. To me this seems like a disaster waiting to happen, if any developers of large systems actually have triggers, stored procedures, views, etc... which depend on OIDs to function.

    3. They take an additional 4 bytes per row. This sounds quite small, but that does impose a penalty for sorting and searching. It is essentially the equivalent of an additional, useless INTEGER column in your table. (hey, OIDs are simply 32-bit integers)

    4. If you happen to have any logic in your database which depends on OIDs, you have an additional set of headaches to deal with for backup/recovery, since you need to explicitly preserve OIDs, or they are re-generated for you.

    5. If you truly understand relational logic, and if you design your databases properly (I.E. don't allow duplicate rows, make sure all primary keys are NOT NULL, etc...), then there is absolutely no additional benefit to using OIDs.

    6. In fact, there is even the possibility that visible OIDs may be dropped from PostgreSQL completely in the future. (search the mailing lists for discussion on this). I personally hope this will happen. Hidden identifiers are of course necessary to database engine internals, but OIDs are a perfect example of "implementation showing through the interface".
    Last edited by rycamor; May 13th, 2004 at 12:18 PM.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  18. #10
  19. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    Performance Guide for 8.0


    Josh Berkus and Joe Conway, frequent contributors to the PG community have a performance guide for PostgreSQL 8.x which has some slight updates with some of the new naming schemes for the .conf parameters.

    http://www.powerpostgresql.com/PerfList
    PostgreSQL, it's what's for dinner...
  20. #11
  21. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    ODBC Next Generation


    For those afflicted with the task of connecting Windows-based applications to a PostgreSQL back-end, there have been lots of complaints about the speed of psqlODBC, especially for running reports or querying large result sets.

    Fortunately, there is ODBC Next Generation in progress which radically speeds up some of these things. For example, one friend of mine doing a Windows/PostgreSQL application with an Access front-end found that a 25-second query now takes about one second. Even though this is just an Alpha release, it seems quite stable. I won't tell you to start using it in production, but it seems like some people are, without serious problems.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  22. #12
  23. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Well, it's been just over a year since we have posted any new information here, so I guess it's time to get everyone up-to-date. With PostgreSQL 8.0, then 8.1, and now 8.2 beta available, there have been many interesting additions:

    1. Autovacuum, which as of 8.0 was still just an add-on contrib module, is now fully available in 8.1 and onward. This allows you to have your database optimized on a continual basis at whatever priority level you want.

    2. Tablespaces, available since 8.0, allow you to decide on which physical filesystem/device each table of your database will reside. It's a great way to begin distributing your data over more than one physical storage device, as your databases grow large. For example if you have a 200GB database that is starting to tax the capacity of your RAID array, you can get a second RAID array and put your largest table or two on that drive.

    BTW, check out this discussion on PostgreSQL with a high-end machine.

    3. Horizontal partitioning has been unofficially available for awhile, by working the with table inheritance mechanism, but is now an officially-documented behavior. What this means is that you can go one step further than tablespaces, and distribute a table over more than one storage device/partition. This sort of option works especially well if you have data that naturally falls into groups, such as months of the year, where you could have each month of data reside in a different partition. Ergo, any query on any particular month would only hit a small subset of the data. Changes in the upcoming 8.2 release will make this even more flexible.

    4. In-memory bitmaps for index lookup. (see in 8.1 release notes) this is an automatic behavior of 8.1+ that allows in-memory bitmaps to handle queries using indexes. This is particularly helpful for any query using more than one index, such as WHERE userid=[whatever] AND signup_date>[somedate].

    Speaking of 8.2, here are the notes for the beta. Lots of goodies in there, including several great performance increases.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    15
    Rep Power
    0
    Hi bcyde,

    I cant access your link.

    Upgrading a postgres install
    and
    Tweaking the postgres conf

    they both return 404 not found.

    May I ask the exact url.

    Many thanks
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    Foreign Key Not Work - Postgres


    I have created the following foreign key,

    ALTER TABLE ped_itens
    Ped_itens_idinsumo_fkey ADD CONSTRAINT FOREIGN KEY (idinsumo)
    REFERENCES bas_insumo (idinsumo) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION;

    where I can not delete a product that is being used in a sales order, however the same does not work, someone could guide me the reason why it is not working, or someone already had the same problem.
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Tuning PostgreSQL for Performance -- varlena.com is down, is there a backup? I would really like to read that article.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo