114 | Chapter 4: Database
As a final touch, we can set things up so that we don’t have to worry about the keys
at all in code. Remember that the original reason for using composite keys was to
allow us to use independent sequences on each database server. First, we create
those sequences in SQL when creating the tables. The way we set this up is DBMS-
specific; the PostgreSQL syntax would be:
CREATE SEQUENCE orders_order_id_seq;
CREATE TABLE orders(
node_id integer not null,
order_id integer not null default nextval('orders_order_id_seq'),
(other attributes)
PRIMARY KEY (node_id, order_id)
);CREATE SEQUENCE line_items_line_item_id_seq;
CREATE TABLE line_items(
node_id integer not null,
line_item_id integer not null default nextval('line_items_line_item_id_seq'),-- FK to orders
order_node_id integer not null,
order_id integer not null,(other attributes)
PRIMARY KEY (node_id, line_item_id)
);When we execute this DDL on all database nodes and enable replication between
them, each node has its own sequence independent of the others. Now we just have
to make sure that each node uses its own node ID. We could either do this in the
database with column defaults (if we can use different DDL for each node) or in
the application with abefore_createcallback (if each application accesses only one
node).
Triggers, Rules, and Stored Procedures
Now we’re in dangerous territory. Let it be known that you should probably have a
good reason to use triggers, rules, or stored procedures for anything terribly compli-
cated. That is not to say that they have no purpose; they can be lifesavers. But they
should be used to address a specific problem or concern, such as the following:
- A complicated process that involves searching through lots of data (such as
OLA Por log analysis) can be much faster if offloaded to the database server. As
always, profiling is key; premature optimization can cost you execution speed,
not just developer time. - Concerns that have little to do with the application logic, such as audit logs, can
usually be safely moved to the database as triggers. - PostgreSQL can use rules to create updateable views. Unfortunately, this is cur-
rently the only way to get updateable views.