vertical database partitioning in postgres 9.2

Question

i have a table with 60 columns and want to vertically partition it, more or like dividing it in further small tables divided in columns. how to do vertical partitioning in a database using postgres 9.2? did alot of google but couldnt find anything helpful.

table that i want to vertically partition is:

CREATE TABLE insurance.vt_owner
(
  regn_no character varying(10) NOT NULL,
  regn_dt timestamp without time zone,
  purchase_dt timestamp without time zone,
  owner_sr numeric(5,0),
  owner_name character varying(150),
  pan_no character varying(10),
  f_name character varying(150),
  c_add1 character varying(50),
  c_add2 character varying(50),
  c_city character varying(50),
  c_district character varying(30),
  c_pincode character varying(6),
  p_add1 character varying(50),
  p_add2 character varying(50),
  p_city character varying(50),
  p_district character varying(30),
  p_pincode character varying(6),
  owner_cd numeric(5,0),
  owner_cd_desc character varying(50),
  regn_type character varying(1),
  regn_type_desc character varying(50),
  vh_class numeric(5,0),
  vh_class_desc character varying(50),
  chasi_no character varying(30),
  eng_no character varying(30),
  maker numeric(5,0),
  maker_desc character varying(50),
  maker_model character varying(50),
  body_type character varying(3),
  body_type_desc character varying(50),
  no_cyl numeric(2,0),
  hp character varying(10),
  seat_cap numeric(3,0),
  stand_cap numeric(3,0),
  sleeper_cap numeric(2,0),
  unld_wt numeric(9,0),
  ld_wt numeric(9,0),
  fuel numeric(3,0),
  fuel_desc character varying(50),
  color character varying(50),
  manu_mon numeric(2,0),
  manu_yr numeric(4,0),
  fit_dt timestamp without time zone,
  norms numeric(2,0),
  norms_desc character varying(50),
  wheelbase character varying(10),
  cubic_cap character varying(10),
  floor_area numeric(7,3),
  ac_fiitted character(1),
  audio_fiitted character(1),
  video_fiitted character(1),
  vch_purchase_as character(1),
  vch_catg character(3),
  dealer_cd numeric(5,0),
  dealer_cd_desc character varying(50),
  sale_amt numeric(9,0),
  laser_code character varying(10),
  garage_add character varying(50),
  state_cd character varying(2) NOT NULL,
  rto_cd character varying(3) NOT NULL,
  CONSTRAINT vt_owner_pkey PRIMARY KEY (state_cd, rto_cd, regn_no)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE insurance.vt_owner
  OWNER TO postgres;

-- Index: insurance."index_VT_OWNER"

-- DROP INDEX insurance."index_VT_OWNER";

CREATE INDEX "index_VT_OWNER"
  ON insurance.vt_owner
  USING btree
  (regn_no COLLATE pg_catalog."default", chasi_no COLLATE pg_catalog."default", state_cd COLLATE pg_catalog."default", rto_cd COLLATE pg_catalog."default");

thanks in advance

postgresql doesn't support vertical partitioning. – Pavel Stehule Jul 18 '13 at 10:35 — Pavel Stehule, Jul 18 '13 at 10:35

score 1 · Answer 1 · answered Jul 18 '13 at 15:42

1

I don't think you are going to get a decent answer unless you specify why you want to vertically partition. Sharding? Performance? Denormalizing? Only certain columns are necessary in the bulk of your queries?

Somewhat related, if your dataset (uncompressed) in under 1TB, you can try Vertica's community edition for free, subject to their licensing restrictions (read the fine print). Vertica is a columnar storage database and for many use-cases is remarkably fast. I've used it in the past for analytics and it worked really well (there are some gotchas of course).

answered Jul 18 '13 at 15:42

bma

9,424
2
33
22

well the thing is that.. i need to run a query on this table that reads only 6 columns out of the 60 columns. therefore when this query executes it performs a row wise read, that reads the other 54 columns as well, which i do not require, hence degrading the performance. as we know that postgresql is a row oriented database, that is the reason why i required to vertically partition it. i found a research paper over the internet, regarding IMPLEMENTATION OF COLUMN-ORIENTED DATABASE IN POSTGRESQL FOR OPTIMIZATION OF READ-ONLY QUERIES. reading it and will try to implement it. – Varun Gupta Jul 19 '13 at 06:47
please post if you have any further solutions to it. THANKS! – Varun Gupta Jul 19 '13 at 06:50
Did you try creating a table with the subset of columns that you need to see if the performance is acceptable? If so, populating that table can be accomplished any number of ways, including triggers, cron jobs, direct loading, etc. – bma Jul 19 '13 at 14:42
well i am currently doing that only..creating sub tables from the parent table. a column oriented approach. – Varun Gupta Jul 20 '13 at 05:40

score 0 · Answer 2 · answered May 27 '15 at 18:43

0

Look for INSTEADOF Trigger. Split the table with 2 different names and unify them throught a view. Apply the instead of trigger on the view, and all inserts and deletes must be applicable to both tables. Look for handling updates carefully too

answered May 27 '15 at 18:43

Alexandre Hadjinlian Guerra

11
3

vertical database partitioning in postgres 9.2

2 Answers2