Graph Database

Giriş

Açıklaması şöyle

PostgreSQL, in their latest release, version 19, has shipped property graphs.

We can now define a graph over our existing relational tables and query them. So, no need to install new extensions or run migrations.

Let me give you an example. If you already have tables like users, follows, posts, today, we would need to write joins to answer questions like "who follows who" or "which posts got liked by friends of friends."

But with this, we can just declare users as vertices and follows as edges, then query the relationship directly using pattern-matching syntax instead of chaining joins. And, this is apt for all graph use cases like:

- social graphs

- fraud detection

- data lineage

- dependency graphs, etc.

Btw, this graph is a read-only view over your existing tables, and a really powerful part is that we can mix a graph query and a normal SQL query in the same statement.

Of course, it is not as performant or powerful as regular graph databases (yet), but it is a pretty solid choice when you are in your 0 to 1 journey.

You know things are serious when PG gets it as first-class support. Graphs are certainly cool again. Also, looks like this will forever remain true:

Just use Postgres :)

In-memory database

Giriş

Açıklaması şöyle

Did you know you can make PostgreSQL run almost fully in-memory?

Postgres docs explicitly describe a non-durable setup where you can put the whole data directory on a RAM disk and disable:

fsync: don’t force writes to durable storage

synchronous_commit: don’t make COMMIT wait for WAL to flush

You basically trade durability for speed.

Pretty interesting that PostgreSQL can be pushed this far without switching to a separate in-memory database.

It’s a game-changer for running in CI, it’s so much faster

postgresql.conf Dosyası - work_mem Alanı

Açıklaması şöyle

PostgreSQL uses the work_mem configuration parameter to determine the amount of memory available for sorting and hashing operations. Properly configuring work_mem can greatly impact the performance of queries involving sorting or hash joins.

Configuring the work_mem parameter in PostgreSQL is crucial for optimizing query performance, but it requires finding the right balance. Increasing work_mem on high-performance systems can speed up sorting and hashing operations, leading to faster query execution.

However, setting it too high might cause excessive memory usage and potential memory contention, impacting overall performance. To determine the ideal work_mem value, start conservatively and monitor system performance while gradually increasing it. Keep a close watch on memory consumption and query execution times. Additionally, consider using partitioned queries or limiting result set sizes for resource-intensive queries to prevent excessive memory usage. Striking the right balance ensures PostgreSQL operates efficiently without overwhelming system memory and maximizes the benefits of work_mem for improved query performance.

Örnek

Şöyle yaparız

SET work_mem = "128MB";

Örnek

Şöyle

Sorgum neden bu kadar yavaş diye saatlerce baktın. Sorun sorguda değildi.

EXPLAIN çıktısında "external merge Disk" yazıyordu. PostgreSQL sıralama işlemini RAM'de bitirememiş, diske taşmıştı. work_mem 4MB'da kalmış, varsayılan değerden hiç çıkmamıştık.

PostgreSQL bellek yetmediğinde hata vermez, sessizce diske geçer. Sorgu yine de sonuç döndürür ama 200 milisaniyede bitmesi gereken iş 4 saniyeye uzar. Fark edilmesi zordur, çünkü sistem sizi uyarmaz.

PostgreSQL Health Check List

Giriş

Yazıyı buradan aldım

The PostgreSQL health check I run on every new database.

Doesn't matter if it's a fresh install or a production instance someone hands me. Same checklist, every time.

Here's what I look at first:

Autovacuum settings. Is it actually running? Are the thresholds sane for the table sizes? Default scale_factor of 0.2 is fine for small tables but terrible for anything with millions of rows.

Missing indexes. I check pg_stat_user_tables for tables with high sequential scan counts relative to index scans. If a table has 500k seq_scans and 12 idx_scans, something is wrong.

Connection limits. How many connections are configured vs. how many are actually in use? I've seen instances running at 95% of max_connections with no connection pooler in sight.

Replication lag. If there's a replica, how far behind is it? Seconds of lag might be acceptable. Minutes means trouble.

Table bloat. Dead tuples piling up means autovacuum is either misconfigured or can't keep up. This is where most "the database is slow" complaints start.

Idle transactions. Long-running idle-in-transaction sessions hold locks and block autovacuum. They're silent killers.

I got tired of running through this manually on every instance, so I built these checks (and about 50 more) into mydba.dev. It runs them regularly and flags anything that needs attention.

Backup Araçları

Databasus

Açıklaması şöyle

Databasus is an industry standard for PostgreSQL backup tools, offering scheduled backups with compression and encryption for both individual developers and enterprise teams.

pgloader

Örnek

Şöyle yaparız

pgloader mysql://user:pass@10.10.10.10:3306/zabbix \
         postgresql://user:pass@10.10.10.1:31320/zabbix

pg_ivm - PostgreSQL Incremental View Maintenance

Giriş

Açıklaması şöyle

an extension that transforms materialized views into self-updating, always-fresh data structures that update instantly as your base data changes.

Açıklaması şöyle

Under the Hood: How pg_ivm Works

When you create an incremental materialized view, pg_ivm automatically:

1. Installs triggers on all referenced base tables

2. Captures changes through INSERT/UPDATE/DELETE monitoring

3. Calculates deltas to determine the minimal impact

4. Updates the view with only the necessary changes