Database Reference
In-Depth Information
Now we will show you some things you need to keep in mind when programming on
the server in PostgreSQL.
Data locality
If at all possible, keep the data on the server. Believe me, it's happier there, and per-
formance is much better when modifying data. If everything was done in the applic-
ation layer, the data would need to be returned from the database, the modifications
made, and then finally sent back to the database for a commit. If you are building a
web-scalable application, this should be your last resort.
Let's walk through a small snippet using two methods of making an update to a single
record:
<?php
$db = pg_connect("host port user password
dbname schema");
$sql = "SELECT * FROM customer WHERE id = 23";
$row = pg_fetch_array($db,$sql);
if ($row['account_balance'] > 6000) {
$sql = "UPDATE customer SET valued_customer =
true;";
pg_query($db,$sql);
}
pg_close($db);
?>
This code snippet pulls a row of data from the database server to the client, makes
an evaluation, and changes a customer account based on the evaluation. The result
of the change is then sent back to the server for processing.
There are several things wrong with this scenario. First, the scalability is terrible.
Imagine if this operation needed to be performed for thousands or even millions of
customers.
The second problem is transactional integrity. What happens if the user's account
balance changes from some other transaction between the query and the update? Is
