Data Independence: The Foundation of Database Flexibility

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

-- ==============================================================
-- PATTERN 1: Consumer-Oriented Design
-- ==============================================================
 
-- BAD: View mirrors table structure (no abstraction value)
CREATE VIEW CustomerView_Bad AS
SELECT * FROM Customer;  -- Leaks all internal details
 
-- GOOD: View designed for specific consumer need (billing system)
CREATE VIEW BillingCustomerView AS
SELECT 
    customer_id,
    CONCAT(first_name, ' ', last_name) AS customer_name,  -- Combined for display
    COALESCE(company_name, 'Individual') AS billing_entity,  -- Business logic
    billing_address_line1 AS address1,  -- Stable names regardless of source
    billing_address_line2 AS address2,
    billing_city AS city,
    billing_state AS state,
    billing_postal_code AS postal_code,
    COALESCE(tax_exempt, FALSE) AS is_tax_exempt,  -- Default handling
    payment_terms_days AS net_days
FROM Customer c
LEFT JOIN CustomerBillingProfile bp ON c.customer_id = bp.customer_id
WHERE c.status = 'active';  -- Built-in filtering
 
 
-- ==============================================================
-- PATTERN 2: Explicit Column Contracts
-- ==============================================================
 
-- BAD: Implicit column dependency
CREATE VIEW OrderSummary_Bad AS
SELECT * FROM Orders;  -- If Orders adds a column, view changes
 
-- GOOD: Explicit column contract
CREATE VIEW OrderSummary AS
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    status
FROM Orders;
-- Adding columns to Orders table doesn't affect this view
 
 
-- ==============================================================
-- PATTERN 3: Abstraction Layers for Flexibility
-- ==============================================================
 
-- Create a stabilization layer for critical tables
-- This allows internal restructuring without external impact
 
-- Internal tables (can be refactored):
-- Customer_Base(customer_id, first_name, last_name, email, ...)
-- Customer_Contact(customer_id, phone_type, phone_number, ...)
-- Customer_Address(customer_id, address_type, line1, line2, city, ...)
 
-- External view (stable interface):
CREATE VIEW Customer AS
SELECT 
    cb.customer_id,
    cb.first_name,
    cb.last_name,
    cb.email,
    (SELECT phone_number FROM Customer_Contact 
     WHERE customer_id = cb.customer_id AND phone_type = 'primary' LIMIT 1) AS phone,
    ca.line1 AS address_line1,
    ca.line2 AS address_line2,
    ca.city,
    ca.state,
    ca.postal_code
FROM Customer_Base cb
LEFT JOIN Customer_Address ca 
    ON cb.customer_id = ca.customer_id AND ca.address_type = 'primary';
 
-- Applications query "Customer" (the view)
-- DBA can restructure underlying tables without application awareness

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193

-- ==============================================================
-- SCENARIO: Unified Customer View Over Split Tables
-- ==============================================================
 
