From: Nickolay <nitro@zhukcity.ru>
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG optimization question
Date: Sat, 09 Jan 2010 15:42:08 +0300
Message-ID: <4B4879A0.2060703@zhukcity.ru> (raw)
In-Reply-To: <2f4958ff1001090418i78dee992t5ff2d78a54187c07@mail.gmail.com>
References: <4B485975.70506@zhukcity.ru>
<2f4958ff1001090418i78dee992t5ff2d78a54187c07@mail.gmail.com>
I do not see any way to normalize this table anymore. it's size is 4Gig
for ~4M rows, i.e. 1Kb per row, i think it's ok.
Also there are 2 indexes: by date_time and by a couple of service fields
(total index size is 250Mb now).
I think i'll be going to partition by months (approx. 1M rows or 1Gig
per month), so it would be like 60 partitions for 5 years. Is that OK
for postgres?
Oh, btw, 95% of queries are searching rows for current date (last 24 hours).
Also we use SELECT...FOR UPDATE row-level locking for updating the rows
in archive (i.e. we INSERT new row when starting outgoing message
transmittion and then doing SELECT...FOR UPDATE and UPDATE for source
(incoming) message when outgoing message was sent), so I guess we would
have to explicitly write the name of partition table (i.e.
"archive_2009_12" instead of "archive") for SELECT...FOR UPDATE and
UPDATE requests, as they may need to access row in previous partition
instead of the current one.
Grzegorz Jaśkiewicz wrote:
> maybe that 'one big table' needs something called 'normalisation'
> first. See how much that will shed off. You might be surprised.
> The partitioning needs to be done by some constant intervals, of time
> - in your case. Whatever suits you, I would suggest to use the rate
> that will give you both ease of archiving/removal of old data (so not
> too wide), and also, one that would make sure that most of the data
> you'll be searching for in your queries will be in one , two
> partitions per query.
>
>
>
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: nitro@zhukcity.ru, gryzman@gmail.com
Subject: Re: PG optimization question
In-Reply-To: <4B4879A0.2060703@zhukcity.ru>
* 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