Tag Archives: Explain

Keeping on top of your data

Not just because it’s vital for your business.
For most production systems the speed-bottleneck lies with accessing your data. Database are excellent for storing all your data and keeping it organised. However, when it comes to getting it out quickly, especially if you have lots of it, they are not the sharpest of tools.

That’s why I always try to press home the importance of keeping on top of your data.

Even though you think all your data is neatly organised in your perfectly structured database it keeps changing shape, or rather the understanding your database has of your data keeps changing.
For example in PostgreSQL the “shape” of your data is stored in a table called pg_statistics. Data is collected and stored there by analyze.

The query planner uses the data collected in pg_statistics to pick the most efficient way to run your queries. Unfortunately no system is perfect, even the best planner makes mistakes. You have to strike a balance between letting analyze collect as much data as possible to give your database a better understanding of your data and keeping it slim enough for it to be quick.

So as much as you can trust machines I suggest you try to keep on top of your data yourself.

There’s a couple of very simple ways to do that.

First. Keep an eye on your database logs, exactly like you do with your webserver logs. There’s a few open-source applications that can help you do that like Enterprise Postgres Query Analyser.
This will give you a basic understanding of which query/ies you will have to focus on.

Once you have an idea of which ones are the slowest queries you should keep an eye on the explain plan at regular intervals. In PostgreSQL I have a scheduled job that every day runs “EXPLAIN ANALYZE” on the heaviest queries in my system and compares the output with the previous day’s.

It’s a lot of work and you are probably better off confronting these problems as they come up and not waste valuable development time creating the most optimised database ever.
Setting up auto-vacuuming properly will keep you safe for a long while.

Tagged , , , , , , , , ,
Follow

Get every new post delivered to your Inbox.