-- Internal normalized structure:
CREATE TABLE CustomerCore (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE CustomerContact (
    contact_id INT PRIMARY KEY,
    customer_id INT REFERENCES CustomerCore(customer_id),
    contact_type VARCHAR(20),  -- 'phone', 'mobile', 'fax'
    contact_value VARCHAR(50),
    is_primary BOOLEAN DEFAULT FALSE
);
 
CREATE TABLE CustomerAddress (
    address_id INT PRIMARY KEY,
    customer_id INT REFERENCES CustomerCore(customer_id),
    address_type VARCHAR(20),  -- 'billing', 'shipping', 'home'
    street VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    is_primary BOOLEAN DEFAULT FALSE
);
 
-- External view (legacy application expects flat structure):
CREATE VIEW Customer AS
SELECT 
    cc.customer_id,
    cc.first_name,
    cc.last_name,
    cc.email,
    con.contact_value AS phone,
    ca.street AS address,
    ca.city,
    ca.state,
    ca.postal_code
FROM CustomerCore cc
LEFT JOIN CustomerContact con 
    ON cc.customer_id = con.customer_id 
    AND con.contact_type = 'phone' 
    AND con.is_primary = TRUE
LEFT JOIN CustomerAddress ca 
    ON cc.customer_id = ca.customer_id 
    AND ca.is_primary = TRUE;
 
 
-- ==============================================================
-- INSTEAD OF INSERT TRIGGER
-- ==============================================================
 
CREATE OR REPLACE FUNCTION customer_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
    new_customer_id INT;
BEGIN
    -- Insert core customer data
    INSERT INTO CustomerCore (first_name, last_name, email)
    VALUES (NEW.first_name, NEW.last_name, NEW.email)
    RETURNING customer_id INTO new_customer_id;
    
    -- Insert phone if provided
    IF NEW.phone IS NOT NULL THEN
        INSERT INTO CustomerContact (customer_id, contact_type, contact_value, is_primary)
        VALUES (new_customer_id, 'phone', NEW.phone, TRUE);
    END IF;
    
    -- Insert address if provided
    IF NEW.address IS NOT NULL THEN
        INSERT INTO CustomerAddress (
            customer_id, address_type, street, city, state, postal_code, is_primary
        )
        VALUES (
            new_customer_id, 'primary', NEW.address, NEW.city, NEW.state, NEW.postal_code, TRUE
        );
    END IF;
    
    -- Return the new row for the view
    NEW.customer_id := new_customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_customer_insert
INSTEAD OF INSERT ON Customer
FOR EACH ROW EXECUTE FUNCTION customer_insert_trigger();
 
 
-- ==============================================================
-- INSTEAD OF UPDATE TRIGGER
-- ==============================================================
 
CREATE OR REPLACE FUNCTION customer_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Update core customer data
    UPDATE CustomerCore 
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        email = NEW.email
    WHERE customer_id = OLD.customer_id;
    
    -- Update or insert phone
    IF NEW.phone IS DISTINCT FROM OLD.phone THEN
        -- Try to update existing primary phone
        UPDATE CustomerContact 
        SET contact_value = NEW.phone
        WHERE customer_id = OLD.customer_id 
          AND contact_type = 'phone' 
          AND is_primary = TRUE;
        
        -- If no rows updated, insert new phone
        IF NOT FOUND AND NEW.phone IS NOT NULL THEN
            INSERT INTO CustomerContact (customer_id, contact_type, contact_value, is_primary)
            VALUES (OLD.customer_id, 'phone', NEW.phone, TRUE);
        END IF;
    END IF;
    
    -- Update or insert address
    IF NEW.address IS DISTINCT FROM OLD.address 
       OR NEW.city IS DISTINCT FROM OLD.city
       OR NEW.state IS DISTINCT FROM OLD.state
       OR NEW.postal_code IS DISTINCT FROM OLD.postal_code THEN
        
        UPDATE CustomerAddress 
        SET street = NEW.address,
            city = NEW.city,
            state = NEW.state,
            postal_code = NEW.postal_code
        WHERE customer_id = OLD.customer_id AND is_primary = TRUE;
        
        IF NOT FOUND AND NEW.address IS NOT NULL THEN
            INSERT INTO CustomerAddress (
                customer_id, address_type, street, city, state, postal_code, is_primary
            )
            VALUES (
                OLD.customer_id, 'primary', NEW.address, NEW.city, NEW.state, NEW.postal_code, TRUE
            );
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_customer_update
INSTEAD OF UPDATE ON Customer
FOR EACH ROW EXECUTE FUNCTION customer_update_trigger();
 
 
-- ==============================================================
-- INSTEAD OF DELETE TRIGGER
-- ==============================================================
 
CREATE OR REPLACE FUNCTION customer_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Cascade delete through all related tables
    DELETE FROM CustomerContact WHERE customer_id = OLD.customer_id;
    DELETE FROM CustomerAddress WHERE customer_id = OLD.customer_id;
    DELETE FROM CustomerCore WHERE customer_id = OLD.customer_id;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_customer_delete
INSTEAD OF DELETE ON Customer
FOR EACH ROW EXECUTE FUNCTION customer_delete_trigger();
 
 
-- ==============================================================
-- USAGE: Application code unchanged!
-- ==============================================================
 
-- Legacy application continues using its familiar interface:
INSERT INTO Customer (first_name, last_name, email, phone, address, city, state, postal_code)
VALUES ('John', 'Doe', 'john@example.com', '555-1234', '123 Main St', 'Anytown', 'CA', '90210');
 
UPDATE Customer 
SET phone = '555-5678', city = 'Newtown'
WHERE customer_id = 1;
 
DELETE FROM Customer WHERE customer_id = 1;
 
-- All operations work through the view, routing to normalized tables behind the scenes!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

┌─────────────────────────────────────────────────────────────────────────────┐
│                    SCHEMA EVOLUTION LIFECYCLE                                │
└─────────────────────────────────────────────────────────────────────────────┘
 
PHASE 1: ANALYSIS
├── Identify the required schema change
├── Catalog all dependent views
├── Catalog all dependent applications (via view usage)
├── Assess impact on each dependency
└── Determine if change is additive, breaking, or transparent
 
PHASE 2: DESIGN
├── Design new schema structure
├── Design view modifications to maintain interfaces
├── If interface must change: design versioned views (V2)
├── Design migration path for data
└── Design rollback procedures
 
PHASE 3: IMPLEMENTATION (Expand Phase)
├── Create new tables/columns alongside existing
├── Implement data sync / dual-write if needed
├── Create or modify views to use new structures
├── Test view behavior with new structures
└── Deploy new views (external interface unchanged!)
 
PHASE 4: MIGRATION (Contract Phase)
├── Migrate data to new structures
├── Validate data integrity
├── Switch views to new underlying structure
├── Monitor for issues
└── Deprecate old structures (if applicable)
 
PHASE 5: CLEANUP
├── Remove synchronization / dual-write
├── Drop deprecated objects after confirmation period
├── Update documentation
└── Close the change cycle

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

-- ==============================================================
-- EXAMPLE: Splitting a Table (Expand-Contract Pattern)
-- ==============================================================
 
-- STARTING STATE: Monolithic Product table
CREATE TABLE Product_Original (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10,2),
    cost DECIMAL(10,2),  -- Moving to separate table
    supplier_id INT,     -- Moving to separate table
    category VARCHAR(50),
    weight_kg DECIMAL(5,2),
    dimensions VARCHAR(50),
    created_at TIMESTAMP
);
 
