PostGIS setup & schema design

PostGIS is PostgreSQL with a spatial extension — the open-source backbone for any GIS work that’s outgrown shapefiles or single-user geodatabases. Multi-user concurrent access, real query performance on serious data volumes, and the foundation a Web GIS portal sits on top of.

We deliver PostGIS setup and schema design for telecom planning, utility asset management, and infrastructure projects. This is delivered through our partner Geodars — the team behind this side of LayerSpec is GIS-led with deep PostGIS experience.

The install is the easy part. CREATE EXTENSION postgis; and you have spatial types. The canonical install steps for every platform are documented at postgis.net, and we won’t reproduce them here. What we deliver is everything after that: PostGIS setup as the operational datastore your asset and network data actually lives in — schema, roles, indexing, and a clean connection story for the QGIS and ArcGIS clients your team uses every day.

PostGIS setup as an operational datastore

A generic PostGIS setup gets you a database that can hold geometry. An operational one is designed around the specific job of being the system-of-record for a fibre, utility, or asset network:

Schema built to the network — feature tables that reflect real asset types and their relationships, not a flat dump of whatever shapefiles arrived
Geometry and SRID chosen deliberately — one projected reference system per project where accuracy matters, not a mix inherited from source data
Roles and privileges that match how the team works — read-only analysts, editors scoped to their layers, an admin role, and a service account for the web tier
Indexing designed for the queries the application will actually run, not added later when things are slow
A connection story that works for QGIS, ArcGIS, and GeoServer at once — because in most real teams all three connect to the same database

The rest of this page covers the parts of that work that most often get skipped in a generic install.

Why PostGIS, when

PostGIS is the right choice when:

Multi-user concurrent editing is needed — shapefiles fall over, file geodatabases support multi-user uneasily
Data volumes are non-trivial — millions of features, repeated complex spatial queries, real-time analysis
You need open-source — licensing, governance, or technical preference
Integration with non-GIS systems matters — PostGIS is PostgreSQL, so it integrates cleanly with web stacks, APIs, ETL pipelines
Web GIS portals will sit on top — most modern Web GIS deployments back to PostGIS

It’s not always the right answer — an Esri geodatabase or a single file store can be the better fit. We weigh that explicitly below rather than defaulting to PostGIS.

What’s in a PostGIS deployment

A complete deployment covers:

Server setup — PostgreSQL + PostGIS extension, version aligned with project requirements (the install itself is quick; the canonical steps live at postgis.net)
Schema design — tables, geometry types, spatial reference systems, indexes
Data loading — initial migration from existing sources
Spatial indexing and query optimisation — GIST and B-tree indexes configured for the queries the application will run
Backup and replication — operational continuity, disaster recovery
User and role-based access — who can read, who can write, who can administer
Documentation — schema reference, query patterns, operational runbook

Deployment options

Three common deployment models:

1. Managed cloud — AWS RDS for PostgreSQL with PostGIS, Azure Database for PostgreSQL, Google Cloud SQL. Reduces operational burden, fits most projects.

2. Self-hosted cloud — PostgreSQL + PostGIS on a cloud VM, full control, more operational work.

3. On-premises — for data residency, network isolation, or regulatory constraints.

The right choice depends on data volume, operational team capability, and constraints. We assess at the start.

Schema design — the part that matters

A productive schema design covers:

Geometry types and SRID — picked deliberately for the use case (geographic vs projected, accuracy requirements)
Table design — normalised where appropriate, denormalised where query performance demands
Foreign keys and constraints — actually enforced, not optional
Versioning approach — how data history is preserved
Topology — for utility networks, structural topology that supports tracing and connectivity queries
Standards alignment — fibre/telecom asset schemas, utility data models, project-specific structures

Schema gets reviewed before data is loaded — far cheaper than discovering structural issues with millions of rows in place.

Connecting QGIS and ArcGIS — and the curve trap

In most real teams, the same PostGIS database is read and edited by more than one client. QGIS connects natively and treats PostGIS geometry as a first-class store. GeoServer and web APIs sit straight on top. ArcGIS Pro connects too — either to a plain PostGIS-enabled PostgreSQL database or to one registered as an Esri enterprise geodatabase — and this is where an otherwise clean setup can go wrong.

The curve trap. ArcGIS supports true curves — circular arcs, Bézier and elliptical segments. PostGIS also has curve types, but ArcGIS does not store its curves in the standard PostGIS geometry column that QGIS, GeoServer and other clients read. It writes them into an additional binary column of its own. The practical effect: a feature drawn with curves in ArcGIS looks correct in ArcGIS, but the geometry the rest of your stack sees is only an approximation — or is missing the curve entirely. The point of putting data in native PostGIS is that every client sees the same authoritative geometry, and true curves quietly defeat that.

