PG Day Chicago 2025 -- raw notes from talks I attended

[[Ryan Booz]]: 1 Billion Row Challenge: Comparing Postgres, DuckDB, and Extensions
- Gunnar Morling - 1 Billion Row Challenge https://www.morling.dev/blog/one-billion-row-challenge/
- 1 billion rows is about 13.5GB file
- Big Data -- "data that can't be normally processed given current, 'standard' methods"
- The Five V's: Volume, Variety, Veracity, ...
- More storage options for big data with data lake tech like iceberg and parquet
- Postgres Ingest
  - Ingest is the challenge, how quickly can you read in data?
  - Copy is single threaded -- can we do multiple copy processes at once? yes, but you need to set things up right for that to work.
  - Transaction overhead requires batching
  - Unlogged tables improves performance, but at the expense of data safety
- Query Processing (once the data is in)
  - work_mem, shared_buffers, random_page_cost, max_workers_processes, max_parallel_workers, max_parallel_workers_per_gather
  - Ultimately, Postgres is still row-based. There is no batch row-retrieval methods in core.
  - "Projecting" an entire row, reading from TOAST, when you grab columns off the heap
- Columnar Data
  - Stored as sets of rows per column (vectors)
  - You get data compression within vectors
  - Compatible with new file formats
- Vectorized Processing
  - Process batches of column data with one operation
  - SIMD (Single Instruction Multiple Data)
  - Specialized aggregate functions to handle vectors
- DuckDB
  - open-source, MIT analytics database
  - columnar, in-memory, relational database
  - easily embedded
  - no dependencies
  - single process, so it can be embedded and used in something like Postgres
  - columnar-vectorized query processing
  - it has a PostgreSQL parser (libpg_query from pganalyze)
  - extensible
  - MVCC
  - it doesn't have it all though, e.g. there are no roles
  - natively reads and writes multiple file formats
  - natively connects to block storage like S3
  - in-memory, so the data needs to be hardened or else it dies with the process
- pg_duckdb and pg_mooncake
  - you can attach a postgres table to a duckdb instance
  - a lot of people use duckdb as an ETL layer and then save it into Postgres
  - pg_mooncake adds a type to postgres called columnstore
  - https://github.com/duckdb/pg_duckdb
  - https://github.com/Mooncake-Labs/pg_mooncake
- demo
  - copy into vanilla postgres took almost 6 minutes to ingest
  - parallel copy is a go program to batch insert rows, this pushed the ingest time down to ~2min
    - this one maybe? https://github.com/timescale/timescaledb-parallel-copy/blob/main/cmd/timescaledb-parallel-copy/main.go
  - per-table you can set parallel_workers, there has to be enough work_mem for the number of parallel_workers you want to enable
  - nice thing about postgres -- you can set per-session config settings to get the perf you need if your app/system can handle it, without it needing to be set that way all the time
  - the select count(*) for Postgres on 1 billion took minutes, whereas with duckdb it took under 10 seconds
[[Shaun Thomas]]: Living on the Edge: Distributed Postgres Clusters and You
- Author of Postgres 12: High Availability Cookbook
- CAP is not ACID
  - C: Consistency
  - A: Availability
  - P: Partition Tolerance
- PACELC is CAP with Latency
  - vaguely, eventual consistency
