Category: PostgreSQL
-
CPU-intensive query to simulate CPU load in PostgreSQL database
This blog post provides a PL/pgSQL script that utilizes pg_background extension to create and execute CPU-bound background worker processes. Also validates the CPU usage with docker stats.
-
Finding Row Count for All Tables in PostgreSQL Database
The post discusses the effectiveness of two concepts for counting rows in PostgreSQL: using table statistics versus executing the SELECT COUNT(*) command. While table statistics offer faster row estimates with minimal resource consumption, accuracy can be hindered if the statistics are outdated. In contrast, SELECT COUNT(*) provides exact counts but incurs substantial performance penalties, especially with large tables. Testing with the Adventureworks database shows that for retrieving counts of all tables relying statistics collector or mimicking the planner’s logic can be significantly more efficient than direct queries.
-
Table Statistics vs. SELECT COUNT: What to Choose?
Counting rows in database tables is a common concern for DBAs, as it’s highly discussed in forums like ASKTOM and Stack Overflow. Despite its performance drawbacks, developers/dbas often use row counts for data validation during migrations, capacity planning, anomaly detection, and performance tuning. The use of table statistics and computing the exact row count with ‘SELECT COUNT(*) FROM’ is debated. The article emphasizes the need to question the necessity of exact row counts in business requirements perspective.
-
How to send mail from PostgreSQL database
This content discusses methods for sending emails directly from a PostgreSQL database. Three approaches are highlighted: using PostgreSQL extensions like pgMail or pgSmtp, creating custom stored procedures with untrusted languages, and employing the LISTEN/NOTIFY mechanism to communicate with external services. The LISTEN/NOTIFY method is implemented in detailed steps: configuring Postfix for email delivery, creating a PostgreSQL table for email records, setting up a trigger, writing a Python listener, creating a systemd service, and managing logs. This solution provides a reliable method for managing emails directly from the database.
-
How to configure HAProxy with external-check to query PostgreSQL database directly
The blog post discusses configuring HAProxy with an external check to query PostgreSQL directly for database server health monitoring. Choosing to deploy PgBouncer on database hosts led to the decision to use an external check in HAProxy. The configuration and benefits of this setup are detailed, along with sample HAProxy configurations for external checks. Deploying PgBouncer directly on PostgreSQL servers minimized network latency and simplified architecture while securing local connections.
-
Fuzzy Matching in PostgreSQL: A Guide to pg_trgm extension
PostgreSQL’s pg_trgm extension facilitates efficient similarity searches using trigram matching, breaking text into trigrams for pattern matching and enabling the use of distance operators. It supports fuzzy matching and indexing for speedier searches, allowing for functions like similarity() and operator usage.
Osman’s DBlog 