bank-postgresql-vault

🏦 bank-postgresql-vault

Bank Emoji

💸 A Curated Vault of PostgreSQL Data Modeling Patterns for Banking and Finance 🚀

✨ Welcome to the Vault!

Explore 65+ real-world, production-ready SQL patterns for PostgreSQL, tailored to modern banking, fintech, and data engineering. Each pattern is a self-contained example, ready for learning or drop-in use.
From basic schema design to advanced indexing, temporal tracking, partitioning, security, and more…

🚀 Getting Started

  1. Clone the repo: 
    git clone https://github.com/AishwaryaaP/bank-postgresql-vault.git
cd bank-postgresql-vault

  2. Browse the patterns folder and pick your favorite pattern.

  3. Run in psql or your favorite SQL IDE!

🎯 What’s Inside?

📂 Patterns Index

Below are direct links to every pattern for quick access, with descriptions:

# Pattern File Description 🌐 Quick Link
1 01_create_banking_schema.sql Creates the base banking schema 🔗 View
2 02_surrogate_key_identity.sql Defines tables with surrogate primary keys using IDENTITY columns. 🔗 View
3 03_uuid_keys_pgcrypto.sql Uses UUIDs as primary keys, generated with the pgcrypto extension. 🔗 View
4 04_composite_pk_ledger_lines.sql Introduces composite primary keys for ledger line consistency. 🔗 View
5 05_natural_key_bic_swift.sql Uses natural keys (BIC/SWIFT codes) for bank identification. 🔗 View
6 06_unique_constraints_customers.sql Applies unique constraints to ensure no duplicate customer records. 🔗 View
7 07_foreign_keys_on_delete.sql Demonstrates foreign key constraints with cascading deletes. 🔗 View
8 08_soft_delete_flag.sql Shows soft deletion using a boolean flag instead of hard deletes. 🔗 View
9 09_audit_trail_customers.sql Adds audit trail functionality to track customer changes. 🔗 View
10 10_temporal_history_accounts.sql Implements temporal tables for account history tracking. 🔗 View
11 11_optimistic_locking_accounts.sql Applies optimistic locking with version numbers for concurrent updates. 🔗 View
12 12_pessimistic_locking_transfer.sql Uses pessimistic locking to safely handle money transfers. 🔗 View
13 13_upsert_customer_contact.sql Provides UPSERT logic for customer contact info. 🔗 View
14 14_merge_daily_rates.sql Demonstrates merging daily FX rates using MERGE. 🔗 View
15 15_idempotent_insert_transactions.sql Ensures idempotent inserts of transaction data. 🔗 View
16 16_bulk_insert_copy_payments.sql Loads bulk payment data using COPY. 🔗 View
17 17_batch_upsert_cards.sql Performs batch upserts for card details. 🔗 View
18 18_partitioning_range_tx_date.sql Implements range partitioning by transaction date. 🔗 View
19 19_partitioning_list_region.sql Partitions data by region using list partitioning. 🔗 View
20 20_partition_maintenance_template.sql Provides a template for partition maintenance tasks. 🔗 View
21 21_materialized_view_balances.sql Creates materialized views for customer balances. 🔗 View
22 22_security_definer_read_model.sql Defines security definer functions for read models. 🔗 View
23 23_jsonb_kyc_profile.sql Stores KYC customer profile data in JSONB format. 🔗 View
24 24_jsonb_indexing_gin.sql Creates GIN indexes on JSONB fields for faster queries. 🔗 View
25 25_full_text_search_support.sql Enables full-text search support on text fields. 🔗 View
26 26_trigram_search_names.sql Uses trigram search for fuzzy matching on customer names. 🔗 View
27 27_array_account_flags.sql Stores multiple account flags using PostgreSQL arrays. 🔗 View
28 28_hstore_misc_attrs.sql Uses hstore for flexible key-value storage. 🔗 View
29 29_window_functions_ranking.sql Applies window functions for ranking transactions. 🔗 View
30 30_recursive_cte_org_hierarchy.sql Models organizational hierarchy using recursive CTEs. 🔗 View
31 31_lateral_join_top_tx_per_acct.sql Finds top transactions per account using lateral joins. 🔗 View
32 32_anti_join_inactive_customers.sql Identifies inactive customers using anti-joins. 🔗 View
33 33_exists_vs_in_cards.sql Compares EXISTS vs IN queries for card lookups. 🔗 View
34 34_partial_index_active_cards.sql Creates partial indexes for active cards only. 🔗 View
35 35_expression_index_lower_email.sql Indexes on lowercase emails for case-insensitive lookups. 🔗 View
36 36_multicolumn_index_hot_path.sql Builds multicolumn indexes for performance hot paths. 🔗 View
37 37_concurrent_index_large_tx.sql Demonstrates concurrent index creation on large transaction tables. 🔗 View
38 38_postgres_fdw_corebank.sql Uses Postgres FDW to connect to external core banking DBs. 🔗 View
39 39_event_sourcing_ledger.sql Implements event sourcing pattern for transaction ledger. 🔗 View
40 40_cqrs_read_model_mv.sql Creates CQRS read models using materialized views. 🔗 View
41 41_triggers_examples_audit.sql Shows triggers for auditing changes in key tables. 🔗 View
42 42_plpgsql_fn_interest_calc.sql PL/pgSQL function to calculate account interest. 🔗 View
43 43_stored_procedure_transfer.sql Stored procedure for executing money transfers. 🔗 View
44 44_roles_privileges_least.sql Implements least privilege role-based security. 🔗 View
45 45_row_level_security_per_tenant.sql Sets row-level security policies per tenant. 🔗 View
46 46_monthly_partition_do_block.sql Automates monthly partitions with DO blocks. 🔗 View
47 47_backup_restore_notes.sql Provides SQL notes for backup and restore best practices. 🔗 View
48 48_maintenance_vacuum_analyze.sql Demonstrates table maintenance with VACUUM and ANALYZE. 🔗 View
49 49_sequences_and_setval.sql Manages sequences and resets them with setval. 🔗 View
50 50_gapless_ticket_numbers.sql Ensures gapless ticket numbers using sequences. 🔗 View
51 51_advisory_locks_dedup.sql Uses advisory locks to prevent duplicate inserts. 🔗 View
52 52_explain_analyze_examples.sql Shows query plans with EXPLAIN and ANALYZE. 🔗 View
53 53_query_rewrite_tips.sql Provides query rewrite strategies for performance. 🔗 View
54 54_refresh_mv_concurrently.sql Refreshes materialized views concurrently. 🔗 View
55 55_temp_tables_etl.sql Uses temporary tables in ETL workflows. 🔗 View
56 56_pg_cron_example.sql Schedules tasks with pg_cron extension. 🔗 View
57 57_copy_to_from_csv.sql Imports and exports data using COPY with CSV. 🔗 View
58 58_cdc_wal2json_overview.sql Demonstrates Change Data Capture with wal2json. 🔗 View
59 59_generic_search_function.sql Defines a generic SQL search function. 🔗 View
60 60_schema_migrations_table.sql Creates schema migrations table for versioning. 🔗 View
61 61_deferrable_constraints.sql Shows deferrable constraints for transaction control. 🔗 View
62 62_citext_case_insensitive.sql Uses citext type for case-insensitive text fields. 🔗 View
63 63_isolation_levels_examples.sql Illustrates different transaction isolation levels. 🔗 View
64 64_generated_columns_demo.sql Demonstrates generated columns in PostgreSQL. 🔗 View
65 65_check_constraints_business_rules.sql Implements check constraints for business rules. 🔗 View

🌈 Why this Vault?

⭐️ If you find this helpful, leave a star and share your thoughts!

👤 Author

Aishwarya Yuvaraj Phirke
📂 GitHub: @AishwaryaaP
👋 Connect on LinkedIn

Made with ❤️, ☕ and PostgreSQL — Happy Coding!