Use of PostgreSQL in Municipal Government

decubitisannouncerΔιαχείριση Δεδομένων

27 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

178 εμφανίσεις

Use of PostgreSQL
in Municipal Government
City of Garden Grove, CA
City of Garden Grove
Founded 1874, incorporated 1956.
Population 174,715 (2009)

City of Garden Grove
Direct Services

Police

Fire

Public Works

Water and Sewer

Storm Drainage

Engineering

Street Maintenance

Parks and Trees

Environmental Compliance

Planning and Permiting

Redevelopment

Housing Authority

Recreation
2010/2011 Budget

$87.8 million general

$209 million overall
City of Garden Grove
~900 users
450 desktops
70 mobile computers

Linux primary server OS since 1995
Began using PostgreSQL 7.1 in 2001
Information Technology Budget: 2.1 million
City Server OS History

Use of computing for finance date back to 1960s

Pick - database / application environment

1977 Microdata Reality : 24 users

1984 Honeywell/Ultimate : 100 serial devices.

1990 Data General Quad Processor Mini

$400,000 (1990 dollars)

~ 400 users, dumb terminals

DGUX/Advanced Pick

1994 x86/SCO

Ethernet and TPC/IP

1995 Linux
How PostgreSQL fits in
City database instances in 2000




Brand
Servers
Applications
Pick
2
Accounting, Budget, Payroll, Business License, Building
Permits, Utilities , HR, Fire Permits, Police Records,
Housing Authority, Public Works, Code Enforcement,
Substandard Housing, Land Use, Street Lighting, Trees,
Warehouse Inventory and more.
Oracle
2
Computer aided dispatch (CAD)
How PostgreSQL fits in
City database instances in 2011




Brand
Servers
Applications
Pick
1
Accounting, Budget, Payroll, Business License
Oracle
3
computer aided dispatch (CAD), computer aided drawing (CAD)
utility network
MS-SQL
3
Jail management, property and evidence, public meeting video
and minutes
MySQL
2
OpenAudit, Zimbra
SQLite
1
Wikis
FileMaker-Pro
2
Housing authority, In-car video library
4D
1
Fire inspection, hazardous waste tracking, training
PostgreSQL
5
Police Records Management, Water Billing, Recreation, Code
Enforcement, Public Works Work-orders, Environmental
Compliance, Utility Maintenance, Time-sheets, Cashiering,
Graffiti Tracking, In-car video (2), RT, Drupal, Spam, Passports,
Building Permits, Engineering Permits, Scalix, GIS, Location
Management/Land Use, Risk Management, Municiple Code,
Snort, Document Archiving, and more.
Why PostgreSQL?
City had been using "multi-
value" Pick database since
1970s.

In 2001...
MySQL didn't have sub-
selects.
DB2 was complicated and
didn't have TCL or PHP
clients.
Oracle was too laborious.
PostgreSQL

Free

Fast

Easy to install

Easy to backup

Worked with many
languages

Good Documentation

Reliable
Why PostgreSQL?
In 2001 PostgreSQL was missing a few features:

ALTER TABLE .. DROP COLUMN -- added in 7.3, 2002

SCHEMAS -- added in 7.3

ALTER COLUMN TYPE -- added in 8.0, 2005

How PostgreSQL fits in

Commercial Applications - Postgres in the wild

CanIT-Pro
: Spam filter
Scalix
: Echange replacement (being replaced)
PermitCity
: Building permitting and inspection system
L3
Communication's in-car video system
MapGuide Enterprise
: Web based GIS server



How PostgreSQL fits in
Open Source Applications - Postgres as a choice

Drupal
: External websites

some effort required to select modules to work
Squidguard
: Web logging

used after MySQL index corruption
RT
: Request Tracker

Entity attribute model
Snort
: Intrusion Detection System



How PostgreSQL fits in
Noteworthy in-house PG applications

Document Archiving
: Agreements, Resolutions,
Ordinances, Minutes, Deeds, Building Plans, ect.

PG's full text search
Water
: Water, sewer, billing, accounting, and cross
connection systems

34,000 + services

~ 30 million in billings a year

12 years of transaction data online

extensive use of Pg functions, triggers,
Recreation
: Facility and class booking application

Complex scheduling handled through PG

Written such that other agencies could use


How PostgreSQL fits in
Noteworthy in-house PG applications

Public Works Workorders


used for all aspects of operations

facilities, streets, water distribution, sewer,
environmental compliance, signs, trees, graffiti,
ect.

270,000 workorders

tracks labor and materials

Alerts via SMS
Police Records Management System

events, arrests, people, vehicles, cites, TC,FI

DNA, calls for service, casefiles, ect.
Municiple Code

pagnated and online output



How PostgreSQL fits in
Current DB Setup

PG 9.0.?

dumbo :
pglive

IBM 3650, 2 CPU

PG instance is 40GB

Storage is iSCSI / Equallogic

plain old 1Gb NIC

babar : pgquery

Some virtual machine

Hot slave, streaming replication

We let users run SQL queries here

Good for backups and dumps

GIS Datasource
First uses of PostgreSQL
2001/2002

Homicide case management system
Sewer overflow tracking system
Utility maintenance tracking


TCL / cgi : bad idea #1

In-house web application framework : bad idea #2

Images stored as blobs in PG : bad idea #3

Paginated reporting using LaTex : bad idea #4

Separate PG databases for PD and City : bad idea #5


First uses of PostgreSQL
2003

Evidence, FI, Graffiti Photos

100 gigs of blobs

interesting to backup

worked fine

no referential integrity

Converted to BYTEA

takes more space

still hard to deal with backups

Moved images out of database in 2005

Now 650GB



Ruby on Rails and PostgreSQL

Started using in 2007

Easy to start using

Takes time to master


Ruby on Rails and PostgreSQL

RoR treats DB as a bit bucket (somewhat)

You don't have to

Models can be views or setof

Constraints do not hurt RoR

Do counting, sorting, and math in PG

CPKs

Needs plugin

Can break REST unless you deal with routes

surrender and add SPKs as needed

Be aware of caching

mostly, don't worry

can be an issue on same page render while DB data
is changing

e.g temp table

Use "uncashed do" block to avoid
Ruby on Rails and PostgreSQL
PostGIS: Underground Service Alert