What follows for the next few posts is a somewhat edited version of a set of advice I am giving to a Sun team on redesigning a C++ application which was built for MySQL, ported to PostgreSQL, and never performance optimized. It occurred to me that this advice would be generally useful to the community, so here goes. (and yes, I’ll pick up “Four Horsemen” again eventually.)
Application Design for PostgreSQL Performance
Query Writing Rules
For all database management systems (DBMSes), “round-trip” time is significant. This is the amount of time which it takes a query to get through the the language parser, the driver, across the network interface, the database parser, the planner, the executor, the parser again, back across the network interface, through the driver data handler, and to the client application. DBMSes vary in the amount of
time and CPU they take to process this cycle, and for a variety of reasons PostgreSQL is a the high end of time and system resources per round-trip.
Further, PostgreSQL has significant per-transaction overhead, including log output and visibility rules which need to be set with each transaction. While you may think that you are not using transactions for singleton read-only SELECT statement, in fact every single statement in PostgreSQL is in a transaction. In the absence of an explicit transaction, the statement itself is an implicit transaction.
Offsetting this, PostgreSQL is only barely second to Oracle in processing large complex queries, and has the capability to handle complex multi-statement transactions with overlapping concurrency conflicts with ease. We also support cursors, both scrollable and non-scrollable.
Tip 1: Never use many small selects when one big query could go the job.
It’s common in MySQL applications to handle joins in the application code;
that is, by querying the ID from the parent record and then looping through the child records with that ID manually. This can result in running hundreds or thousands of queries per user interface screen. Each of these queries carres 2-6 milleseconds of round-trip time, which doesn’t seem significant until you add it up for 1000 queries, at which point you’re losing 3-5 seconds to round trip time. Comparatively, retrieving all of those records in a single query only takes a few
hundred milleseconds, a time savings of 80%.
Tip 2: Group many small UPDATES, INSERTS or DELETEs into large statements, or failing that, large transactions.
First, the lack of subselects in early versions of MySQL has caused application developers to design their data modification statements (DML) in much the same way as joins-in-middleware. This is also a bad approach for PostgreSQL. Instead, you want to take advantage of subselects and joins in your UPDATE, INSERT and DELETE statements to try to modify batches in a single statement. This reduces round-trip time and transaction overhead.
In some cases, however, there is no single query which can write all the rows you want and you have to use a bunch of serial statements. In this case, you want to make sure to wrap your series DML statements in an explicit transaction (e.g.
BEGIN; UPDATE; UPDATE; UPDATE; COMMIT. This reduces transaction
overhead and can cut execution time by as much as 50%.
Tip 3: Consider bulk loading instead of serial INSERTS
PostgreSQL provides a bulk loading mechanism called COPY, which takes
tab-delimited or CSV input from a file or pipe. Where COPY can be used instead of hundreds or thousands of INSERTS, it can cut execution time by up to 75%.
Tip 4: DELETE is expensive
It’s common for application developers to think that a DELETE statement is virtually without cost. You’re just unlinking some nodes, right? Wrong. DBMSes are not filesystems; when you delete a row, indexes need to be updated and free space needs to be cleaned up, making a delete in fact more costly than an insert. Thus applications which habitually delete all detail rows and replace them with new every time a use makes any change are saving effort on the application side by pushing it onto the database. Where possible, this should be replaced with more discriminating row-replacement behavior, like updating only modified
Also, when clearing a whole table, always use the TRUNCATE TABLE statement rather than DELETE FROM TABLE. The former is up to 100 times faster than the latter because it batch processes the whole table as a unit instead of one row at a time.
Tip 5: Use PREPARE/EXECUTE for query loops
Sometimes despite your attempts to consolidate loops of near-identical queries
into large statements, it’s not possible due to the structure of your application. That’s what PREPARE … EXECUTE is for; it allows the database engine to skip parsing and planning for each query iteration.
query_handle = query(‘SELECT * FROM TABLE WHERE id = ?’)(parameter_type = INTEGER)
Then start your loop:
Classes for prepared statements for C++ are explained in the libpqxx documentation.
This will reduce query execution time in direct proportion to the size of the looped set.
Tip 6: Use connection pools effectively
For a web application, you’ll find that as much as 50% of your potential performance can be controlled through your use of, and proper configuration of, connection pooling. This is because creating and dropping database connections takes a significant amount of system time, and excess idle connections still equire RAM and system resources.
There are a number of tools you can use to do connection pooling on PostgreSQL. A third-party Open Source tool which is very popular is pgPool. However, for a C++ application HA requirements, it’s probably better to use libpqxx’s native pseudo-pooling technique called “lazy connections” . I suggest
contacting the libpqxx mailing list for more information on how to use this utility.
With PostgreSQL, you want to have as many persistent connections (or connection objects) defined as your normal peak concurrent connection usage. So if max normal usage (first thing in the morning, say) is 200 concurrent connections from agents, users and components, then you want to have that many defined so that your application does not have to wait for new connections during a peak when they will be slow to fork.