Postgresql partitioning - single hot table or distributed

From: sam mulube <sam(dot)mulube(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Postgresql partitioning - single hot table or distributed
Date: 2010-06-29 20:00:21
Message-ID: AANLkTilmirvSBsaSlLBdK9FEWsJgz6-1NS3S__Sr5mb7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

we are considering database partitioning as a possible solution to
some performance issues we are having with our database, and we are
trying to decide on a partitioning scheme. We have a moderately write
heavy application (approx 50 inserts per second, with writes
outnumbering reads by roughly 5:1), and the table in question looks
something like this:

-------------------------------------------------------------------------------
column name : id | value | server_id | created_at
column type : integer | string | integer | timestamp
with time zone
other info : pk | | fk, indexed |
indexed
-------------------------------------------------------------------------------

Or initial thoughts on partitioning was to partition by date using the
created_at column, with a separate partition for each month; however
the vast majority of our inserts would be for 'now', so we would be
almost entirely writing to the partition for the current month. Other
month partitions might get occasional updates, but this would be a
relatively infrequent occurrence.

Alternatively we wondered about partitioning by the server_id foreign
key, using for example the modulo of the foreign key id. This would
give us a finite number of partitions (rather than the potentially
unbounded date option), and would likely cause writes to be much more
evenly distributed between the partitions.

Does anyone have any likely idea which would be the better choice. The
single hot table getting most of the inserts, which might mean any
indexes are fully in memory, or dividing the writes more evenly over
all of our partitions?

Many thanks for any advice.

Sam

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Geoffrey 2010-06-29 20:04:27 dropdb weirdness
Previous Message MD 2010-06-29 19:28:33 Looking for multithreaded build of libpq for Windows