this<\/a> for an overview of the improvements that came in PostgreSQL 11).<\/p>\nThis time we will use the list partitioned table and this is how it looks like currently:<\/p>\n
\npostgres=# d+ traffic_violations_p_list\n Partitioned table \"public.traffic_violations_p_list\"\n Column | Type | Collation | Nullable | Default | Storage | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid | text | | | | extended | | \n date_of_stop | date | | | | plain | | \n time_of_stop | time without time zone | | | | plain | | \n agency | text | | | | extended | | \n subagency | text | | | | extended | | \n description | text | | | | extended | | \n location | text | | | | extended | | \n latitude | numeric | | | | main | | \n longitude | numeric | | | | main | | \n accident | text | | | | extended | | \n belts | boolean | | | | plain | | \n personal_injury | boolean | | | | plain | | \n property_damage | boolean | | | | plain | | \n fatal | boolean | | | | plain | | \n commercial_license | boolean | | | | plain | | \n hazmat | boolean | | | | plain | | \n commercial_vehicle | boolean | | | | plain | | \n alcohol | boolean | | | | plain | | \n workzone | boolean | | | | plain | | \n state | text | | | | extended | | \n vehicletype | text | | | | extended | | \n year | smallint | | | | plain | | \n make | text | | | | extended | | \n model | text | | | | extended | | \n color | text | | | | extended | | \n violation_type | text | | | | extended | | \n charge | text | | | | extended | | \n article | text | | | | extended | | \n contributed_to_accident | boolean | | | | plain | | \n race | text | | | | extended | | \n gender | text | | | | extended | | \n driver_city | text | | | | extended | | \n driver_state | text | | | | extended | | \n dl_state | text | | | | extended | | \n arrest_type | text | | | | extended | | \n geolocation | point | | | | plain | | \n council_districts | smallint | | | | plain | | \n councils | smallint | | | | plain | | \n communities | smallint | | | | plain | | \n zip_codes | smallint | | | | plain | | \n municipalities | smallint | | | | plain | | \nPartition key: LIST (violation_type)\nPartitions: traffic_violations_p_list_citation FOR VALUES IN ('Citation'),\n traffic_violations_p_list_esero FOR VALUES IN ('ESERO'),\n traffic_violations_p_list_sero FOR VALUES IN ('SERO'),\n traffic_violations_p_list_warning FOR VALUES IN ('Warning'),\n traffic_violations_p_list_default DEFAULT\n<\/pre>\nThere is not a single constraint or index and the same is true for the partitions (only showing the first one here but is is the same for all of them):<\/p>\n
\npostgres=# d traffic_violations_p_list_citation\n Table \"public.traffic_violations_p_list_citation\"\n Column | Type | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid | text | | | \n date_of_stop | date | | | \n time_of_stop | time without time zone | | | \n agency | text | | | \n subagency | text | | | \n description | text | | | \n location | text | | | \n latitude | numeric | | | \n longitude | numeric | | | \n accident | text | | | \n belts | boolean | | | \n personal_injury | boolean | | | \n property_damage | boolean | | | \n fatal | boolean | | | \n commercial_license | boolean | | | \n hazmat | boolean | | | \n commercial_vehicle | boolean | | | \n alcohol | boolean | | | \n workzone | boolean | | | \n state | text | | | \n vehicletype | text | | | \n year | smallint | | | \n make | text | | | \n model | text | | | \n color | text | | | \n violation_type | text | | | \n charge | text | | | \n article | text | | | \n contributed_to_accident | boolean | | | \n race | text | | | \n gender | text | | | \n driver_city | text | | | \n driver_state | text | | | \n dl_state | text | | | \n arrest_type | text | | | \n geolocation | point | | | \n council_districts | smallint | | | \n councils | smallint | | | \n communities | smallint | | | \n zip_codes | smallint | | | \n municipalities | smallint | | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('Citation')\n<\/pre>\nAs already mentioned in one of the previous posts we can not create a primary key or unique index because there are duplicate rows in the partitioned table. We can, however, create a standard btree index:<\/p>\n
\npostgres=# create index i1 on traffic_violations_p_list ( model );\nCREATE INDEX\npostgres=# d+ traffic_violations_p_list\n Partitioned table \"public.traffic_violations_p_list\"\n Column | Type | Collation | Nullable | Default | Storage | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid | text | | | | extended | | \n date_of_stop | date | | | | plain | | \n time_of_stop | time without time zone | | | | plain | | \n agency | text | | | | extended | | \n subagency | text | | | | extended | | \n description | text | | | | extended | | \n location | text | | | | extended | | \n latitude | numeric | | | | main | | \n longitude | numeric | | | | main | | \n accident | text | | | | extended | | \n belts | boolean | | | | plain | | \n personal_injury | boolean | | | | plain | | \n property_damage | boolean | | | | plain | | \n fatal | boolean | | | | plain | | \n commercial_license | boolean | | | | plain | | \n hazmat | boolean | | | | plain | | \n commercial_vehicle | boolean | | | | plain | | \n alcohol | boolean | | | | plain | | \n workzone | boolean | | | | plain | | \n state | text | | | | extended | | \n vehicletype | text | | | | extended | | \n year | smallint | | | | plain | | \n make | text | | | | extended | | \n model | text | | | | extended | | \n color | text | | | | extended | | \n violation_type | text | | | | extended | | \n charge | text | | | | extended | | \n article | text | | | | extended | | \n contributed_to_accident | boolean | | | | plain | | \n race | text | | | | extended | | \n gender | text | | | | extended | | \n driver_city | text | | | | extended | | \n driver_state | text | | | | extended | | \n dl_state | text | | | | extended | | \n arrest_type | text | | | | extended | | \n geolocation | point | | | | plain | | \n council_districts | smallint | | | | plain | | \n councils | smallint | | | | plain | | \n communities | smallint | | | | plain | | \n zip_codes | smallint | | | | plain | | \n municipalities | smallint | | | | plain | | \nPartition key: LIST (violation_type)\nIndexes:\n \"i1\" btree (model)\nPartitions: traffic_violations_p_list_citation FOR VALUES IN ('Citation'),\n traffic_violations_p_list_esero FOR VALUES IN ('ESERO'),\n traffic_violations_p_list_sero FOR VALUES IN ('SERO'),\n traffic_violations_p_list_warning FOR VALUES IN ('Warning'),\n traffic_violations_p_list_default DEFAULT\n<\/pre>\nThis is a so called partitioned index and you can verify that with:<\/p>\n
\npostgres=# select * from pg_partition_tree('i1');\n relid | parentrelid | isleaf | level \n----------------------------------------------+-------------+--------+-------\n i1 | | f | 0\n traffic_violations_p_list_citation_model_idx | i1 | t | 1\n traffic_violations_p_list_esero_model_idx | i1 | t | 1\n traffic_violations_p_list_sero_model_idx | i1 | t | 1\n traffic_violations_p_list_warning_model_idx | i1 | t | 1\n traffic_violations_p_list_default_model_idx | i1 | t | 1\n<\/pre>\nIndeed the index cascaded down to all the partitions:<\/p>\n
\npostgres=# d traffic_violations_p_list_citation\n Table \"public.traffic_violations_p_list_citation\"\n Column | Type | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid | text | | | \n date_of_stop | date | | | \n time_of_stop | time without time zone | | | \n agency | text | | | \n subagency | text | | | \n description | text | | | \n location | text | | | \n latitude | numeric | | | \n longitude | numeric | | | \n accident | text | | | \n belts | boolean | | | \n personal_injury | boolean | | | \n property_damage | boolean | | | \n fatal | boolean | | | \n commercial_license | boolean | | | \n hazmat | boolean | | | \n commercial_vehicle | boolean | | | \n alcohol | boolean | | | \n workzone | boolean | | | \n state | text | | | \n vehicletype | text | | | \n year | smallint | | | \n make | text | | | \n model | text | | | \n color | text | | | \n violation_type | text | | | \n charge | text | | | \n article | text | | | \n contributed_to_accident | boolean | | | \n race | text | | | \n gender | text | | | \n driver_city | text | | | \n driver_state | text | | | \n dl_state | text | | | \n arrest_type | text | | | \n geolocation | point | | | \n council_districts | smallint | | | \n councils | smallint | | | \n communities | smallint | | | \n zip_codes | smallint | | | \n municipalities | smallint | | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('Citation')\nIndexes:\n \"traffic_violations_p_list_citation_model_idx\" btree (model)\n<\/pre>\nAs soon as you add another partition it will be indexed automatically:<\/p>\n
\npostgres=# create table traffic_violations_p_list_demo\npostgres-# partition of traffic_violations_p_list\npostgres-# for values in ('demo');\nCREATE TABLE\npostgres=# d traffic_violations_p_list_demo\n Table \"public.traffic_violations_p_list_demo\"\n Column | Type | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid | text | | not null | \n date_of_stop | date | | | \n time_of_stop | time without time zone | | | \n agency | text | | | \n subagency | text | | | \n description | text | | | \n location | text | | | \n latitude | numeric | | | \n longitude | numeric | | | \n accident | text | | | \n belts | boolean | | | \n personal_injury | boolean | | | \n property_damage | boolean | | | \n fatal | boolean | | | \n commercial_license | boolean | | | \n hazmat | boolean | | | \n commercial_vehicle | boolean | | | \n alcohol | boolean | | | \n workzone | boolean | | | \n state | text | | | \n vehicletype | text | | | \n year | smallint | | | \n make | text | | | \n model | text | | | \n color | text | | | \n violation_type | text | | | \n charge | text | | | \n article | text | | | \n contributed_to_accident | boolean | | | \n race | text | | | \n gender | text | | | \n driver_city | text | | | \n driver_state | text | | | \n dl_state | text | | | \n arrest_type | text | | | \n geolocation | point | | | \n council_districts | smallint | | | \n councils | smallint | | | \n communities | smallint | | | \n zip_codes | smallint | | | \n municipalities | smallint | | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('demo')\nIndexes:\n \"traffic_violations_p_list_demo_model_idx\" btree (model)\nCheck constraints:\n \"chk_make\" CHECK (length(seqid) > 1)\n<\/pre>\nYou can as well create an index on a specific partition only (maybe because you know that the application is searching on a specific column on that partition):<\/p>\n
\npostgres=# create index i2 on traffic_violations_p_list_citation (make);\nCREATE INDEX\npostgres=# d traffic_violations_p_list_citation\n Table \"public.traffic_violations_p_list_citation\"\n Column | Type | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid | text | | | \n date_of_stop | date | | | \n time_of_stop | time without time zone | | | \n agency | text | | | \n subagency | text | | | \n description | text | | | \n location | text | | | \n latitude | numeric | | | \n longitude | numeric | | | \n accident | text | | | \n belts | boolean | | | \n personal_injury | boolean | | | \n property_damage | boolean | | | \n fatal | boolean | | | \n commercial_license | boolean | | | \n hazmat | boolean | | | \n commercial_vehicle | boolean | | | \n alcohol | boolean | | | \n workzone | boolean | | | \n state | text | | | \n vehicletype | text | | | \n year | smallint | | | \n make | text | | | \n model | text | | | \n color | text | | | \n violation_type | text | | | \n charge | text | | | \n article | text | | | \n contributed_to_accident | boolean | | | \n race | text | | | \n gender | text | | | \n driver_city | text | | | \n driver_state | text | | | \n dl_state | text | | | \n arrest_type | text | | | \n geolocation | point | | | \n council_districts | smallint | | | \n councils | smallint | | | \n communities | smallint | | | \n zip_codes | smallint | | | \n municipalities | smallint | | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('Citation')\nIndexes:\n \"i2\" btree (make)\n \"traffic_violations_p_list_citation_model_idx\" btree (model)\n<\/pre>\nWhat is not working right now, is creating a partitioned index concurrently:<\/p>\n
\npostgres=# create index CONCURRENTLY i_con on traffic_violations_p_list (zip_codes);\npsql: ERROR: cannot create index on partitioned table \"traffic_violations_p_list\" concurrently\n<\/pre>\nThis implies that there will be locking when you create a partition index an indeed if you create the index in one session:<\/p>\n
\npostgres=# create ndex i_mun on traffic_violations_p_list (municipalities);\nCREATE INDEX\n<\/pre>\n… and at the same time insert something in another session it will block until the index got created in the first session:<\/p>\n
\npostgres=# insert into traffic_violations_p_list ( seqid, date_of_stop ) values ( 'xxxxx', date('01.01.2023'));\n-- blocks until index above is created\n<\/pre>\nYou can limit locking time when you create the partitioned index on the partitioned table only but do not cascade down to the partitions:<\/p>\n
\npostgres=# create index i_demo on only traffic_violations_p_list (accident);\nCREATE INDEX\n<\/pre>\nThis will leave the index in an invalid state:<\/p>\n
\npostgres=# select indisvalid from pg_index where indexrelid = 'i_demo'::regclass;\n indisvalid \n------------\n f\n(1 row)\n<\/pre>\nNow you can create the index concurrently on all the partitions:<\/p>\n
\npostgres=# create index concurrently i_demo_citation on traffic_violations_p_list_citation (accident);\nCREATE INDEX\npostgres=# create index concurrently i_demo_demo on traffic_violations_p_list_demo (accident);\nCREATE INDEX\npostgres=# create index concurrently i_demo_esero on traffic_violations_p_list_esero(accident);\nCREATE INDEX\npostgres=# create index concurrently i_demo_sero on traffic_violations_p_list_sero(accident);\nCREATE INDEX\npostgres=# create index concurrently i_demo_warning on traffic_violations_p_list_warning(accident);\nCREATE INDEX\npostgres=# create index concurrently i_demo_default on traffic_violations_p_list_default(accident);\nCREATE INDEX\n<\/pre>\nOnce you have that you can attach all the indexes to the partitioned index:<\/p>\n
\npostgres=# alter index i_demo attach partition i_demo_citation;\nALTER INDEX\npostgres=# alter index i_demo attach partition i_demo_demo;\nALTER INDEX\npostgres=# alter index i_demo attach partition i_demo_esero;\nALTER INDEX\npostgres=# alter index i_demo attach partition i_demo_sero;\nALTER INDEX\npostgres=# alter index i_demo attach partition i_demo_warning;\nALTER INDEX\npostgres=# alter index i_demo attach partition i_demo_default;\nALTER INDEX\n<\/pre>\nThis makes the partitioned index valid automatically:<\/p>\n
\npostgres=# select indisvalid from pg_index where indexrelid = 'i_demo'::regclass;\n indisvalid \n------------\n t\n(1 row)\n<\/pre>\nThe very same is true for constraints: You can create them on the partitioned table and on specific partitions:<\/p>\n
\npostgres=# alter table traffic_violations_p_list add constraint chk_make check (length(seqid)>1);\nALTER TABLE\npostgres=# d+ traffic_violations_p_list\n Partitioned table \"public.traffic_violations_p_list\"\n Column | Type | Collation | Nullable | Default | Storage | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid | text | | | | extended | | \n date_of_stop | date | | | | plain | | \n time_of_stop | time without time zone | | | | plain | | \n agency | text | | | | extended | | \n subagency | text | | | | extended | | \n description | text | | | | extended | | \n location | text | | | | extended | | \n latitude | numeric | | | | main | | \n longitude | numeric | | | | main | | \n accident | text | | | | extended | | \n belts | boolean | | | | plain | | \n personal_injury | boolean | | | | plain | | \n property_damage | boolean | | | | plain | | \n fatal | boolean | | | | plain | | \n commercial_license | boolean | | | | plain | | \n hazmat | boolean | | | | plain | | \n commercial_vehicle | boolean | | | | plain | | \n alcohol | boolean | | | | plain | | \n workzone | boolean | | | | plain | | \n state | text | | | | extended | | \n vehicletype | text | | | | extended | | \n year | smallint | | | | plain | | \n make | text | | | | extended | | \n model | text | | | | extended | | \n color | text | | | | extended | | \n violation_type | text | | | | extended | | \n charge | text | | | | extended | | \n article | text | | | | extended | | \n contributed_to_accident | boolean | | | | plain | | \n race | text | | | | extended | | \n gender | text | | | | extended | | \n driver_city | text | | | | extended | | \n driver_state | text | | | | extended | | \n dl_state | text | | | | extended | | \n arrest_type | text | | | | extended | | \n geolocation | point | | | | plain | | \n council_districts | smallint | | | | plain | | \n councils | smallint | | | | plain | | \n communities | smallint | | | | plain | | \n zip_codes | smallint | | | | plain | | \n municipalities | smallint | | | | plain | | \nPartition key: LIST (violation_type)\nIndexes:\n \"i1\" btree (model)\nCheck constraints:\n \"chk_make\" CHECK (length(seqid) > 1)\nPartitions: traffic_violations_p_list_citation FOR VALUES IN ('Citation'),\n traffic_violations_p_list_esero FOR VALUES IN ('ESERO'),\n traffic_violations_p_list_sero FOR VALUES IN ('SERO'),\n traffic_violations_p_list_warning FOR VALUES IN ('Warning'),\n traffic_violations_p_list_default DEFAULT\n\npostgres=# d+ traffic_violations_p_list_citation\n Table \"public.traffic_violations_p_list_citation\"\n Column | Type | Collation | Nullable | Default | Storage | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid | text | | | | extended | | \n date_of_stop | date | | | | plain | | \n time_of_stop | time without time zone | | | | plain | | \n agency | text | | | | extended | | \n subagency | text | | | | extended | | \n description | text | | | | extended | | \n location | text | | | | extended | | \n latitude | numeric | | | | main | | \n longitude | numeric | | | | main | | \n accident | text | | | | extended | | \n belts | boolean | | | | plain | | \n personal_injury | boolean | | | | plain | | \n property_damage | boolean | | | | plain | | \n fatal | boolean | | | | plain | | \n commercial_license | boolean | | | | plain | | \n hazmat | boolean | | | | plain | | \n commercial_vehicle | boolean | | | | plain | | \n alcohol | boolean | | | | plain | | \n workzone | boolean | | | | plain | | \n state | text | | | | extended | | \n vehicletype | text | | | | extended | | \n year | smallint | | | | plain | | \n make | text | | | | extended | | \n model | text | | | | extended | | \n color | text | | | | extended | | \n violation_type | text | | | | extended | | \n charge | text | | | | extended | | \n article | text | | | | extended | | \n contributed_to_accident | boolean | | | | plain | | \n race | text | | | | extended | | \n gender | text | | | | extended | | \n driver_city | text | | | | extended | | \n driver_state | text | | | | extended | | \n dl_state | text | | | | extended | | \n arrest_type | text | | | | extended | | \n geolocation | point | | | | plain | | \n council_districts | smallint | | | | plain | | \n councils | smallint | | | | plain | | \n communities | smallint | | | | plain | | \n zip_codes | smallint | | | | plain | | \n municipalities | smallint | | | | plain | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('Citation')\nPartition constraint: ((violation_type IS NOT NULL) AND (violation_type = 'Citation'::text))\nIndexes:\n \"i2\" btree (make)\n \"traffic_violations_p_list_citation_model_idx\" btree (model)\nCheck constraints:\n \"chk_make\" CHECK (length(seqid) > 1)\nAccess method: heap\n<\/pre>\nFor a specific partition only:<\/p>\n
\npostgres=# alter table traffic_violations_p_list_citation add constraint chk_state check (state is not null);\nALTER TABLE\npostgres=# d traffic_violations_p_list_citation\n Table \"public.traffic_violations_p_list_citation\"\n Column | Type | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid | text | | | \n date_of_stop | date | | | \n time_of_stop | time without time zone | | | \n agency | text | | | \n subagency | text | | | \n description | text | | | \n location | text | | | \n latitude | numeric | | | \n longitude | numeric | | | \n accident | text | | | \n belts | boolean | | | \n personal_injury | boolean | | | \n property_damage | boolean | | | \n fatal | boolean | | | \n commercial_license | boolean | | | \n hazmat | boolean | | | \n commercial_vehicle | boolean | | | \n alcohol | boolean | | | \n workzone | boolean | | | \n state | text | | | \n vehicletype | text | | | \n year | smallint | | | \n make | text | | | \n model | text | | | \n color | text | | | \n violation_type | text | | | \n charge | text | | | \n article | text | | | \n contributed_to_accident | boolean | | | \n race | text | | | \n gender | text | | | \n driver_city | text | | | \n driver_state | text | | | \n dl_state | text | | | \n arrest_type | text | | | \n geolocation | point | | | \n council_districts | smallint | | | \n councils | smallint | | | \n communities | smallint | | | \n zip_codes | smallint | | | \n municipalities | smallint | | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('Citation')\nIndexes:\n \"i2\" btree (make)\n \"traffic_violations_p_list_citation_model_idx\" btree (model)\nCheck constraints:\n \"chk_make\" CHECK (length(seqid) > 1)\n \"chk_state\" CHECK (state IS NOT NULL)\n<\/pre>\nChanging the properties of a column works the same way: Either on the partitioned table level or for a specific partition only:<\/p>\n
\npostgres=# alter table traffic_violations_p_list alter column seqid set not null;\nALTER TABLE\npostgres=# alter table traffic_violations_p_list_citation alter column time_of_stop set not null;\nALTER TABLE\npostgres=# d+ traffic_violations_p_list\n Partitioned table \"public.traffic_violations_p_list\"\n Column | Type | Collation | Nullable | Default | Storage | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid | text | | not null | | extended | | \n date_of_stop | date | | | | plain | | \n time_of_stop | time without time zone | | | | plain | | \n agency | text | | | | extended | | \n subagency | text | | | | extended | | \n description | text | | | | extended | | \n location | text | | | | extended | | \n latitude | numeric | | | | main | | \n longitude | numeric | | | | main | | \n accident | text | | | | extended | | \n belts | boolean | | | | plain | | \n personal_injury | boolean | | | | plain | | \n property_damage | boolean | | | | plain | | \n fatal | boolean | | | | plain | | \n commercial_license | boolean | | | | plain | | \n hazmat | boolean | | | | plain | | \n commercial_vehicle | boolean | | | | plain | | \n alcohol | boolean | | | | plain | | \n workzone | boolean | | | | plain | | \n state | text | | | | extended | | \n vehicletype | text | | | | extended | | \n year | smallint | | | | plain | | \n make | text | | | | extended | | \n model | text | | | | extended | | \n color | text | | | | extended | | \n violation_type | text | | | | extended | | \n charge | text | | | | extended | | \n article | text | | | | extended | | \n contributed_to_accident | boolean | | | | plain | | \n race | text | | | | extended | | \n gender | text | | | | extended | | \n driver_city | text | | | | extended | | \n driver_state | text | | | | extended | | \n dl_state | text | | | | extended | | \n arrest_type | text | | | | extended | | \n geolocation | point | | | | plain | | \n council_districts | smallint | | | | plain | | \n councils | smallint | | | | plain | | \n communities | smallint | | | | plain | | \n zip_codes | smallint | | | | plain | | \n municipalities | smallint | | | | plain | | \nPartition key: LIST (violation_type)\nIndexes:\n \"i1\" btree (model)\nCheck constraints:\n \"chk_make\" CHECK (length(seqid) > 1)\nPartitions: traffic_violations_p_list_citation FOR VALUES IN ('Citation'),\n traffic_violations_p_list_esero FOR VALUES IN ('ESERO'),\n traffic_violations_p_list_sero FOR VALUES IN ('SERO'),\n traffic_violations_p_list_warning FOR VALUES IN ('Warning'),\n traffic_violations_p_list_default DEFAULT\n\npostgres=# d traffic_violations_p_list_citation\n Table \"public.traffic_violations_p_list_citation\"\n Column | Type | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid | text | | not null | \n date_of_stop | date | | | \n time_of_stop | time without time zone | | not null | \n agency | text | | | \n subagency | text | | | \n description | text | | | \n location | text | | | \n latitude | numeric | | | \n longitude | numeric | | | \n accident | text | | | \n belts | boolean | | | \n personal_injury | boolean | | | \n property_damage | boolean | | | \n fatal | boolean | | | \n commercial_license | boolean | | | \n hazmat | boolean | | | \n commercial_vehicle | boolean | | | \n alcohol | boolean | | | \n workzone | boolean | | | \n state | text | | | \n vehicletype | text | | | \n year | smallint | | | \n make | text | | | \n model | text | | | \n color | text | | | \n violation_type | text | | | \n charge | text | | | \n article | text | | | \n contributed_to_accident | boolean | | | \n race | text | | | \n gender | text | | | \n driver_city | text | | | \n driver_state | text | | | \n dl_state | text | | | \n arrest_type | text | | | \n geolocation | point | | | \n council_districts | smallint | | | \n councils | smallint | | | \n communities | smallint | | | \n zip_codes | smallint | | | \n municipalities | smallint | | | \nPartition of: traffic_violations_p_list FOR VALUES IN ('Citation')\nIndexes:\n \"i2\" btree (make)\n \"traffic_violations_p_list_citation_model_idx\" btree (model)\nCheck constraints:\n \"chk_make\" CHECK (length(seqid) > 1)\n \"chk_state\" CHECK (state IS NOT NULL)\n<\/pre>\nThis was indexing and constraints with partitioned tables. In the next post we will have a look sub partitioning.<\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday we talked about attaching and detaching of partitions. Today we will look at indexing and constraints when it comes to partitioned tables. If you missed the last posts, again, here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning PostgreSQL partitioning (4) : Hash […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12486","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\n
PostgreSQL partitioning (7): Indexing and constraints - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL partitioning (7): Indexing and constraints\" \/>\n<meta property=\"og:description\" content=\"Yesterday we talked about attaching and detaching of partitions. Today we will look at indexing and constraints when it comes to partitioned tables. If you missed the last posts, again, here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning PostgreSQL partitioning (4) : Hash […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-07T04:00:12+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL partitioning (7): Indexing and constraints\",\"datePublished\":\"2019-06-07T04:00:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\"},\"wordCount\":472,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\",\"name\":\"PostgreSQL partitioning (7): Indexing and constraints - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-06-07T04:00:12+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL partitioning (7): Indexing and constraints\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL partitioning (7): Indexing and constraints - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL partitioning (7): Indexing and constraints","og_description":"Yesterday we talked about attaching and detaching of partitions. Today we will look at indexing and constraints when it comes to partitioned tables. If you missed the last posts, again, here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning PostgreSQL partitioning (4) : Hash […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/","og_site_name":"dbi Blog","article_published_time":"2019-06-07T04:00:12+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL partitioning (7): Indexing and constraints","datePublished":"2019-06-07T04:00:12+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/"},"wordCount":472,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/","name":"PostgreSQL partitioning (7): Indexing and constraints - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-07T04:00:12+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-7-indexing-and-constraints\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL partitioning (7): Indexing and constraints"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12486","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12486"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12486\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12486"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}