Do you really need a DBA?
Let me say at the getgo: Every organization needs full-time database management. Delaying the hiring of a full time DBA could lead to a bunch of problems that are festering and growing to the point where easy, non-intrusive fixes may not be possible anymore. Unfortunately, some organizations either have never had a DBA or do […]
Running VACUUM FULL in Non-blocking Mode
OK, I got your attention. You’re probably thinking I must actually be referring to other tools like pg_repack. No I am not. I really am referring to running the VACUUM FULL command in non-blocking mode. WHAT!?#$%@! Here we go: Setup 2 psql session windows on the same screen. The 1st window shows the state of […]
Connection Scaling
Connection scaling is concerned with the database science of increasing the number of users connected to a database without performance degradation. PostgreSQL can support thousands of users. For OLTP type SQL requests, it does not correlate into thousands of simultaneously occurring SQL requests being sent to the database. Why? Because most OLTP transactions are small […]
PSQL Helper: Managing Connections and Simplifying Queries
For folks using PSQL to connect to PG databases, it can be a headache to manage a lot of different DB profile connections. PG makes it a bit easier by organizing db profiles in a file called, .pgpass. It contains one line for each DB Profile like this: localhost:5432:mydb:myuser:mypassword This file must reside in the […]
Vacuum Those MVs!
On a particular project, Materialized Views (MVs) were being used quite extensively in the Apps that were being migrated from Oracle to PostgreSQL. One thing I noticed was missing was VACUUM ANLYZE on these MVs after being created or refreshed. It is extremely important to integrate VACUUM ANALYZE commands into this process. Here is the […]
Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
From the docs: “repmgr is an open-source tool suite for managing replication and failover in a cluster of PostgreSQL servers.” For a standby node to be used in a repmgr configuration, it has to be registered. Normally you do this by allowing repmgr to clone the standby first. If a node was not cloned by […]
Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
While I continue to be amazed at how fast PG runs on Amazon Aurora (no WAL logging) or how fast I can create a snapshot or standby (shared storage), there are always a few clouds around. Today the cloud I’m lookin’ at is replica reboots. Every week or so, one of my standbys gets rebooted […]
Working with Amazon Aurora PostgreSQL: what happened to the stats?
If you reboot your PostgreSQL aurora instance, your distribution stats will still be there, but your monitoring stats will not. Official PostgreSQL documentation states that if you do a normal shutdown, both distribution and monitoring stats will still be there when you restart your instance (Monitoring Stats): “When the server shuts down cleanly, a permanent […]
How to set application_name for psql command line utility?
If you are using psql command line utility and wonder how you can set application_name… If you try to set application_name using psql variable, it doesn’t work … $ psql -d postgres -U elephas –variable=application_name=elephas_backup postgres=# show application_name ; application_name —————— psql (1 row) Option-1: psql -U elephas -d ‘dbname=postgres application_name=elephas_backup’ postgres=# show application_name ; […]
Is there a limit on number of partitions handled by Postgres?
PostgreSQL is continuously improving partitions support but there is limitations on number of partitions handled by each release. Based on our experience , if you are using a lot more partitions than its practical limit for a PostgreSQL release, you will experience performance degradation during the planning phase of the query. When you approach the […]