Postgresql

1. pg_stat_statements Extension that tracks execution statistics for all SQL statements (execution count, total time, etc.). Here full docs 2. PostgreSQL Logging (postgresql.conf) Configure parameters like log_statement, log_duration, or log_min_duration_statement to log queries to a file. 3. auto_explain Automatically logs execution plans for slow queries, useful for performance tuning. 4. pg_stat_activity View currently running queries and session info: SELECT * FROM pg_stat_activity; 5. pgBadger A powerful log analyzer that generates detailed HTML reports from PostgreSQL logs. ...

Client certificate authentication in PostgreSQL provides a secure, passwordless way to authenticate users. Instead of relying on passwords, clients present valid X.509 certificates to prove their identity. What to Configure Server-Side Requirements SSL certificates: Server certificate + client certificates PostgreSQL SSL settings: Enable SSL and configure certificate paths Authentication rules: Configure pg_hba.conf for certificate-based auth User mapping: Link certificate Common Names to database users Client-Side Requirements Client certificate: Valid X.509 certificate for the user Private key: Matching private key for the certificate Root certificate: Server’s certificate for verification Connection parameters: Proper SSL mode and certificate paths How to Configure 1. Generate SSL Certificates # Create SSL directory mkdir -p /var/lib/postgresql/17/main/ssl cd /var/lib/postgresql/17/main/ssl # Generate server certificate openssl genrsa -out server.key 4096 openssl req -new -key server.key -out server.csr \ -subj "/C=US/ST=State/L=City/O=Org/OU=OrgUnit/CN=localhost" openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt # Generate client certificate for user 'appuser' openssl genrsa -out appuser.key 4096 openssl req -new -key appuser.key -out appuser.csr \ -subj "/C=US/ST=State/L=City/O=Org/OU=OrgUnit/CN=appuser" openssl x509 -req -days 365 -in appuser.csr \ -CA server.crt -CAkey server.key -CAcreateserial -out appuser.crt # Set permissions chown postgres:postgres *.crt *.key chmod 600 *.key chmod 644 *.crt 2. Configure PostgreSQL SSL Settings Add to postgresql.conf: ...

PostgreSQL 18 Beta 1 - New Features and Breaking Changes Source: GitHub - PostgreSQL 18 Beta 1 New Features Overview PostgreSQL 18 introduces over 200 new features and changes. This document summarizes all significant new features, improvements, and breaking changes introduced in the Beta 1 release. Meaning these features can disappear too, this is just a beta. 1. Highlights of Major New Features 1.1 Adaptation to Large Scale Environments Virtual Columns: Support for virtual (non-stored) columns in tables. ONLY Clause for VACUUM/ANALYZE: Now supports partitioned tables to avoid recursive partition scans. 1.2 Reliability Enhancements pg_dumpall Output Format: Supports formats other than plain text. pg_verifybackup Tar Format: Adds support for tar format verification. 1.3 Maintainability Improvements Statistics Migration: pg_dump and pg_dumpall can now include optimizer statistics. Functions for Stats Management: Added functions to set or clear optimizer stats per table/column. pg_restore_relation_stats, pg_clear_relation_stats, pg_restore_attribute_stats, pg_clear_attribute_stats Commits: d32d146 1.4 Programming Improvements Access to OLD and NEW in RETURNING clause. New Function: UUID v7 support. 1.5 Preparations for Future Features Asynchronous I/O Infrastructure: Introduced with io_method and io_workers. Commits: da72269 NUMA Support: Basic infrastructure and views (pg_shmem_allocations_numa). Commit: 8cc139b 2. Breaking Changes & Incompatibilities 2.1 Platform & Tool Support Dropped support for: PA-RISC, LLVM < 14, OpenSSL < 1.1.1, Python < 3.6.8. Added support: Tcl 9. 2.2 Passwords MD5 passwords: Now deprecated; warning shown by default. Commit: db6a4a9 2.3 Configuration Removed --disable-spin-locks and --disable-atomics. Commits: e256266 initdb now enables data checksums by default. Commit: 04bec89 2.4 SQL & DDL Statements COPY FREEZE: Disallowed on foreign tables. Commit: 401a695 EXPLAIN: BUFFERS enabled by default. Commit: c2a4078 CREATE SUBSCRIPTION: Default streaming = parallel. Commit: 1bf1140 3. Architecture Changes 3.1 Catalog Changes New Views: pg_aios, pg_shmem_allocations_numa Added Columns: Many views now include additional metadata, such as constraint enforcement flags, VACUUM/ANALYZE timing, and I/O stats. 3.2 Logical Replication Logs detailed conflict types like insert_exists, update_differ, delete_missing. Commit: 9758174 Propagation of generated columns via publish_generated_columns. Commit: 7054186 3.3 Storage I/O Enhancements Streaming I/O: Broadened scope to VACUUM, CREATE DATABASE, GiST/SP-GiST, etc. Async I/O: Controlled with io_method, can use io_uring. 4. SQL Enhancements 4.1 ALTER Enhancements ALTER CONSTRAINT SET INHERIT/NO INHERIT ADD CONSTRAINT ... NOT VALID ALTER TABLE ONLY DROP CONSTRAINT 4.2 ANALYZE Improvements ONLY Clause: Skip partitions. Verbose Stats: Includes I/O, WAL, and delay stats. 5. Utilities & Tooling pg_combinebackup, pg_createsubscriber: New utilities. pg_stat_io: Combines WAL and I/O metrics. psql \conninfo: More detailed output. 6. Contrib Modules Highlights: ...

Fine tuning and optimizing the fill factor in PostgreSQL can significantly impact the performance of your database. The fill factor is a parameter that determines the percentage of a data page to be filled with rows, leaving space for future updates to minimize page splits and improve performance on frequently updated tables. By adjusting the fill factor, you can optimize storage efficiency and reduce the overhead of page splits. To fine-tune the fillfactor for tables and indexes, consider the following: ...

Fillfactor in PostgreSQL is a parameter that sets the percentage of a data page to be filled with rows, leaving space for future updates to minimize page splits and improve performance on frequently updated tables. If you are having a table with a lot of updates, you can set the fillfactor to a lower value to leave space for future updates. The default fillfactor is 100, which means that the table is completely packed with rows. ...