The best tips & troubleshooting for PostgreSQL databases.
One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen … Read more
If you have tried installing PostgreSQL database on CentOS 5.5 using yum, you will notice that you an old, outdated version! There are several ways to get the 8.4.x version of Postgres installed on this OS, but here is the quick and easy method! Installing PostgreSQL 8.4.x on CentOS 5.5 Download the PostgreSQL 8.4 repository … Read more
If you are making modifications to the Postgres configuration file postgresql.conf (or similar), and you want to new settings to take effect without needing to restart the entire database, there are two ways to accomplish this. Another similar command essential to PostgreSQL, is to reload pg_hba.conf. Option 1: From the command-line shell su – postgres … Read more
This article will show you how to see a list view a list of locks that are currently open (or waiting) on your database rows and tables. This information is beneficial when debugging timing-related errors and data inconsistencies. Login to the PostgresSQL command-line interface psql -U [username] [database_name] Run the following query: select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from … Read more
This article will show you how to see a list of open database connections as well as all active queries that are running on a PostgresSQL 8.x database. This information can be very beneficial when profiling your application and determining queries that have “gone wild” and are eating CPU cycles. Login to the PostgresSQL command-line … Read more
This article will show you how to determine when your database tables were last vacuumed, auto-vacuumed, analyzed, and auto-analyzed on a PostgresSQL database. Performing these operations are critical in keeping the database optimized and performant. What is the purpose of PostgreSQL Vacuum? PostgreSQL’s “VACUUM” is a maintenance operation designed to reclaim storage occupied by expired … Read more
This article will show you how to drop a constraint, such as a foreign key constraint, on a PostgresSQL database. There are several different types of constraints and we’ll show you examples of each. Sure, here are the same instructions with headers and sub-headers: Dropping a PRIMARY KEY Constraint in PostgreSQL To remove a primary … Read more
This article will show you a very simple way to find the size of a PostgreSQL database using a SQL SELECT statement. This will work on PostgreSQL version 8.3.3, but should work on older versions as well. Looking for optimize your database performance, check out How to find when PostgreSQL tables were auto-vacuumed and auto-analyzed … Read more