Tomasz Gintowt’s Post

SELECT age + 5 AS new_age FROM users WHERE new_age > 30; Will it: ✅ Run successfully ❌ Throw an error 👇 Comment your answer before reading further. 💡 Hint: Think Execution Order in PostgreSQL In PostgreSQL, a SELECT query is logically processed in this order: 🥇FROM 🥈 WHERE 🥉 GROUP BY 🏅 HAVING 🎯 SELECT 📊 ORDER BY ✂️ LIMIT #Database #SQL #PostgreSQL #Backend

PostgreSQL's The Cumulative Statistics System: pg_stat_activity I’m honestly falling in love with PostgreSQL’s documentation. It’s some of the best documentation I’ve ever read. Postgres also provides some of the best built-in statistics you can get out of the box: - Running queries - Locks - Indexes - Cache hit rate - Write-ahead log (WAL) - I/O - Replication - And more. I’ve worked at a few different companies, and at each one of the most used was pg_stat_activity to check currently running queries, their state, and the elapsed time. Basic query to fetch this data looks like: select now() - query_start as elapsed, query, state, wait_event, wait_event_type, usename, backend_type from pg_stat_activity; You can tune it however you need: show only queries from your account, filter for active queries, build a locked-queries tree, and so on. If you use PostgreSQL in your projects, which stats do you check most often? Do you have any go-to queries for monitoring or metrics? #Backend #PostgreSQL #Database

PostgreSQL micro blog: Don’t ignore table bloat, it silently kills performance. In PostgreSQL, bloat happens when dead row versions from UPDATE/DELETE operations accumulate on disk. Over time this increases table size, bloats indexes, and slows queries and backups. A few quick signals to watch for: • Rising disk usage without corresponding data growth • Slow scans on tables that used to be fast • Autovacuum not keeping up with churn How to tackle it: • Run regular VACUUM and tune autovacuum thresholds per table • Use tools like pgstattuple to measure actual dead tuples • Reclaim space with smarter tools (pg_repack, VACUUM FULL, or pg_squeeze ) when needed • Consider partitioning large, high-churn tables as part of long-term strategy () Bloat isn’t a bug, it’s a natural side effect of MVCC. But proactive maintenance keeps Postgres fast and storage efficient. #PostgreSQL #DatabasePerformance #DBA #PerformanceTuning #DatabaseSpa

Completed PostgreSQL Summary Stats and Window Functions (4 hours). Key concepts: Window functions (ROW_NUMBER, RANK, etc.) Partitioning datasets Running totals and advanced aggregations Analytical query patterns Window functions significantly expand analytical capabilities within SQL itself. #PostgreSQL #WindowFunctions #DataAnalytics

Tired of database management feeling like a relic from the past? 🕰️ Serverless SQL is here to make your life easier, but with a growing landscape, choosing the right one can be tricky. We break down the top serverless SQL databases for developers in 2026, comparing their strengths, vibes, and catches. Discover which one fits your project best – from workflow kings to edge speedsters. #ServerlessSQL #DatabaseManagement #DeveloperTools #TechComparison #BackendDevelopment #CloudComputing #PostgreSQL #MySQL #SQLite #NeonDB #Supabase #Turso #PlanetScale #TiDB #Prisma

If you’re using PostgreSQL without knowing its core commands, you’re underutilizing it. #PostgreSQL #Postgres #Database #SQL #BackendDevelopment #DatabaseDesign #QueryOptimization #DataEngineering #TechLearning #SoftwareEngineering #DBMS #DeveloperGrowth

LinkedIn & Facebook Version The promise of hands-off multi-master PostgreSQL is almost there, but not quite. Conflict resolution remains a key issue, but there are more. We explain the 5 biggest operational and consistency hurdles in Postgres in this primer. Read it here: https://lnkd.in/e3CdASTc #PostgreSQL #DBA #HighAvailability #Database #ClusterControl

PostgreSQL EXPLAIN and why the width parameter actually matters ℹ️ When people use EXPLAIN in PostgreSQL, they usually focus on: - cost - join type - index usage 🛑 But one parameter is often ignored: width And it directly affects query performance. In a PostgreSQL execution plan, width is the average estimated row size in bytes for that node. For example, among two next queries, the second's width will be much less: select * from orders where client_id=1; OR select id, created from orders where client_id=1; 🦥 Smaller row width means: - Less memory used in sorts and hash joins - Smaller intermediate result sets - Less data moved between nodes (in case using entire clusters) - Lower overall execution cost - Less data transfered over a network On large datasets, this matters a lot. If you're serious about PostgreSQL performance tuning, EXPLAIN might be the hidden reason your plan is expensive. #PostgreSQL #DatabasePerformance #Backend #PerformanceTuning

In PostgreSQL, running a massive DELETE command does not immediately free up disk space. In fact, an UPDATE is just an INSERT in disguise. Understanding MVCC is the key to solving database bloat. Here is how it works. Postgres uses Multi-Version Concurrency Control. The goal is to allow readers to access data without being blocked by writers modification of the same rows. To achieve this, Postgres never modifies data in place. When you run an UPDATE, the engine actually performs two distinct actions. 1. It performs an INSERT of the new version of the row. 2. It marks the old version as a 'dead tuple' (essentially a soft delete). The old data is still sitting physically on the disk. It is just invisible to new transactions. This leads to a specific problem. If you have a write-heavy application, these dead tuples pile up fast. This is table bloat. Your queries get slower because the database engine has to scan through all the junk to find the live data. This is why the Auto-Vacuum process is mandatory. It is the janitor. It runs in the background to clean up these dead tuples and mark that storage space as reusable for future inserts. If your storage usage seems high after a cleanup, don't panic. Check if your vacuum process is keeping up. #Database #SoftwareEngineering #DataEngineering #PostgreSQL #SQL #DatabasePerformance #MVCC #SystemDesign

Quick Tutorial: Setting Up PostgreSQL WAL Logging PostgreSQL’s Write-Ahead Logging (WAL) is the backbone of durability and crash recovery. Setting it up for logging is straightforward and powerful. Here are the essential Steps: 1. Edit your postgresql.conf file: wal_level = logical # or replica, depending on use case archive_mode = on archivecommand = 'cp %p /var/lib/postgresql/walarchive/%f' logging_collector = on logdirectory = 'pglog' log_filename = 'postgresql-%a.log' 2. Create the WAL archive directory: bash mkdir -p /var/lib/postgresql/wal_archive chown postgres:postgres /var/lib/postgresql/wal_archive 3. Restart PostgreSQL to apply changes: systemctl restart postgresql Result: - WAL files are archived for recovery or replication. - Logs are collected in pg_log for monitoring and troubleshooting. Key Takeaway: WAL logging isn’t just about compliance—it’s about resilience. With proper setup, you gain reliable recovery, replication readiness, and clear visibility into your database’s behavior. #PostgreSQL #Database #WAL #Logging #DevOps #CloudArchitecture #DataEngineering #DatabaseAdministration #OpenSource #TechTutorial #SQL #Resilience #HighAvailability #DataRecovery #SystemDesign