PostGIS Related Database parameters

First, the table structure definition

--Table:public.tbl_buildings

--DROP TABLE public.tbl_buildings;

CREATE TABLE public.tbl_buildings

(

ID character (7) not NULL,

Name character (20),

Type character (10),

Address character (30),

The_geom geometry,

CONSTRAINT Tbl_buildings_pkey PRIMARY KEY (ID)

)

Column Changes

SELECT dropgeometrycolumn (' tbl_properties ', ' The_geom ')

SELECT addgeometrycolumn (' tbl_properties ', ' The_geom ', 4326,

' Point ', 2);

Add data

INSERT into Tbl_properties (ID, town, Postal_Code, Street,

"Number", The_geom) VALUES (1, ' London ', ' N7 6PA ', ' Holloway Road ',

St_geomfromewkt (' srid=4326; Point (51.556173-0.116190));

Second, Geometry column establish GIST index

CREATE INDEX idx_geo_buildings on tbl_buildings USING GIST (THE_GEOM);

Third, database parameters

1.checkpoint_segments (9.5 ago)

Default Value:3

Suggested Value:6

-----------------------------------

Max_wal_size (after 9.5)

Default value:1 GB

2.constraint_exclusion

Default value:deactivated

Suggested value:activated, partition

3.maintenance_work_mem

Default value:16

Suggested value:128

4.random_page_cost

Default Value 4.0

Suggested Value 2.0

5.shared_buffers

Default value:32 MB

Suggested value:64 MB to MB unlikely for a allocation of more than-percent

of RAM (Windows), 25% physical Memory (Linux)

6.wal_buffers

Default VALUE:64KB

Suggested VALUE:1MB

7.work_mem

Default VALUE:1MB

Suggested VALUE:16MB

The above are recommended values, the specific needs of the system according to the operation of the changes.

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1870227

PostGIS Related Database parameters