pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alan Stange <stange@rentec.com>
To: Ramon Bastiaans <bastiaans@sara.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: multi billion row tables: possible or insane?
Date: Tue, 01 Mar 2005 21:28:36 -0500
Message-ID: <422524D4.6000009@rentec.com> (raw)
In-Reply-To: <42243725.6060804@sara.nl>
References: <42243725.6060804@sara.nl>

Ramon Bastiaans wrote:

> I am doing research for a project of mine where I need to store 
> several billion values for a monitoring and historical tracking system 
> for a big computer system. My currect estimate is that I have to store 
> (somehow) around 1 billion values each month (possibly more).
>
> I was wondering if anyone has had any experience with these kind of 
> big numbers of data in a postgres sql database and how this affects 
> database design and optimization.
>
> What would be important issues when setting up a database this big, 
> and is it at all doable? Or would it be a insane to think about 
> storing up to 5-10 billion rows in a postgres database.
>
> The database's performance is important. There would be no use in 
> storing the data if a query will take ages. Query's should be quite 
> fast if possible.
>
> I would really like to hear people's thoughts/suggestions or "go see a 
> shrink, you must be mad" statements ;)

It just dawned on me that we're doing something that, while not the 
same, might be relevant.  One of our tables has ~85M rows in it 
according to the output from an "explain select * from table".  I don't 
plan on trying a select count(*) any time soon :)    We add and remove 
about 25M rows a day to/from this table which would be about 750M 
rows/month total.  Given our current usage of the database, it could 
handle a larger row/day rate without too much trouble.  (The problem 
isn't adding rows but deleting rows.)

    Column    |   Type   | Modifiers
--------------+----------+-----------
 timeseriesid | bigint   |
 bindata      | bytea    |
 binsize      | integer  |
 rateid       | smallint |
 ownerid      | smallint |
Indexes:
    "idx_timeseries" btree (timeseriesid)

In this case, each bytea entry is typically about  2KB of data, so the 
total table size is about 150GB, plus some index overhead.

A second table has ~100M rows according to explain select *.  Again it 
has about 30M rows added and removed / day.  

   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 uniqid     | bigint                |
 type       | character varying(50) |
 memberid   | bigint                |
 tag        | character varying(50) |
 membertype | character varying(50) |
 ownerid    | smallint              |
Indexes:
    "composite_memberid" btree (memberid)
    "composite_uniqid" btree (uniqid)

There are some additional tables that have a few million rows / day of 
activity, so call it 60M rows/day added and removed.  We run a vacuum 
every day.

The box is an dual Opteron 248 from Sun.   Linux 2.6, 8GB of memory.  We 
use reiserfs.  We started with XFS but had several instances of file 
system corruption.  Obviously, no RAID 5.  The xlog is on a 2 drive 
mirror and the rest is on separate mirrored volume.  The drives are 
fiber channel but that was a mistake as the driver from IBM wasn't very 
good.

So, while we don't have a billion rows we do have ~200M total rows in 
all the tables and we're certainly running the daily row count that 
you'd need to obtain.   But scaling this sort of thing up can be tricky 
and your milage may vary.

In a prior career I ran a "data intensive computing center" and helped 
do some design work for a high energy physics experiment:  petabytes of 
data, big tape robots, etc., the usual Big Science toys.   You might 
take a look at ROOT and some of the activity from those folks if you 
don't need transactions and all the features of a general database like 
postgresql.

-- Alan



view thread (15+ messages)  latest in thread

Message-ID: <422524D4.6000009@rentec.com>
Permalink:  ../422524D4.6000009@rentec.com/
Also on:    postgresql.org/message-id/422524D4.6000009@rentec.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: stange@rentec.com, bastiaans@sara.nl
  Subject: Re: multi billion row tables: possible or insane?
  In-Reply-To: <422524D4.6000009@rentec.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox