I was recently reviewing a database partitioning definition in YugabyteDB (the postgres “ysql” API), and realized the partition distribution might not be what the developer intended.
Postgres and YugabyteDB allow you to define partitions of parent tables. Partitions are useful in at least two ways:
I was recently setting up a demo to show off query logging features. Two common extensions, pg_stat_statements and pg_stat_monitor, store data locally. In the case of a distributed database, it is helpful to combine the query runtimes on all nodes.
Quick post to share my presentation last week at the YugabyteDB Friday Tech Talk. It was on fuzzy matching, and more generally string searches. Got to nerd out on two of my favorite topics: words (broadly, linguistics and specifically, names) and databases. Check it out!
Fuzzy string matching in YugabyteDB can be done with wildcard lookups, phonetic algorithms (Soundex, Metaphone), and trigram similarity. I’ll show a demo of practical examples using artist names, highlighting the performance differences between wildcard searches and phonetic indices. A combination of indexed double metaphone and trigram methods works best for both speed and precision. Also, while YugabyteDB supports PostgreSQL-style extensions, some indexing optimizations behave differently due to its distributed storage layer.
Partial indexes in PostgreSQL dramatically improve query performance for searches on specific email domains. Standard indexes may not help with wildcard searches, but a partial index targeting a particular domain can reduce query times by avoiding full table scans. Practical examples and execution plans illustrate the speedup, and partial indexes are best for data distributions that remain stable over time.
Covering indexes in PostgreSQL allow queries to be satisfied directly from the index, reducing I/O by leveraging the visibility map. Index-only scans become possible when all required columns are included in the index, and the visibility map tracks which rows are visible. Designing indexes to maximize this effect is a best practice for optimizing read-heavy workloads.
Security vulnerabilities in PostgreSQL related to the search_path setting can allow malicious users to inject trojan-horse functions or objects. Remediation includes removing the public schema from the default search_path and restricting object creation in public. Practical commands and best practices help secure PostgreSQL installations against these exploits.
Nodetool provides detailed information about Cassandra cluster status, node health, and performance metrics. Using nodetool commands helps administrators monitor and troubleshoot clusters, ensuring smooth operation and quick resolution of issues.
MySQL’s Performance Schema enables detailed query profiling and analysis. Using built-in tables and tools helps identify slow queries and optimize database performance, making it easier to troubleshoot and tune applications.