PostgreSQL event triggers for automatic updates

In PostgreSQL, you can use event triggers to handle situations where you want detailed records of actions and an aggregate summary stored in another table. For example, if you have a `post_likes` table that keeps track of which users liked which post, you also want the `posts` table to store the total number of likes per post. Instead of recalculating likes with every query using a `COUNT` join, you can create triggers on the `post_likes` table to automatically update the `likes_count` column in the `posts` table whenever a like or unlike occurs. This way, you keep the granular event history and a fast-to-query aggregate value. Do you want me to share more PostgreSQL tips?

A long while back, I came across an article about some cool SQL optimizations. The author compared a few databases to see which optimizations they supported or not. Since I'm working on a caching proxy for PostgreSQL, I decided to look at the optimizations from the article and see what has changed in the most recent version, PG 18, compared to the version tested in the article, PG 9.6 - I wrote a post about what I found: https://lnkd.in/dWsc-m4h

🗣️ "I remember thinking PostgreSQL 7 was as good as it gets. Well, let me put it mildly: I was wrong, and things are a lot better than they used to be." That reflection comes from our Founder & CEO, Hans-Juergen Schoenig, and it frames a crucial question for every PostgreSQL Professional: What comes after PostgreSQL 18's groundbreaking Asynchronous I/O? 🤔 Hans looks beyond PostgreSQL 18 to explore the future of disk I/O, latency, and throughput in PostgreSQL. If you manage high-volume data, then this expert analysis is for you. ▶️ https://ow.ly/x3l750X4c2S #PostgreSQL #PostgreSQL18 #Database #FutureofPostgres #CYBERTEC

PostgreSQL 17 Cheat Sheet — Small Upgrades, Big Gains. PostgreSQL 17 is not about flashy features — it’s about making daily work smoother for DB engineers. Many small, practical improvements that actually matter in real-world environments. Some highlights worth noting: psql usability: Listing tables, schemas, and roles got cleaner and faster. Performance tweaks: Better sorting speed and improved visibility in EXPLAIN. Replication: Logical replication got more control — a big plus for multi-tenant setups. Security: Role management and password control commands are now simpler. What stands out is not the new commands — it’s the developer friendliness. PostgreSQL continues to balance power and simplicity like no other open-source database. If you haven’t gone through the full cheat sheet yet, it’s a great quick read for anyone working hands-on with PostgreSQL 17: 🔗 PostgreSQL 17 Cheat Sheet — Exploring Databases with psql Commands https://lnkd.in/gUHQfWGw PostgreSQL keeps evolving quietly — but efficiently. What’s your favorite new change in v17 so far? #PostgreSQL #DatabaseEngineering #OpenSource #DBA #SQL #Productivity

PostgreSQL 18 is fixing a hidden performance problem with your primary keys. Day 7/8. If you use random UUIDs (uuid_generate_v4()), every INSERT is like shoving a book into a random spot on a sorted shelf. It forces the database to split things apart just to make room. This is slow and creates a mess (index fragmentation). The new uuid_v7() function in PostgreSQL 18 creates IDs that are sorted by time. This means new data is always added neatly to the end of the index. It's a clean, fast "append" operation instead of a messy "insert-in-the-middle" one. The result is faster writes and a much healthier, more efficient database. Read more: https://lnkd.in/geEP54fF #PostgreSQL #PG18 #Database #Developer #Performance #UUID #postgres #pgsql18 #PostgreSQL18 #pgsql

🚀 Day 9 of #100DaysOfDevOps with #KodeKloud 🚀 Today’s challenge was all about MariaDB Troubleshooting Here’s how I did it: 🔹 Ran sudo systemctl status mariadb to check logs and get a better idea of what was going on. 🔹 Googled the error message: “Failed to start MariaDB 10.5 database server" , and the process exited with failure code: "ExecStart=/usr/libexec/mariadbd --basedir=/usr” 🔹 Found multiple possible causes : permission issues, misconfigured service files, or corrupted data directories. 🔹 Since MariaDB was already installed, I suspected a permission issue on the data directory. 🔹Solution: Made sure the data directory (/var/lib/mysql) was owned by the mysql user: sudo chown -R mysql:mysql /var/lib/mysql 🔹Restarted the service and it worked perfectly! 💡 Key Takeaways: ✅ Finding the issue is often harder than fixing it. 😅 ✅ The -R option in chown changes ownership recursively for all subdirectories and files. It was a simple fix, but it solved a real problem. 🦬

Hello everyone. Here is a small article on how to re-sync/rebuild a PostgreSQL standby database by using archived WAL segments, instead of having to fully rebuild it, which can save a considerable amount of time for very large databases. I hope this is helpful. https://lnkd.in/e-FJNwS6

The post highlights the difference between monitoring statistics vs optimizer statistics. pg_upgrade in v18 supports optimizer stats but not extended stats.

PostgreSQL 18 is officially released! The world's most advanced open-source database just got a massive upgrade, focusing on performance, developer experience, and security. Here are the headline features for Postgres 18: Performance & Core Engine Asynchronous I/O (AIO): Up to 3x faster reads for sequential scans and VACUUM operations. B-tree Skip Scans: Allows index usage for queries that omit leading columns in a multi-column index. Faster pg_upgrade: Major upgrades now retain optimizer statistics for instant post-upgrade performance. Developer & SQL - Native uuidv7(): Support for the new time-ordered UUID standard, improving indexing. - Virtual Generated Columns: Now the default, saving disk space and speeding up INSERT/UPDATE. - RETURNING OLD / NEW: Access both before-and-after values in a single UPDATE/DELETE statement. - Temporal Constraints: New WITHOUT OVERLAPS and PERIOD clauses for enforcing time-based integrity. Security & Admin - OAuth 2.0 Support: Easier integration with centralized identity providers (SSO). - Default Checksums: Enabled by default on new clusters for enhanced data integrity. - Improved EXPLAIN ANALYZE: Now automatically includes buffer usage for better query analysis. - MD5 Password Deprecation: Encouraging a shift to SCRAM-SHA-256 for stronger security. #PostgreSQL #Postgres18 #Database #OpenSource #TechNews #Developer

Do you know what makes me stop and admire PostgreSQL? The way it handles updates, deletes, and cleaning itself up. I always thought: Delete → row is gone. Update → row is overwritten. Simple, right? But PostgreSQL doesn’t work like that. When you update, it creates a new version and marks the old one as dead. When you delete, the row isn’t removed instantly — it’s just marked dead. Now think about a busy system with constant updates and deletes. Tables start filling with these “dead” rows. The table size grows bigger than the actual data inside. This is called bloat. And here comes the clever part: VACUUM. It’s like a cleaner that removes dead rows and reclaims space. Even better: Autovacuum. A background worker that does this automatically, like silent housekeeping for your database. But here’s the twist: If autovacuum runs too late → tables bloat and queries slow down. If it runs too aggressively → it eats resources and slows the system. That’s why PostgreSQL lets us tune it with parameters like autovacuum_vacuum_threshold and autovacuum_scale_factor. For me, this is real engineering. Not just storing data, but keeping the system efficient over years of heavy use. PostgreSQL doesn’t just delete blindly. It manages versions, cleans them smartly, and gives us control over the process. This balance between performance, durability, and self-maintenance… that’s what makes me respect PostgreSQL even more. It’s not just a database. It’s an entire system of smart decisions happening behind the scenes. #pg #postgres #sql #vacuum #auto #database #db #optimization