24 lines
1.7 KiB
Plaintext
24 lines
1.7 KiB
Plaintext
Performance Optimizations & Triggers:
|
|
The database uses triggers to automate maintenance tasks, keeping data consistent and fast without manual intervention.
|
|
|
|
1. Auto-Timestamps: Key tables (users, listings, animals, etc.) automatically update updated_at whenever modified.
|
|
2. Auto-Hydration (listings): Critical search data (species, breed, location, sex) is automatically copied types from animals/locations to listings.
|
|
3. Auto-Ratings: Seller ratings (average & count) are instantly recalculated and stored on the user profile whenever a review is added.
|
|
|
|
Tables:
|
|
1. listings (The Feed)
|
|
The central table. un-normalized for speed.
|
|
Optimization: It stores redundant copies of search fields (filter_species_id, filter_location_geog, etc.).
|
|
This allows millions of listings to be filtered/sorted instantly using a single table scan.
|
|
|
|
2. listing_analytics_events (High-Speed Buffer)
|
|
Tracks every user interaction (views, clicks, calls).
|
|
Design: Insert-only buffer. This prevents locking the main listings table for every single view, ensuring the marketplace stays responsive under high load.
|
|
|
|
3. breeds
|
|
Description stores text or link to an s3 document.
|
|
|
|
Extensions Used:
|
|
1. pgcrypto: Provides cryptographic functions, used here primarily for generating random UUIDs (gen_random_uuid()) and potentially for hashing if needed later.
|
|
2. uuid-ossp: Provides functions to generate universally unique identifiers (UUIDs), specifically used for uuid_generate_v4() as a fallback or alternative to gen_random_uuid().
|
|
3. postgis: Adds support for geographic objects to the PostgreSQL database. Crucial for the "Near Me" feature, analyzing the `geog` column in the listings table to find items within a certain radius. |