#Database
29 posts
SQLAlchemy 2.0 #6 Advanced Queries: Joins, Aggregation, Subqueries, Bulk Operations
Building real-world queries on select() alone: the difference between scalars and execute return shapes, combining conditions with or_, joins and explicit ON clauses, reading group_by aggregates through labels, subqueries and EXISTS, LIMIT-OFFSET pagination and its limits versus keyset pagination, and bulk INSERT and UPDATE that bypass the ORM unit of work.
PostgreSQL Basics #6 Transactions and MVCC: The Fundamentals of Concurrency
How PostgreSQL preserves consistency when many connections touch the same data at once. The basics of BEGIN, COMMIT, and ROLLBACK and atomicity, the core of MVCC (multi-version concurrency control) — readers never block writers — the fact that UPDATE actually creates a new row version and the dead tuples that foreshadows, what the default Read Committed isolation level means and its trap, and when to raise to Repeatable Read or Serializable.
SQLAlchemy 2.0 #5 Relationships: One-to-Many, Many-to-Many, and the N+1 Problem
The ORM at its best and its most dangerous — relationship(): how foreign keys and relationship() divide the work, declaring bidirectional one-to-many with back_populates, many-to-many through a secondary table, cascade and delete-orphan for parent-child lifecycles, spotting the N+1 problem that lazy loading creates by reading the echo log, and choosing between selectinload and joinedload to fix it.
PostgreSQL Basics #5 Reading EXPLAIN: Diagnosing Queries with Execution Plans
How to confirm why a query is slow with the execution plan instead of guesswork. The difference between EXPLAIN and EXPLAIN ANALYZE (the latter actually executes), reading the plan tree from the innermost node outward, what Seq Scan, Index Scan, Index Only Scan, and Bitmap Scan mean, comparing cost and actual time and estimated vs actual rows, the remedy when estimates are badly off (ANALYZE), and the habit of checking read volume with BUFFERS.
SQLAlchemy 2.0 #4 The Session: Change Tracking, flush vs commit, and the Four Object States
The heart of the ORM, the Session: how it batches changes as a unit of work and emits them as SQL, the difference between flush and commit, the four object states — transient, pending, persistent, detached — the identity map that returns the same object for the same row, why attribute access after commit triggers a new query, and the one-session-per-request scoping rule for web applications.
PostgreSQL Basics #4 Index Fundamentals: When Indexes Get Used and When They Don't
Indexes are a reliable way to make reads fast, and this chapter grounds them in PostgreSQL specifics. How a B-tree index changes lookups, the classic reasons an index you created is not being used (leading-column mismatch, functions applied to the column, type mismatch, low selectivity), the cost in write performance and storage, and the working order of "build from the WHERE, JOIN, and ORDER BY of actually slow queries."
SQLAlchemy 2.0 #3 Defining ORM Models: DeclarativeBase, Mapped, mapped_column
The 2.0-style way of declaring tables as Python classes: the DeclarativeBase inheritance structure, how Mapped type hints and mapped_column divide the work, the rules mapping Python types to database types, how Optional drives nullable, declaring defaults, unique constraints, and indexes, and the naming convention that gives constraints predictable names from day one.
PostgreSQL Basics #3 Joins and Aggregation: Working Instincts for Read Queries
Joins and aggregation make up most of real-world read queries, and this chapter builds the instincts. Choosing between INNER and LEFT JOIN and the classic mistake of filtering LEFT JOIN results in WHERE (turning them back into INNER), the division of labor between GROUP BY and HAVING, the double-counting trap when joins meet aggregation, the fork between subqueries and joins, and the PostgreSQL-native way to get the latest row per group (DISTINCT ON).
SQLAlchemy 2.0 #2 Engines and Transactions: Connection Pools and the Two Commit Patterns
The foundation of SQLAlchemy: engines and transactions. Why connection pools exist and how they behave, what pool_size and max_overflow actually limit, the commit-as-you-go and begin-once patterns split between connect() and begin(), why transactions never auto-commit, defining tables with MetaData and Table, and running CRUD with Core expressions.
PostgreSQL Basics #2 Data Types and Table Design: What to Store Things As
Half of PostgreSQL design is choosing data types. Why text alone is enough for strings, why numeric is mandatory for money, timestamptz as the standard for points in time and the timestamp trap, primary key strategy (bigint IDENTITY vs UUID, and uuidv7), enforcing data integrity at the database layer with NOT NULL, CHECK, and foreign keys, and where enums and arrays belong.
SQLAlchemy 2.0 #1 The Big Picture: Core, ORM, and the 2.0 Style
First post in a series covering SQLAlchemy, the de facto standard database library for Python, from the ground up: the two-layer structure of Core and ORM, why 1.x-era code and 2.0-style code look different, the unified select()-centric query style and type hint support, installation and the first connection, and running raw SQL with text().
PostgreSQL Basics #1 What PostgreSQL Is: Why It Became the Default, Installation and First psql Session
Starting from why PostgreSQL became the default database for new projects: standards compliance, a rich set of data types, the extension ecosystem (pgvector, PostGIS), and a permissive license. Then the fastest local setup with Docker, connecting with psql and the essential meta-commands (\l, \dt, \d), a first cycle of creating a table, inserting, and querying, and a map of the whole course.