CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-partitioning
Vojtech
Vojtech

Reputation: 2603

PostgreSQL partitioning and indexing

I'm new to PostgreSQL (version 9) partitioning and I need an advice please. I have a set of parts for country A and another set of parts for country B. Each set has about two million records. I need to load both sets into the database. Each set needs to be updated weekly. By updating the set I mean clearing all data of a set and loading it again from a file. Sets should be updated independently so when clearing data of set A I must not clear data of set B. I understood that when I store each set in a separate partition I can truncate each partition independently which is much faster then deleting records. So I decided to do it this way (there are more columns in the table 'part' but they aren't important for this question):

CREATE TABLE part (
  country            CHAR(3) NOT NULL,
  manufacturer_code  CHAR(2) NOT NULL,
  part_code          CHAR(4) NOT NULL,
  part_description   VARCHAR(100)
);

CREATE TABLE part_cze (
  CHECK (country = 'CZE')
) INHERITS (part);

CREATE INDEX idx__part_cze ON part_cze (part_code, manufacturer_code);

CREATE TABLE part_svk (
  CHECK (country = 'SVK')
) INHERITS (part);

CREATE INDEX idx__part_svk ON part_svk (part_code, manufacturer_code);

The application queries table 'part' to fetch the data. The query may look like this:

SELECT * FROM part WHERE country='CZE' AND part_code='4578' AND manufacturer_code='22'

My questions:

  1. Is the above scheme correct or would you recommend something else?
  2. Do I need indexes on table part or part_cze? If I understand well Postgres fetches data from table part_cze which has the index so table part shouldn't need it.
  3. Should the index idx__part_cze contain column country or is it sufficient that data are separated to partitions by the country?
  4. If I created index on table part should it contain column country?

Upvotes: 3

Views: 621

Answers (1)

Eelke
Eelke

Reputation: 21993

  1. Looks fine, except that you do not define primary keys but that isn't a requirement for postgres.

  2. No indexes needed on 'part' as long as there is no siginificant amount of data in that table.

  3. country shouldn't be in the index postgres will select the right table based on the check constraints. Putting country in the index will only serve to make the index larger and thus less efficient.

  4. Only create an index on part if you put a significant amount of data in that table. Then depending on the queries and the actual data in the table it may or may not help to put country in the index.

Upvotes: 1

Related Questions