I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with partitioning, but a multi-layer partition:
host-service inherits service, host-service-yyyymm inherits host-service.
I've worked out an example scheme for a specific service (php errors in this case) and am looking for some PostgreSQL experts to critique it for obvious performance bottlenecks.
// SET UP MASTER PHP LOG TABLE // CREATE TABLE php ( log_id int not null, host char(5), logdate date not null, message text ); // SET UP HOST-SPECIFIC 'PARTITIONS' // CREATE TABLE host1_php ( CHECK ( host = 'host1' ) ) INHERITS (php); CREATE TABLE host2_php ( CHECK ( host = 'host2' ) ) INHERITS (php); // SET UP HOST-SPECIFIC TIME 'PARTITIONS' // CREATE TABLE host1_php_2011m12 ( CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' ) ) INHERITS (host1_php); CREATE TABLE host1_php_2012m01 ( CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' ) ) INHERITS (host1_php); CREATE TABLE host2_php_2011m12 ( CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' ) ) INHERITS (host2_php); CREATE TABLE host2_php_2012m01 ( CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' ) ) INHERITS (host2_php); CREATE INDEX host1_php_2011m12_logdate ON host1_php_2011m12 (logdate); CREATE INDEX host1_php_2012m01_logdate ON host1_php_2012m01 (logdate); CREATE INDEX host2_php_2011m12_logdate ON host2_php_2011m12 (logdate); CREATE INDEX host2_php_2012m01_logdate ON host2_php_2012m01 (logdate);
I'll also be adding services such as apache access/errors.
I think I have to trigger to insert into the host_service (to utilize the host check constraint), and then each host_service must trigger to insert into the host_service_yyyymm table.
What can I expect performance-wise of such a trigger/partition scheme?
Some extra info discussed in chat:
- PostgreSQL version 9.1.2
- Queries on data won't be very often, and mostly on the current month of data for multiple hosts.
- PHP is only about 1 per minute insert, but total apache is probably going to be around 300-500 per second across all hosts.