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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
