Kyle Kingsbury’s Post

Indexes improve scalability with serializable transactions (serializable != serialized). For example, PostgreSQL has a false positive with Seq Scan but no error with Index Scan. In YugabyteDB, there is no wait when the primary key includes the locked range https://lnkd.in/dpXHtKPG

Discord's Message Storage: From Millions to Trillions In 2015, Discord started using MongoDB to store messages. By November 2015, there were already 100 million messages that did not fit in RAM, causing unpredictable latency. In 2017, Discord moved to storing billions of messages in a Cassandra database. By 2022, the number of stored messages had ballooned to trillions across 177 nodes.  But Cassandra was plagued by several serious issues. - Hot partitions occurred when a small number of high traffic channels overwhelmed nodes, cascading latency across the cluster. - Garbage collection pauses created latency spikes. - Compactions fell behind, forcing expensive reads to query multiple SSTables. - Maintenance like node repairs interrupted service. Discord migrated to ScyllaDB for the following benefits: - Written in C++ instead of Java, eliminating disruptive garbage collection pauses - Shard-per-core model provides stronger workload isolation to prevent hot partitions from cascading latency across nodes.  - Reverse query performance optimized to meet Discord's needs - They reduced nodes to 72 while increasing disk space per node to 9TB. To further protect ScyllaDB, Discord: - Built intermediary data services in Rust that limit concurrent traffic spikes - Data services sit between the API and database, coalescing requests - Query the database just once even if multiple users request the same data - Rust provided fast, safe concurrency ideal for this workload The results? Tail latencies down from 40-125ms to a steady 15ms. Database uptime improved from weekend-long outages to smooth sailing. The system easily handled World Cup traffic spikes, processing events like goals and penalties without breaking a sweat. Discord continues to scale, now reliably storing trillions of messages with ScyllaDB. -- Subscribe to our weekly newsletter to get a Free System Design PDF (158 pages): https://bit.ly/496keA7

In Postgres (and many other databases), the 𝘀𝗲𝗿𝗶𝗮𝗹 𝗼𝗿𝗱𝗲𝗿 of serializable transactions isn't determined by their 𝘀𝘁𝗮𝗿𝘁 𝗼𝗿 𝗰𝗼𝗺𝗺𝗶𝘁 𝗼𝗿𝗱𝗲𝗿, which makes debugging tricky. For example, consider two concurrent transactions, Tx1 and Tx2, where Tx1 starts first but Tx2 commits first. Postgres allows this because the final effect is as if Tx1 ran and committed before Tx2, meaning the serial order is Tx1 -> Tx2, even though the commit order is Tx2 -> Tx1. If you swap the contents of these transactions, the serial order would reverse as well. According to Postgres docs, "any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order." However, this "some order" isn't tied to the transaction start or commit order, and the actual serial order can't be retrieved from Postgres. This complicates debugging -- replaying transactions by start or commit order won't reproduce the same results. The challenge is even greater with more commonly used levels like snapshot isolation when no serial order exists. I discussed more and proposed a solution in my VLDB'23 paper: https://lnkd.in/gsMM9RNr I'm writing a new blog post about how the DBOS time travel debugger can correctly reproduce past transactions under snapshot or serializable isolation. Stay tuned!

We are approaching in-memory read/write parity with MySQL. We did this by transitioning our Yacc parser from statically union typed stack objects to runtime-casted interface types. The change removes about 80% of parsing overhead. Learn more in our blog by Max Hoffman. https://lnkd.in/eKSya4Zf

How to avoid the pitfalls of indexing data in PostgreSQL? The article describes how improper index management in a PostgreSQL database can lead to serious system performance problems. For example, the author shows how adding an index using the CREATE INDEX CONCURRENTLY method can fail without any error message, leaving a half-generated index and causing a load on the system during query execution. So it's worth monitoring your indexes, but how to do it? Read more in the article.

Postgres replication in docker compose. You don't have to repeat the same code in docker-compose.yml: version: '3.8' x-postgres-common: &postgres-common image: postgres:14-alpine user: postgres restart: always healthcheck: test: 'pg_isready -U user --dbname=postgres' interval: 10s timeout: 5s retries: 5 services: postgres_primary: <<: *postgres-common ....

In this video, I delve into the intricacies of Isolation levels and their impact on the Galera Cluster. I provide a detailed explanation of the SNAPSHOT ISOLATION level, complete with a demo. Additionally, I touch upon the Galera Certification based replication architecture. Watch the video here: https://lnkd.in/gQgd-BWv Your feedback, questions, and suggestions are highly appreciated. Feel free to share them in the comment section to help enhance the quality of the content. Don't forget to subscribe to my channel and share it with your MySQL network. Learn more about Galera Certification based replication architecture: https://lnkd.in/g-y9BTmf #mysql #mysqldba #galera #isolation

Avoiding network round-trips is a good practice to improve performance and throughput in distributed systems. That's why this SQL (and Postgres) feature called "AND CHAIN " is helpful: START TRANSACTION; -- workload of 1st transaction COMMIT AND CHAIN; -- workload of 2nd transaction ROLLBACK AND CHAIN; -- workload of 3rd transaction COMMIT; https://lnkd.in/dSTfGYCG

"What we learned This was a fun journey after all, and definitely a good optimization: who doesn’t love making something a thousand times faster? It ended up landing on v9.7.0 and later, and got backported to the 9.5 ESR as well, starting in v9.5.3. Also, I learned a couple of very interesting things: Always use BUFFERS when running an EXPLAIN. It gives some data that may be crucial for the investigation. Always, always try to get an Index Cond (called Index range scan in MySQL) instead of a Filter. Always, always, always assume PostgreSQL and MySQL will behave differently. Because they do." https://lnkd.in/epzYvwek

I've just published a new article diving deep into Database Locking mechanisms. 🔒 If you've ever faced race conditions, phantom reads, or wondered how to prevent data anomalies in transactional systems, this one's for you! I also explore real-world examples like ticket booking systems to show how these mechanisms work in action. Check out the full article and let me know your thoughts or share your experiences with database locking and concurrency issues. https://lnkd.in/d47zTAQ6 #DatabaseManagement #ConcurrencyControl #MySQL #DatabaseLocking #InnoDB #SoftwareEngineering #TechArticles #SoftwareDevelopment #TechCommunity