-- STARTING VIEW: Applications use this
CREATE VIEW Product AS SELECT * FROM Product_Original;
 
 
-- ==============================================================
-- PHASE 1: EXPAND - Create new structures alongside old
-- ==============================================================
 
-- New table for supplier/cost data
CREATE TABLE ProductSupplier (
    product_id INT PRIMARY KEY,
    supplier_id INT,
    cost DECIMAL(10,2),
    last_cost_update TIMESTAMP,
    CONSTRAINT fk_product FOREIGN KEY (product_id) 
        REFERENCES Product_Original(product_id)
);
 
-- New table for core product data (will replace original)
CREATE TABLE ProductCore (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10,2),
    category VARCHAR(50),
    weight_kg DECIMAL(5,2),
    dimensions VARCHAR(50),
    created_at TIMESTAMP
);
 
 
-- ==============================================================
-- PHASE 2: DUAL-WRITE - Populate new structures
-- ==============================================================
 
-- Initial data migration
INSERT INTO ProductCore (product_id, name, description, price, category, weight_kg, dimensions, created_at)
SELECT product_id, name, description, price, category, weight_kg, dimensions, created_at
FROM Product_Original;
 
INSERT INTO ProductSupplier (product_id, supplier_id, cost, last_cost_update)
SELECT product_id, supplier_id, cost, NOW()
FROM Product_Original;
 
