Optimal database table optimization method

Question

I have a database table that is growing too big (few hundred million rows) that needs to be optimized, but before I get into partitioning it, I thought I'd ask about suggestions.

Here is the usage:

0 . Table contains about 10 columns of length about 20 bytes each.

1. INSERTS are performed at a rate of hundreds of times per second.

2. SELECT statements are performed based on column 'a' (where a='xxxx' ) a few times per hour.

3. DELETE statements are performed based on a DATE column. (delete where date older than 1 year) usually once per day.

The key requirement is to speed up INSERT and SELECT statements, and be able to keep history data of 1 year back without locking the whole table down while deleting.

I would guess that I must have two indexes, one for column 'a', and the other for the date field. or is it possible to optimize both?

Will there be a necessary trade-off between speed on select and speed of delete?

Is partitioning the only solution? What are good strategies for partitioning such table?

I'm using a PostgreSQL 8.4 database.

Answer 1

Rather than keeping it a single physical table, have you looked into PostgreSQL partitioning? It's supported as of version 8.1.

Partitioning can help you avoid the problem of choosing between fast INSERT vs fast DELETE performance. You can always partition the table by Year/Month, and just drop the partitions that you no longer need. Dropping partitions is extremely fast, and inserting into small partitions is also extremely fast.

From the manual:

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:

• Query performance can be improved dramatically for certain kinds of queries.
• Update performance can be improved too, since each piece of the table has indexes smaller than an index on the entire data set would be. When an index no longer fits easily in memory, both read and write operations on the index take progressively more disk accesses.
• Bulk deletes may be accomplished by simply removing one of the partitions, if that requirement is planned into the partitioning design. DROP TABLE is far faster than a bulk DELETE, to say nothing of the ensuing VACUUM overhead.
• Seldom-used data can be migrated to cheaper and slower storage media.

The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

Currently, PostgreSQL supports partitioning via table inheritance. Each partition must be created as a child table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set. You should be familiar with inheritance (see Section 5.8) before attempting to implement partitioning.