Background Let us expose the columns in each of our tables. Code Outline Column Length The Column length is one of the more perplexing attribute The reason being the existence of a couple of columns that intertwine to properly decode it Attributes attlen ( Attribute Length ) atttypid ( Attribute Type ID ) atttypmod ( … Continue reading PostgreSQL :- Listing Column Names
Background Wanted to put together a short SQL for calculating Average Row Length for each table. Code Outline To get the Average Row Length, we do the following :- Get Table Size pg_relation_size against table object pg_class.oid or pg_class.relpages * pg_catalog.pg_settings.value ( name = 'page_size') Get Number of records pg_class.rel_tuples SQL Output Output #1 … Continue reading PostgreSQL :- Average Row Length for a table
Background Admitting nothing real, nor original. I am just trying to close out this Chrome Tab that showcases the best of Stack Overflow. Stack Overflow Luke Francl List columns with indexes in PostgreSQL Link SQL Outline Tables pg_catalog.pg_class pg_catalog.pg_namespace pg_class.relnamespace = pg_namespace.oid pg_catalog.pg_index pg_class.relnamespace = pg_index.indrelid pg_catalog.pg_attribute pg_attribute.attrelid = pg_index.indrelid and pg_attribute.attnum = any(pg_index.indkey) Columns … Continue reading PostgreSQL – Metadata – Index Columns
Background Of course, we rarely have enough memory. And, so we have to remain vigilant as to what objects are squabbling over memory. Code Extension :- pg_buffercache Registration Pleas ensure that pg_buffercache is registered. Registration steps are outlined here. Outline Get Buffer Size Object :- pg_settings name => shared_buffers setting => value Get Objects … Continue reading PostgreSQL :- What is in our buffer cache ?
Background Getting database table sizes is a good way to familiarize oneself with a couple of PostgreSQL functions. Code SQL Output Output - Northwind Output - Sample Database Summary Here is a quick rundown of the functions we used :- pg_relation_size Returns table size Accepts pg_catalog.pg_class.oid ( object id ) or pg_catalog.pg_class.relname ( relation name … Continue reading PostgreSQL :- Get Table Size
Background Let us quickly list tables. Outline The Information Schema is an ANSI compliant Schema and works well across all database vendors. pg_catalog is PostgreSQL native. Here are the tables and views that we will use in this exercise. information_schema information_schema.tables pg_catalog pg_catalog.pg_tables pg_catalog.pg_class Query information_schema information_schema.tables SQL Output pg_catalog pg_catalog.pg_tables SQL Output pg_catalog.pg_class SQL … Continue reading PostgreSQL :- List Tables