- EDB and PgEdge prefer Availability at the cost of Consistency
- Multi-Master
  - Postgres logical replication copies entire result tuple when syncing two data sources
  - There are four categories of conflicts that you can expect when multiple database are accepting writes and eventually need to agree
    - Naturally convergent conflicts
      - Resolve on their own
      - e.g. Update - Delete, either order those happen on Node A and Node B, you get the same end result, the row is deleted
      - Delete - Delete - same end result
      - Update - Truncate - end result is table is truncated
    - Resolvable conflicts
      - lots of systems have a timestamp sync to resolve this with "most recent" wins
      - Insert - Insert : one insert is discarded/overwritten
      - Insert - Insert (with multiple unique keys) : lose a primary key, potentially orphan foreign data
      - Update - Update (Type 1) : two conflicting updates
    - Divergent conflicts
      - Insert - Update : if there are 3+ nodes and inserts and updates arrive at different times, there can be one node that throws away an update and now diverges from the other nodes
    - Phantom conflicts
      - distributed systems need you to use app-generated IDs or rely on natural keys
      - if a commit "failed", confirm that it actually failed
  - How to fix these things?
    - Keep transactions local/regional
    - Avoid unnecessary cross-node activity
    - Prefer ledgers or insert-only tables instead of using updates
    - To avoid insert-insert conflict, use keys that can't conflict
      - UUIDs
      - Snowflake IDS https://github.com/pgEdge/snowflake #[[Postgres Extension]]
    - How to handle Insert-Delete
      - Retain the old row as a "tombstone" (soft delete)
      - Don't allow inserts for tombstone keys
  - CRDT
    - Two basic approaches:
      - apply a diff between the incoming and existing values
      - use a custom data type with per-node "hidden" fields
    - Postgres has a Spock extension for CRDT (via deltas) #[[Postgres Extension]]
    - Postgres has a BDR extension with a postgres type for CRDT #[[Postgres Extension]]
      - Deltas or Aggregates
[[Gülçin Yıldırım Jelínek]]: Anatomy of Table-Level Locks in PostgreSQL
- Locks are a concurrency primitive
- Ensures conflicting actions don't happen in parallel
- Postgres Lock Avoidance
  - MVCC for DML which allows reads and writes to not block one another, generally
  - Writes make copies of new data
  - But reads still lock objects on the small
- There are different isolation levels in Postgres, but read-committed is the default.
  - We have to have a middle ground for consistency, but still getting things done. This is why we don't default to something like serializable.
- MVCC requires having autovaccum tuned well because you'll have these tuples that are getting created and orphaned behind the scenes as things are updated and deleted
- Why we lock? - to ensure correctness and consistency of our data, at the cost of latency, loss of throughput and performance
- Certain DDL operations can lock everything, so we want to use techniques that reduce what gets locked so that we can keep our database responsive to other connections and operations.
- It's important to pay attention to how different types of locks interact or conflict with one another.
  - There is a good chart/matrix of this in the Postgres docs https://www.postgresql.org/docs/current/explicit-locking.html
  - Postgres has many optimizations to take weaker lock modes when it can.
  - A transaction holds a lock until the transaction completes even if the statement that initiate the lock completes
- Lock Queue
  - The queue is not visible in pg_locks, so you need to see the pg_blocking_pids() view to get at that information.
  - Takeaway: set lock_timeout to limit how long something waits for a lock.
  - You still need to think about how your system/application is going to handle a situation where a lock timeout occurs. How will you make sure it eventually gets retried?
- Find ways to do things in Postgres where you don't fully lock things, when possible.
  - E.g. if you can, create an index concurrently instead of serially.
  - or alter table detach partition concurrently
  - These concurrently commands have tradeoffs -- they take longer, not transactional, half the work gets left done when there is a failure.
  - Another approach is to do things in steps:
    - Add a new column that is nullable, update existing rows in batches, and then add not null afterward once everything is backfilled
    - Or, add a check constraint that is not valid to enforce not null, validate the constraint, this is better than simply setting not null after backfilling.
      - TODO check this approach out, new to me!
  - Postgres improves over time
    - For newer versions of Postgres, if you set a default to a scalar value, instead of doing an exclusive lock to update everything, it can present that value through metadata and take a very short lock
- pg_roll #[[Postgres Extension]]
  - zero-downtime, reversible schema changes for Postgres
  - how can we smooth out these kinds of migrations when doing the "expand and contract" pattern
  - your app sets the search path to connect to the version of the database that they want to use
