Pavel Velikhov’s Post

B-trees are used by many modern DBMSs. Learn how they work, how databases use them, and how your choice of primary key can affect index performance.

"B-trees are used by many modern DBMSs. Learn how they work, how databases use them, and how your choice of primary key can affect index performance." https://lnkd.in/eufw4iAh

🚀 Embracing best practices in database management! Check out this insightful article by Daniel on why using UUIDs might not be the best choice for your database. A must-read for developers and database administrators! #DatabaseManagement #BestPractices [Read more here: https://lnkd.in/dAHZ3vsB]

#PostgreSQL's MVCC ensures data consistency with three isolation levels: Read Committed, Repeatable Read, and Serializable. Row-level and table-level locks maintain integrity but may affect performance. Use the pg_locks view to monitor and resolve locking bottlenecks. #PostgreSQLPerformance @MinervaDB #dba https://zurl.co/4rss

#PostgreSQL's MVCC ensures data consistency with three isolation levels: Read Committed, Repeatable Read, and Serializable. Row-level and table-level locks maintain integrity but may affect performance. Use the pg_locks view to monitor and resolve locking bottlenecks. #PostgreSQLPerformance @MinervaDB #dba https://zurl.co/4rss

tl;dr: “By the time you finish this article, you'll have learned how B-trees and B+trees work, why databases use them for indexes, and why using a UUID as your primary key might be a bad idea.” Via: B-trees And Database Indexes — Benjamin Dicken

Pgvector vs. Pinecone: Vector Database Performance and Cost Comparison https://lnkd.in/gK3nKBKG

Hi everyone, I have just published another blog titled “DBMS Indexing: Types, How It Works and Why It Matters“ which covers: - What is Indexing in DBMS? - How Indexing works in DBMS? - Types of Indexing. - How Indexing improves efficiency? - Why Indexing is needed? #Databases #Indexing #DBMS https://lnkd.in/dDw3hFWm

I notice many TPC-C or TPC-DS benchmarks still use outdated SQL queries implemented 30 years ago. Would you prefer to compare databases using poor queries or efficient ones? https://lnkd.in/eY-nDdPC

Thanks : Franck Pachot ! your article is really insightful on multiple fronts: - It highlights that well-written code (or code optimized for performance) is a rewarding strategy for developers. - It demonstrates the ongoing value of SQL rewrites, always an interesting endeavor. However, there are some viewpoints I don’t fully agree with : - For instance, the decision to keep the #PostgreSQL optimizer simple ("it has always been decided to avoid complicating the query planner code when the problem can be fixed in the query") feels a bit limiting. Many other RDBMSs optimize query plans effectively, delivering substantial performance gains without needing code changes—which can sometimes be challenging. - The rewritten code with the window function runs in 500ms, which is an impressive 150x improvement over the initial code. However, the pg_duckdb extension achieved a 50ms runtime (10x faster) without altering the code. To me, it's great no ? - While window functions are indeed more recent and modern, they can occasionally be tricky. For example, LAST_VALUE can be challenging in both #MSSQL and #PostgreSQL, whereas its implementation in #DuckDB or #Netezza is well-optimized and notably faster. - I think the TPC benchmarks remain useful despite their age, as they allow RDBMS comparisons across multiple generations. Rewrites, as far as I know, are permitted for those aiming to make their mark on the performance leaderboard. - Finally, as Erik Darling points out, much SQL code isn't perfect, and the workload required to improve it all would be staggering. So when an optimizer, engine, or extension boosts performance without needing code changes, I’m all for it.