The usual resolution is to keep curves out of the shared store: densify curved features into straight-segment linestrings or polygons on the way in, so every client reads the same geometry. It is a deliberate trade — a small loss of geometric precision on curved features in exchange for a datastore that round-trips cleanly across QGIS, ArcGIS and the web tier. Where curves genuinely matter, they stay in an ArcGIS-owned layer rather than the shared PostGIS one.

We set the connection strategy up front: which clients are authoritative for editing, whether ArcGIS registers the database as an enterprise geodatabase or connects to plain PostGIS, and how curve geometry is handled so nobody discovers the mismatch after data is loaded.

PostGIS or a file / enterprise geodatabase?

PostGIS is not always the answer, and part of a good setup is deciding honestly whether it is the right store at all.

A file geodatabase (or GeoPackage) is fine when the work is genuinely single-user or a small, coordinated team — a design dataset, an offline field package, a deliverable handed over as a file. No server, no administration. It falls over the moment several people need to edit concurrently or a web service needs a live backend.

An Esri enterprise geodatabase — on SQL Server, Oracle, or PostgreSQL itself — earns its place where the wider Esri stack is the operational reality: ArcGIS Pro editing, Utility Network topology and tracing, branch versioning, and Portal-published services. It brings Esri’s versioning and connectivity model at the cost of Esri licensing and a heavier administrative footprint. Notably, an enterprise geodatabase can run on PostgreSQL/PostGIS — the two are not mutually exclusive.

Plain PostGIS is the right store when you want an open, integration-friendly datastore that any client can reach — QGIS, GeoServer, a web app, an ETL pipeline — without a per-seat licence, and when you don’t need Esri’s built-in versioning or Utility Network model. It carries multi-user concurrency through PostgreSQL’s transactional model; where you need edit history or versioning, that’s designed application-side.

We make this call against the actual constraints — who edits, what clients are in use, whether Utility Network features are required, licence posture, and how much administration the operational team can carry. See also multi-user geodatabase design where the choice is between enterprise geodatabase and PostGIS for a whole team.

Indexing and performance for large layers

Spatial performance is where a PostGIS setup is proven or exposed. Two levers do most of the work:

GIST indexes on geometry columns — the spatial index PostGIS relies on for bounding-box and intersection queries. Without one, spatial queries table-scan; with one they use the index. On a network layer this is not optional.
```
CREATE INDEX idx_cables_geom ON cables USING GIST (geom);
```
B-tree indexes on the attributes you filter and join on — status, asset type, feeder or route identifiers, foreign keys. Spatial queries almost always also filter on attributes, and a missing B-tree index is a common cause of a query that’s fast on the map extent but slow across the dataset.

After indexing, ANALYZE the tables so the planner has current statistics, and check real query plans with EXPLAIN ANALYZE rather than guessing. On large layers — millions of features — this is the difference between an interactive map and one that stalls.

A caveat worth stating plainly. When PostGIS is fronted by ArcGIS feature services, teams sometimes see a large layer stay slow even after GIST and B-tree indexes are in place. In practitioner accounts, the bottleneck here is the feature-service throughput layer rather than the database — the query returns quickly, but the service serialising and delivering it is the constraint. A slow map is not automatically a slow database, and further database tuning won’t fix a service-tier limit. We benchmark the database and the service path separately so the actual bottleneck gets addressed.

Inputs we need

A PostGIS engagement runs on:

Use case — what’s the database for, what queries will run against it
Data volume — feature counts, expected growth rate
Source data — what data is being migrated in, in what formats
Operational requirements — uptime, recovery, performance SLAs
Integration points — what systems need to read or write to the database
Security requirements — access control, encryption, audit

Common pitfalls in outsourced PostGIS work

Schema designed without query patterns. A schema optimised for storage that’s poor for retrieval produces an unusable database. We design with query patterns in mind.

No versioning thought. Data without history is fine until you need to know what changed last week. We design versioning into the schema even where it’s not the headline requirement.

Backup as an afterthought. A database without tested backup is a database that loses data. We deliver backup with documented restore procedure that’s actually been tested.

Typical timelines

Single-purpose deployment (e.g. asset records for a defined network) — 2-4 weeks
Multi-purpose deployment with several schemas, integrations — 4-8 weeks
Enterprise deployment with high availability, replication, complex integrations — 8-16 weeks

Migration from existing sources runs alongside or after — see GIS data migration & cleansing.

How we deliver

Geospatial work runs through our partner Geodars, who lead PostGIS, ArcGIS, and broader GIS engineering. The team has direct experience deploying PostGIS for utility, telecom, and infrastructure projects.

Talk to us about a PostGIS deployment

Tell us the use case, expected data volume, and operational context. We’ll scope and price for your project.

Related reading: PostGIS Setup for Network & Infrastructure Data: Beyond CREATE EXTENSION — how we stand PostGIS up as an operational datastore, the ArcGIS curve trap, and the PostGIS-vs-geodatabase decision.