-- Create triggers for dual-write (new inserts go to both structures)
CREATE OR REPLACE FUNCTION sync_product_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO ProductCore VALUES (
        NEW.product_id, NEW.name, NEW.description, NEW.price, 
        NEW.category, NEW.weight_kg, NEW.dimensions, NEW.created_at
    );
    INSERT INTO ProductSupplier VALUES (
        NEW.product_id, NEW.supplier_id, NEW.cost, NOW()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_insert
AFTER INSERT ON Product_Original
FOR EACH ROW EXECUTE FUNCTION sync_product_insert();
 
 
-- ==============================================================
-- PHASE 3: SWITCH - Redirect view to new structures
-- ==============================================================
 
-- Update view to use new tables (external interface unchanged!)
CREATE OR REPLACE VIEW Product AS
SELECT 
    pc.product_id,
    pc.name,
    pc.description,
    pc.price,
    ps.cost,           -- Now from ProductSupplier
    ps.supplier_id,    -- Now from ProductSupplier
    pc.category,
    pc.weight_kg,
    pc.dimensions,
    pc.created_at
FROM ProductCore pc
LEFT JOIN ProductSupplier ps ON pc.product_id = ps.product_id;
 
-- Add INSTEAD OF triggers for the new structure (similar to previous examples)
-- ... (trigger implementations)
 
 
-- ==============================================================
-- PHASE 4: CONTRACT - Remove old structures after verification
-- ==============================================================
 
-- After verification period (e.g., 2 weeks of production use):
DROP TRIGGER trg_sync_insert ON Product_Original;
DROP FUNCTION sync_product_insert();
DROP TABLE Product_Original;
 
-- RESULT: Table split complete
-- Applications never changed—they still query the "Product" view
-- View now serves from normalized ProductCore + ProductSupplier

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

-- ==============================================================
-- ANTI-PATTERN: Index Hints (Breaks Physical Independence)
-- ==============================================================
 
-- BAD: Hard-coded index hint
SELECT /*+ INDEX(orders idx_orders_customer) */ 
    order_id, total
FROM orders 
WHERE customer_id = 123;
 
-- If DBA renames or drops idx_orders_customer, this query fails or regresses!
 
 
-- GOOD: Let optimizer decide
SELECT order_id, total
FROM orders 
WHERE customer_id = 123;
 
-- Optimizer will use the best available index automatically
 
 
-- ==============================================================
-- ANTI-PATTERN: Column Ordinal Dependencies
-- ==============================================================
 
-- BAD: Relies on column position (some programming languages allow this)
-- Code that does: row[0], row[1], row[2] instead of row["order_id"], etc.
 
-- GOOD: Always use named columns
SELECT order_id, customer_id, total FROM orders;
-- And access by name in application code
 
 
-- ==============================================================
-- PATTERN: Tablespace-Based Physical Management
-- ==============================================================
 
-- Create tablespaces for different storage tiers
CREATE TABLESPACE fast_storage LOCATION '/mnt/nvme/pg_data';
CREATE TABLESPACE archive_storage LOCATION '/mnt/hdd/pg_archive';
 
-- Move hot data to fast storage
ALTER TABLE active_orders SET TABLESPACE fast_storage;
 
-- Move cold data to archive storage  
ALTER TABLE historical_orders SET TABLESPACE archive_storage;
 
-- Application queries unchanged—tablespace is internal detail
 
 
-- ==============================================================
-- PATTERN: Online Index Operations
-- ==============================================================
 
-- PostgreSQL: CONCURRENTLY avoids blocking
CREATE INDEX CONCURRENTLY idx_orders_date ON orders(order_date);
 
-- SQL Server: ONLINE = ON maintains availability
CREATE NONCLUSTERED INDEX idx_orders_date ON orders(order_date)
WITH (ONLINE = ON);
 
-- Oracle: ONLINE keyword
CREATE INDEX idx_orders_date ON orders(order_date) ONLINE;
 
 
-- ==============================================================
-- PATTERN: Statistics Maintenance
-- ==============================================================
 
-- PostgreSQL: Regular analyze
ANALYZE orders;
ANALYZE VERBOSE customers;  -- With output
 
-- Automated via cron or pg_cron:
-- 0 2 * * * psql -c "ANALYZE;" mydb
 
-- SQL Server: Update statistics
UPDATE STATISTICS orders;
UPDATE STATISTICS customers WITH FULLSCAN;  -- More accurate
 
-- Oracle: Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS('MYSCHEMA', 'ORDERS');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

-- ==============================================================
-- LEGACY RETROFITTING: Shadow View Approach
-- ==============================================================
 
-- SCENARIO: Legacy "CUSTOMER" table accessed directly by 20+ applications
-- GOAL: Introduce abstraction layer without changing applications
 
-- STEP 1: Create new schema for table ownership
CREATE SCHEMA data_internal;
 
-- STEP 2: Move original table to internal schema
ALTER TABLE public.CUSTOMER SET SCHEMA data_internal;
-- Table is now data_internal.CUSTOMER
 
-- STEP 3: Create view with original name in original location
CREATE VIEW public.CUSTOMER AS 
SELECT 
    CUSTOMER_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    PHONE,
    ADDRESS_1,
    ADDRESS_2,
    CITY,
    STATE,
    ZIP,
    CREATED_DATE,
    MODIFIED_DATE
FROM data_internal.CUSTOMER;
 
-- STEP 4: Grant permissions on view (match original table permissions)
GRANT SELECT, INSERT, UPDATE, DELETE ON public.CUSTOMER TO app_role;
 
-- STEP 5: Add INSTEAD OF triggers for write operations
-- (See previous examples for full trigger implementations)
 
-- RESULT:
-- - Applications continue using "CUSTOMER" (now a view)
-- - All access goes through the view layer
-- - Table is now in data_internal schema (hidden from apps)
-- - Future schema changes modify view definition, not applications
 
 
-- ==============================================================
-- STEP 6 (FUTURE): Now we can safely restructure
-- ==============================================================
 
-- Example: Split address into separate table
CREATE TABLE data_internal.CUSTOMER_ADDRESS (
    ADDRESS_ID INT PRIMARY KEY,
    CUSTOMER_ID INT REFERENCES data_internal.CUSTOMER(CUSTOMER_ID),
    ADDRESS_TYPE VARCHAR(20),
    LINE_1 VARCHAR(100),
    LINE_2 VARCHAR(100),
    CITY VARCHAR(50),
    STATE VARCHAR(2),
    ZIP VARCHAR(10)
);
 
-- Migrate data...
 
-- Update view to join tables (applications unchanged!)
CREATE OR REPLACE VIEW public.CUSTOMER AS
SELECT 
    c.CUSTOMER_ID,
    c.FIRST_NAME,
    c.LAST_NAME,
    c.EMAIL,
    c.PHONE,
    a.LINE_1 AS ADDRESS_1,
    a.LINE_2 AS ADDRESS_2,
    a.CITY,
    a.STATE,
    a.ZIP,
    c.CREATED_DATE,
    c.MODIFIED_DATE
FROM data_internal.CUSTOMER c
LEFT JOIN data_internal.CUSTOMER_ADDRESS a 
    ON c.CUSTOMER_ID = a.CUSTOMER_ID AND a.ADDRESS_TYPE = 'primary';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- ==============================================================
-- MATERIALIZED VIEWS: When Regular Views Are Too Slow
-- ==============================================================
 
-- Scenario: Complex reporting view with multiple joins and aggregations
-- Regular view is too slow for dashboard queries
 
-- Regular view (computed every query):
CREATE VIEW OrderSummaryByRegion AS
SELECT 
    r.region_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.total_amount) AS total_revenue,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN regions r ON c.region_id = r.region_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY r.region_name;
 
