The best way to optimize a database table

Question

I have a database table that is getting too large (several hundred million rows) that needs to be optimized, but before I get into splitting it, I thought that I would ask about suggestions.

Here is the usage:

0. The table contains about 10 columns with a length of about 20 bytes each.

• INSERTS are executed at a speed of hundreds of times per second.

• SELECT statements are executed based on the 'a' column (where a = 'xxxx') several times per hour.

• DELETE statements are executed based on a DATE column. (delete if date is older than 1 year) usually once a day.

The key requirement is to speed up the INSERT and SELECT statements and the ability to save historical data for 1 year ago without blocking the entire table when deleted.

I would suggest that I should have two indexes: one for the column 'a', and the other for the date field. Or can both be optimized?

Will there be a necessary compromise between the speed of choice and the speed of removal?

Does the only solution share? What are good strategies for splitting such a table?

I am using a PostgreSQL 8.4 database.

Answer 1

Instead of storing it in one physical table, have you looked at PostgreSQL Partitioning ? It has been supported since version 8.1.

INSERT Fast DELETE. / , . , .

:

  

     .      :

  

• .
• , , . , .
• , . DROP TABLE , , VACUUM.
• .

  

,      .     ,          ,      ,           .

         

PostgreSQL .           .          ;      .      (.     5.8), .

  

Answer 2

- , , :

hash(a). a - , a%256 . , - substring(md5(a) for 2).

.

, , . ( XX: 30) :

delete from table_name
where date<(current_date - interval '1 year')
and
  hash(a)
  =
  (extract(doy from current_timestamp) * 24
    + extract(hour from current_timestamp))::int % 256;

---

EDIT: :

create function hash(a text) returns text as $$ select substring(md5($1) for 1) $$ language sql immutable strict;
CREATE TABLE tablename (id text, mdate date);
CREATE TABLE tablename_partition_0 ( CHECK ( hash(id) = '0' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_1 ( CHECK ( hash(id) = '1' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_2 ( CHECK ( hash(id) = '2' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_3 ( CHECK ( hash(id) = '3' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_4 ( CHECK ( hash(id) = '4' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_5 ( CHECK ( hash(id) = '5' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_6 ( CHECK ( hash(id) = '6' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_7 ( CHECK ( hash(id) = '7' ) ) INHERITS (tablename); 
CREATE TABLE tablename_partition_8 ( CHECK ( hash(id) = '8' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_9 ( CHECK ( hash(id) = '9' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_a ( CHECK ( hash(id) = 'a' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_b ( CHECK ( hash(id) = 'b' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_c ( CHECK ( hash(id) = 'c' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_d ( CHECK ( hash(id) = 'd' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_e ( CHECK ( hash(id) = 'e' ) ) INHERITS (tablename);
CREATE TABLE tablename_partition_f ( CHECK ( hash(id) = 'f' ) ) INHERITS (tablename);
analyze;
explain select * from tablename where id='bar' and hash(id)=hash('bar');

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Result  (cost=0.00..69.20 rows=2 width=36)
   ->  Append  (cost=0.00..69.20 rows=2 width=36)
         ->  Seq Scan on tablename  (cost=0.00..34.60 rows=1 width=36)
               Filter: ((id = 'bar'::text) AND ("substring"(md5(id), 1, 1) = '3'::text))
         ->  Seq Scan on tablename_partition_3 tablename  (cost=0.00..34.60 rows=1 width=36)
               Filter: ((id = 'bar'::text) AND ("substring"(md5(id), 1, 1) = '3'::text))
(6 rows)

hash(id)=hash('searched_value') , Postgres .

---

EDIT: :

create rule tablename_rule_0 as
  on insert to tablename where hash(NEW.id)='0'
  do instead insert into tablename_partition_0 values (NEW.*);
create rule tablename_rule_1 as
  on insert to tablename where hash(NEW.id)='1'
  do instead insert into tablename_partition_1 values (NEW.*);
-- and so on
insert into tablename (id) values ('a');
select * from tablename_partition_0;
 id | mdate 
----+-------
 a  | 
(1 row)

Answer 3

.

, ( DAO) , , (, , ) / "" . ​​ script script, " " DAO.

"" ( ); , , , , "" INSERT/SELECT ( , )

Answer 4

, truncate delete, , .

Answer 5

, , "a" , ( ) ( ), .

, .

, ? / ?