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":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-12486","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\n
PostgreSQL partitioning (7): Indexing and constraints - dbi services | 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 services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-07T04:00:12+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"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,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"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 services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-06-07T04:00:12+00:00\",\"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 services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL partitioning (7): Indexing and constraints - dbi services | 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 services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2019-06-07T04:00:12+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"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,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"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 services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-07T04:00:12+00:00","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 services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}