DBMS PostgreSQL Integration - Benjamin Arai

manlybluegooseData Management

Nov 27, 2012 (4 years and 8 months ago)


DBMS PostgreSQL Integration
By: Benjamin Arai & Conley Read, Version: 1.0.0

What is PostgreSQL?
PostgreSQL is an enhancement of the POSTGRES database management system, a next-generation DBMS
research prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, it
replaces the PostQuel query language with an extended subset of SQL. PostgreSQL is free and the complete
source is available.
PostgreSQL development is performed by a team of developers who all subscribe to the PostgreSQL
development mailing list. The current coordinator is Marc G. Fournier (scrappy@PostgreSQL.org). (See section
1.6 on how to join). This team is now responsible for all development of PostgreSQL.
The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many others have contributed to the porting,
testing, debugging, and enhancement of the code. The original Postgres code, from which PostgreSQL is
derived, was the effort of many graduate students, undergraduate students, and staff programmers working
under the direction of Professor Michael Stonebraker at the University of California, Berkeley.
The original name of the software at Berkeley was Postgres. When SQL functionality was added in 1995, its
name was changed to Postgres95. The name was changed at the end of 1996 to PostgreSQL.

Data Volume
Due to the large datasets often associated with data mining, it becomes imperative to store data in an efficient
and manageable system. PostgreSQL is a location aware database capable of storing and indexing data up to
1024 dimensions, with storage of point locations limited only by available disk space. PostgreSQL creates basis
framework for software to be built upon by removing the R-Tree development cycle and replacing it with a
optimization and tuning cycle, therefore allowing a greater amount of time to be spent on the optimization of
the data analysis and user interface(s).
Spatial System
PostgreSQL stores data points in a R-Tree data structure, with full indexing and data modification, insertion,
and deletion support. PostgreSQL can analyze powerful questions such as location based searches (nearest
neighbor) and sub-searches (searches based upon linear or spatial indexing of the subset(s)). PostgreSQL
spatial analysis functions are a mature set of tools culminating of both insertion and deletion capabilities with
extensions for re-balancing.
Why not build it our selves?
Building a spatial analysis tool from the ground up is argues task, by allowing the spatial analysis tools to be
outsourced it allows client development to become more focused, which intern creates a better overall solution
for the client. PostgreSQL the smart solution for companies today looking for unique solution that can be used
as framework to create more compelling and productive solutions, it also creates a second basis for support and
maintenance, which lowers overall TCO (Total Cost of Ownership).
What’s left for the developer to do?
Development and handling of analysis on the server side of the application(s) is required in order to take full
advantage of the PostgreSQL system. PostgreSQL offers simplistic tools for creating and modifying spatial
databases but server side trigger(s) programming is still required to create optimal server-side queries/analysis
that takes advantage of the spatial data. From this the client application has to be fully integrated into the
system to both communicate to the PostgreSQL server but more importantly to take advantage of the server side
triggers used in finding optimal spatial solutions.