-- Materialized view (computed once, stored physically):
CREATE MATERIALIZED VIEW OrderSummaryByRegion_MV AS
SELECT 
    r.region_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.total_amount) AS total_revenue,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN regions r ON c.region_id = r.region_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY r.region_name;
 
-- Create index on materialized view for fast access
CREATE INDEX idx_order_summary_region ON OrderSummaryByRegion_MV(region_name);
 
-- Refresh periodically (manual or scheduled)
REFRESH MATERIALIZED VIEW OrderSummaryByRegion_MV;
 
-- Automatic refresh via pg_cron (PostgreSQL example):
-- SELECT cron.schedule('0 */1 * * *', 'REFRESH MATERIALIZED VIEW OrderSummaryByRegion_MV');
 
-- TRADE-OFF:
-- + Query performance: milliseconds instead of seconds
-- + Still provides logical independence (underlying tables can change)
-- - Data freshness: stale between refreshes
-- - Storage: requires disk space for results
-- - Refresh cost: periodic overhead

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

-- ==============================================================
-- MONITORING: Track Direct Table Access vs View Access
-- ==============================================================
 
-- PostgreSQL: Using pg_stat_statements to identify access patterns
 
-- First, ensure pg_stat_statements is enabled
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
 
-- Query to find direct table access (excluding view access):
SELECT 
    query,
    calls,
    mean_time,
    rows
FROM pg_stat_statements
WHERE query ILIKE '%FROM data_internal.%'  -- Internal schema = direct access
  AND query NOT ILIKE '%pg_catalog%'  -- Exclude system queries
ORDER BY calls DESC
LIMIT 20;
 
-- Alert on any results—these are abstraction violations!
 
 
-- ==============================================================
-- DEPENDENCY TRACKING: View to Application Mapping
-- ==============================================================
 
-- Create a dependency tracking table
CREATE TABLE view_dependencies (
    view_name VARCHAR(100),
    dependent_app VARCHAR(100),
    access_type VARCHAR(20),  -- 'read', 'write', 'both'
    last_access TIMESTAMP,
    PRIMARY KEY (view_name, dependent_app)
);
 
-- Populate via query logging analysis:
-- Parse query logs, extract application identifier (from connection comments, app_name, etc.)
-- Insert into view_dependencies table
 
-- Query to assess impact of view changes:
SELECT 
    dependent_app,
    access_type,
    last_access
FROM view_dependencies
WHERE view_name = 'Customer'  -- View we're planning to modify
ORDER BY last_access DESC;
 
 
-- ==============================================================
-- VIEW VERSION HISTORY
-- ==============================================================
 
CREATE TABLE view_version_history (
    version_id SERIAL PRIMARY KEY,
    view_name VARCHAR(100),
    view_definition TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(100),
    change_reason TEXT
);
 
-- Trigger to capture view changes:
CREATE OR REPLACE FUNCTION log_view_change()
RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    WHERE object_type = 'view'
    LOOP
        INSERT INTO view_version_history (view_name, view_definition, changed_by, change_reason)
        VALUES (
            obj.object_identity,
            pg_get_viewdef(obj.objid),
            current_user,
            current_query()
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;
 
CREATE EVENT TRIGGER view_change_trigger 
ON ddl_command_end
WHEN TAG IN ('CREATE VIEW', 'ALTER VIEW', 'DROP VIEW')
EXECUTE FUNCTION log_view_change();