PostgreSQL Tips and Tricks

Couple of weeks ago, I wrote an article about writing PostGIS Spatial Queries (here).
Today I found the following post: PostgreSQL Tips & Tricks. There are a couple of tips for working with a PostgreSQL database. Here’s a summary:

1. don’t do sequential scans, use indexes instead;
2. index all foreign keys;
3. denormalize can sometimes help;
4. avoid joins;
5. don’t do unachored text searches or full text searching;
6. use the COPY command for bulk data loading;
7. use multi-column indexes (I didn’t know this one…);
8. don’t bother indexing evenly distributed booleans or enum columns;
9. use EXPLAIN ANALYZE to benchmark and compare queries;
10. use expression indexes (didn’t know this either!);
11. indexes can have WHERE clauses;
12. don’t use the database if it isn’t a good fit!

Nice article! A must read for all the postgresql & postgis users out there!