[[Paul Whalen]]: Who Needs Kafka? PubSub Patterns Beyond Message Queues Using Postgres LISTEN/NOTIFY
- What is PubSub?
  - Messaging pattern for sending messages for a topic
  - Subscribers receive messages for a topic
  - Publishers and subscribers don't know about one another, there is a broker between the two
  - Optionally:
    - Usually real time
    - maybe queue-like behavior
- Kafka
  - Producer (publisher) publishes to the broker
  - Consumer (subscriber) subscribes to a topic and receives messages from the broker
  - Messages are persisted
- Postgres
  - NOTIFY to publish a message
  - LISTEN to subscribe to a channel
  - NOTIFY sends a message to the channel
  - pg_notify function does a NOTIFY
- Can we build Kafka on Postgres?
  - Subscriber is notified of an ID that it can then look up the message for from a channel table.
- With notify we can only send a certain amount of data, a hard limit of 8kb.
  - With Postgres you can decouple the notification from the reading of the data
- With listen/notify in postgres, they are completely ephemeral, so if you aren't listening then you will miss messages.
  - You have to know what you last were notified about and then be able to query for any missed messages.
- You can get far on Postgres using the right patterns.
[[Jay Miller]]: Getting Advice from Myself Using PGVector, RAG, Flat Files, and JSON_TABLEs
- Open Search as a vector database https://opensearch.org/platform/os-search/vector-database/
- LangChain - wrapper layer to smooth differences between different LLMs https://www.langchain.com/
- I told ChatGPT Jesus to take the wheel.
- JSON_TABLE https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-TABLE
- hybrid search
  - vector search isn't enough because it is finding things that are near all the terms which can give you results that don't make sense for what you want
  - hybrid search allows you to get multiple search results with weights so that you can better filter your results to something with higher signal
[[Jimmy Zelinskie]]: Implementing Strict serializability with pg_xact
- Consistency (of ACID)
```
> the contract for how data can be observed  
```
- causal dependency - a read informs the choice to do a write, but the read could be outdated
- atomicity doesn't give a guarantee about visibility
  - leader-follower with a read replica is an example of when this can happen
  - but this can also happen in single node postgres as well
- Strict Serializability https://jepsen.io/consistency/models
  - This is the most consistent you can get. It requires both Serializability and Linearizability
- SpiceDB https://authzed.com/spicedb
  - Authorization Data Layer that can point to and store data in other database, like Postgres
- pg_xact https://pgpedia.info/p/pg_xact.html
  - pg_current_snapshot()
  - pg_snapshot_xmin()
  - pg_snapshot_xmax()
  - pg_snapsho_xip() (transactions in progress)
  - pg_visible_in_snapshot()
    - synthesize their own snapshot ID to pass to this function for custom snapshots
  - commit_timestamp=on; pg_xact_commit_timestamp()
- xid and xid8 (64 bit) transaction IDs
- Vector Clocks https://sookocheff.com/post/time/vector-clocks/
[[Melih Mutlu]]: The Journey of your Query
- libpq has simple and extended query modes
  - extended breaks the planning process into steps that can be cached and reused
  - psql uses the simple query mode by default -- exec_simple_query()
  - can we configure psql to do extended query mode?
- Parsing
  - Converts SQL statement into C code (I assume he means some kind of internal AST representation that the C code can work with)
  - Postgres may rewrite the query if there are optimizations that can be done
- Planning
  - Planning based on costs, where you estimate the costs of different ways to execute the query, e.g. whether to do a seq scan or use an index, and then pick a plan
- Explain / Analyze
  - The cost=1.23..45.67) is composed of the first number is the startup cost and the second number is the total cost
- Executing (the plan)
  - it traverses the plan tree, and as it gets to each scan node, it performs a scan and returns the results to the rest of the plan and continues execution
Lightning Talks
- pspg as a pager for psql https://github.com/okbob/pspg
- visualizing dot density on maps (who was the speaker? any resources for learning more about dot density techniques?)

jbranchaud/pg_day_chicago_2025.md

Consistency (of ACID)