Leo Liang
Leo Liang

Reputation: 55

Low cost way to host a large table yet keep the performance scalable?

I have a growing table storing time series data, 500M entries now, and 200K new records every day. The total size is around 15GB for now.

My clients are querying the table via a PHP script mostly, and the size of the result set is around 10K records (not very large).

select * from T where timestamp > X and timestamp < Y and additionFilters

And I want this operation cheap.

Currently my table is hosting in Postgres 7, on a single 16G memory Box, and I would love to see some good suggestion for me to host this in low cost and also allow me to scale up for performance if needed. The table serves:
1. Query: 90%
2. Insert: 9.9%
2. Update: 0.1% <-- very rare.

Upvotes: 1

Views: 128

Answers (1)

PostgreSQL 9.2 supports partitioning and partial indexes. If there are a few hot partitions, and you can put those partitions or their indexes on a solid state disk, you should be able to run rings around your current configuration.

There may or may not be a low cost, scalable option. It depends on what low cost and scalable mean to you.

Upvotes: 1

Related Questions