Site Reliability Engineering: How Google Runs Production Systems – Book Review

Essential Read for anyone managing highly available distributed systems at scale

The book starts with a story about a time Margaret Hamilton brought her young daughter with her to NASA, back in the days of the Apollo program. During a simulation mission, her daughter caused the mission to crash by pressing some keys accidentally. Hamilton noticed this defect and proactively submitted a change to add error checking code to prevent this from happening again, however the change was rejected because program leadership believed that error should never happen. On the next mission, Apollo 8, that exact error condition occurred and a potentially fatal problem that could have been prevented with a trivial check took NASA’s engineers 9 hours to resolve. Hence early learning from book

“Embrace the idea that systems failures are inevitable, and therefore teams should work to optimize to recover quickly through using SRE principles.”

The book is divided into four parts, each comprised of several sections. Each section is authored by a Google engineer.

In Part I, Introduction, the authors introduce Google’s Site Reliability Engineering (SRE) approach to managing global-scale IT services running in datacenters spread across the entire world. (Google approach is truly extraordinary) After a discussion about how SRE is different from DevOps (another hot term of the day), this part introduces the core elements and requirements of SRE, which include the traditional Service Level Objectives (SLOs) and Service Level Agreements (SLAs), management of changing services and requirements, demand forecasting and capacity, provisioning and allocation, etc. Through a sample service, Shakespeare, the authors introduce the core concepts of running a workflow, which is essentially a collection of IT tasks that have inter-dependencies, in the datacenter.

In Part II, Principles, the book focuses on operational and reliability risks, SLO and SLA management, the notion of toil (mundane work that scales linearly, and can be automated) and the need to eliminate it (through automation), how to monitor the complex system that is a datacenter, a process for automation as seen at Google, the notion of engineering releases, and, last, an essay on the need for simplicity . This rather disparate collection of notions is very useful, explained for the laymen but still with enough technical content to be interesting even for the expert (practitioner or academic).

In Parts III and IV, Practices and Management, respectively, the book discusses a variety of topics, from time-series analysis for anomaly detection, to the practice and management of people on-call, to various ways to prevent and address incidents occurring in the datacenter, to postmortems and root-cause analysis that could help prevent future disasters, to testing for reliability (a notoriously difficult issue), to software engineering the SRE team, to load-balancing and overload management (resource management and scheduling 101), communication between SRE engineers, etc. etc. etc., until the predictable call for everyone to use SRE as early as possible and as often as possible. This is where I started getting a much better sense of practical SRE (a.ha!)

Overall it’s a great read, however it isn’t perfect. The two big downsides for me are 1.) this is one of those books that’s a collection of chapters by different people, so there’s a fair amount of redundancy and 2.) the book takes a sided approach on “Build Vs Buy” dilemma of engineering. I mean at Google scale, it will always be better to build, however that is rarely true in the real world. But even including the downsides, I’d say that this is the most valuable technical book I’ve read in the year. If you really like these notes, you’ll probably want to read the full book.

Azure Data Studio – Switching from Management Studio (SSMS) to Azure Data Studio (ADS)

Azure Data Studio (formerly SQL Operations Studio) is a free Cross-Platform DB management tool for for Windows, macOS and Linux. Azure Data Studio (ADS) initial release was only compatible for SQL Server, however recently Microsoft released a PostgreSQL extension for ADS – so now you can also manage your PostgreSQL instance using ADS. For more details on Azure Data Studio PostgreSQL Extension, refer to my earlier posts

Initially, I was apprehensive switching to ADS as I did not want to leave the comfort and ease of SSMS – after all I had been using it for more than a decade. My thoughts changed once I was on it.

Also, do you know ADS is built on Visual Studio code that has multiple options to ‘Customize’?And in this post I’ll take that feature and share how you could ‘Customize’ and ‘Personalize’ Azure Data Studio.

I. Change the Color theme

  1. Open Settings by clicking the gear on the bottom left and click on Settings
  2. Click on Color Theme
  3. Choose from the number of options (Choose between Light, Dark and High Contrast themes)

II. Change Keyboard Shortcuts

  1. Open Settings by clicking the gear on the bottom left and click on Settings
  2. Click on Keyboard Shortcuts

Change Run Query from Default to F5 And/Or Ctrl+E

// Place your key bindings in this file to overwrite the defaults
[
 {
  "key": "ctrl+e",
  "command": "runQueryKeyboardAction"
 },
 {
  "key": "f5",
  "command": "-runQueryKeyboardAction"
 }
]

III. Add Extensions

  1. Click the Extensions icon on the left
  2. Select the Install button on the extension you want from the list
  3. Click the Reload button to activate the installed extension

Refer to my previous post for  detailed step-by-step instructions for installing PostgreSQL extension.

IV. Get ‘Actual’ execution plan

  1. Highlight the query and Press [Ctrl] + [M] Or click Explain

You can modify the keyboard shortcut to your preference in user settings

V. Open an Integrated Terminal

  1. Use the Ctrl+` keyboard shortcut with the back tick character
  2. As a default, Terminal on my Windows 10 use Powershell, while Linux and macOS use $SHELL.
  3. You can customize and change the terminal by specifying the correct path for executable and update the settings. Below is the list of common shell executable and their default locations.
// Command Prompt
"terminal.integrated.shell.windows": "C:\\Windows\\System32\\cmd.exe"
// PowerShell
"terminal.integrated.shell.windows": "C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe"
// Git Bash
"terminal.integrated.shell.windows": "C:\\Program Files\\Git\\bin\\bash.exe"

Isn’t customizing on ADS easy? Let me know what you think.

I’ll share more about Azure Data Studio as I continue the journey – so stay tuned!

Azure Data Studio PostgreSQL Extension – Custom insight dashboard


Azure Data Studio (formerly SQL Operations Studio) is a free Cross-Platform DB management tool for Windows, macOS and Linux.  Staying true to their promise of offering a unified data management experience for developers, Microsoft recently released PostgreSQL Extension for Azure Data Studio

So now developers can use same great GUI features for PostgreSQL database as they were for SQL Server, like IntelliSense, Multiple Query windows and Custom insight dashboard. In this blog post I’ll explain how to create a custom insight and add it to PostgreSQL dashboard as a widget

For this exercise I’ll use a simple query to display ‘active connections’ grouped by connection state

Step 1. Prepare your custom insight

1. Open a new Query Editor (Ctrl+N)

2. Copy / Paste below query

-- ADS custom dashboard - Get Active Vs Inactive connections
SELECT state, count(pid) 
    FROM pg_stat_activity 
     GROUP BY state, datname
     HAVING datname = '<app data>' --replace with your db name
          ORDER BY count(pid) DESC;

3. Save the query as “connection_stats.SQL” file and execute the query (F5)

4. Once you get the result-set, click on View as Chart

5. Customize the chart and click on Create Insight

Step 2. Add the insight to database dashboard

  1. Copy the insight configuration (the JSON data).
  2. Press Ctrl+Comma to open User Settings
  3. Type dashboard in Search Settings
  4. Click Edit for dashboard.database.widgets

  5. Paste the insight configuration JSON into dashboard.database.widgets. A formatted dashboard setting should look something like this
  6. Save the User Settings file
  7. In Server Explorer, right click on your database server name and click Manage
    Similar to above, you can also create more such “insights” to the default server dashboard. I have created one for checking top 5 tables by size (using below query)

    -- Get details of TOP `n` tables in database
    SELECT cl.relname AS objectname 
    ,pg_total_relation_size(cl.oid)/1024/1024/1024 AS size_in_GB
      FROM pg_class cl
         LEFT JOIN pg_namespace n ON (n.oid = cl.relnamespace)
         LEFT JOIN pg_stat_user_tables s ON (s.relid =cl.oid)
         WHERE nspname NOT IN ('pg_catalog', 'information_schema')
                 AND cl.relkind <> 'i' 
                 AND nspname !~ '^pg_toast' 
                   ORDER BY pg_total_relation_size(cl.oid) DESC 
                      LIMIT 5;

Azure Data Studio PostgreSQL Extension – Free data management tool to manage your PostgreSQL databases

Azure Data Studio (formerly SQL Operations Studio) is free Cross-Platform DB management tool for for Windows, macOS and Linux. Azure Data Studio was initially only released for managing SQL Server, however with the today’s Microsoft’s announcement , it will now be possible to connect and manage PostgreSQL databases with Azure Data Studio PostgreSQL Extension (Sweet deal!)

In this (and probably next few blog posts) I’ll be exploring this shiny new Azure Data Studio PostgreSQL Extension and will share my experience. So lets get started with Install and Configuration.

This post is written assuming you already have Azure Data Studio Installed on your machine (PC or Mac).  If you haven’t installed it already, the steps are simple and available here

How to Add Azure Data Studio PostgreSQL Extension

  1. Select the extensions icon from the sidebar in Azure Data Studio
  2. Type ‘PostgreSQL‘ into the search bar. Select the PostgreSQL extension
  3. Click “Reload Now
  4. Extension Install Notification
    Downloading https://pgtoolservice.file.core.windows.net/download/pgsqltoolsservice-win-x64.zip?sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-06-28T01:59:13Z&st=2019-02-15T18:59:13Z&spr=https&sig=LPGNO%2BGA%2FjWCavlMuLFKevGK%2FkbulFXARRbXwORgkp4%3D
    
    (21434 KB)....................Done!
    
    Installing pgSQLToolsService service to C:\XXXX\.azuredatastudio\extensions\microsoft.azuredatastudio-pgsql-0.1.0\out\pgsqltoolsservice\Windows\1.2.0-alpha.22
    
    Installed

    Note: If you are do not see above notification, consider restarting Azure Data Studio Window

  5. Click “New Connection” icon in the SERVERS page
  6. Here, you’ll now be able to select “PostgreSQL” in Connection type list
  7. Specify the all connection details as below and click
    - Server Name: PostgreSQL host name
    - User name: User name for the PostgreSQL
    - Password: Password for the PostgreSQL user
    - Database Name: Database name in PostgreSQL
    - Server Group: <Optional - if you want to create a server group>
    - Name: <Optional - name this connection>

In the next few posts, I’ll to share my experience using Azure Data Studio PostgreSQL Extension.

PostgreSQL Table Partitioning Part III – Partition Elimination

Understanding Partition Elimination in PostgreSQL 11

This is Part-III for my series on Postgres Table partitioning. I’ll encourage you to also read Part-I and II

PostgreSQL Table Partitioning Part I – Implementation Using Inheritance

PostgreSQL Table Partitioning Part II – Declarative Partitioning

In this post, lets compare the READ performance between partitioned and an un-partitioned table. Before that lets first review our table schema and data distribution.

Un-Partitioned Data Set
-- Data Distribution un-partitioned tbl
SELECT logdate, COUNT (logdate)
FROM measurement_np
GROUP BY logdate;

Partitioned Data Set
-- Data Distribution partitioned tbl
SELECT logdate, COUNT (logdate)
FROM measurement
GROUP BY logdate;

Comparing Query Plan (EXPLAIN ANALYZE) and Partition Elimination

1st Execution – on cold cache
-- Un-Partitioned tbl
Index Scan using measurement_np_indx_logdate on measurement_np (cost=0.44..416.13 rows=10672 width=16) (actual time=0.031..40.625 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)

-- Partitioned tbl
Append (cost=0.43..4.46 rows=1 width=16) (actual time=0.028..2.316 rows=10000 loops=1)
-> Index Scan using measurement_y2006m04_logdate_idx on measurement_y2006m04 (cost=0.43..4.45 rows=1 width=16) (actual time=0.028..1.813 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)
2nd Execution – on hot cache
-- Un-Partitioned tbl
Index Scan using measurement_np_indx_logdate on measurement_np (cost=0.44..337.14 rows=8671 width=16) (actual time=0.040..1.750 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)

-- Partitioned tbl
Append (cost=0.43..405.61 rows=9519 width=16) (actual time=0.022..1.942 rows=10000 loops=1)
-> Index Scan using measurement_y2006m04_logdate_idx on measurement_y2006m04 (cost=0.43..358.01 rows=9519 width=16) (actual time=0.021..1.426 rows=10000 loops=1)
Index Cond: (logdate = '2006-04-11'::date)
Conclusion

On in both attempts (cold and hot cache), the data retrieval from  ‘partitioned’ tables was faster than the un-partitioned table.

PostgreSQL Table Partitioning Part II – Declarative Partitioning

Starting Postgres 10.x and onward, it is now possible to create declarative partitions.

In my previous post ‘postgresql-table-partitioning-part-i-implementation-using-inheritance‘, I discussed about implementing Partitioning in PostgreSQL using ‘Inheritance’. Up until PostgreSQL 9, it was only way to partition tables in PostgreSQL. It was simple to implement, however had some limitations like:

  • Row INSERT does not automatically propagate data to a child tables (aka partition), instead it uses explicit ‘BEFORE INSERT’ trigger, making them slower
  • INDEXES and constraints have to be separately created on child tables
  • Significant manual work is required to create and maintain child tables ranges

‘Declarative’ partitioning released with Postgres 10 does not have these limitations and requires much less manual work to manage partitions.

Let’s see the implementation of ‘Declarative’ partitioning with example:

— Step 1.
Create a partitioned table using the PARTITION BY clause,                              — which includes the partitioning method (RANGE in this example) and the list of column(s) to use as the partition key

CREATE TABLE measurement (
 	city_id int not null,
 	logdate date not null,
 	peaktemp int,
 	unitsales int
) PARTITION BY RANGE (logdate);

— Step 2.
— Create Index on parent table
— Note: creation of seperate indexes on parent table is not required

CREATE INDEX measurement_indx_logdate ON measurement (logdate);

— Step 3.
— Create Default partition

CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;

— Create partitions with exclusive range and dfeualt partition (catch-all for out of range values)

CREATE TABLE measurement_y2006m02 PARTITION OF measurement 
             FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement 
             FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE measurement_y2006m04 PARTITION OF measurement 
             FOR VALUES FROM ('2006-04-01') TO ('2006-05-01');
CREATE TABLE measurement_y2007m11 PARTITION OF measurement 
             FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

Let’s review our schema now

— Step 4
— Insert sample rows

DO $DECLARE
  BEGIN
    FOR i in 1..10 loop
      INSERT INTO measurement VALUES (1,'2006-02-07',1,1);
    end loop;
  end $;

DO $DECLARE
  BEGIN
    FOR i in 1..1000000 loop
      INSERT INTO measurement VALUES (1,'2006-03-06',1,1);
    end loop;
  end $;

DO $DECLARE
  BEGIN
    FOR i in 1..1000000 loop
      INSERT INTO measurement VALUES (1,'2006-04-09',1,1);
    end loop;
  end $;

DO $DECLARE
  BEGIN
    FOR i in 1..1000000 loop
      INSERT INTO measurement VALUES (1,'2007-11-11',1,1);
    end loop;
  end $;

--Optional Step
ANALYZE measurement;

–Step 5
–Test partition elimination

  SELECT * FROM measurement
    WHERE logdate = '2007-11-11';

Let’s look at the Query Plan

As you can see, the ‘Declarative’ partitioning is much more intuitive and requires less manual steps in declaring  partitions compares to inheritance.

thanks for reading!

PostgreSQL Table Partitioning Part I – Implementation Using Inheritance

In earlier PostgreSQL versions, it was not possible to declare table partitions syntactically. Partitioning can be implemented using table inheritance. The inheritance approach involves creating a single parent table and multiple child tables (aka. Partitions) to hold data in each partition range.

In this post, I’ll discuss the implementation of table partitions using inheritance. However before proceeding, let’s first understand why do we need partitioning?

Why Partition?

The simple answer is to improve the scalability and manageability of large data sets and tables that have varying access patterns.

Typically, you create tables to store information about an entity, such as customers or sales, and each table has attributes that describe only that entity. While a single table for each entity is the easiest to design and understand, these tables are not necessarily optimized for performance, scalability, and manageability, particularly as the table grows larger.

How can partitioning help? 

  1. When tables and indexes become very large, partitioning can help by partitioning the data into smaller and more manageable sections.
  2. It allows you to speed up loading and archiving of data, so that you can perform maintenance operations on individual partitions instead of the whole table, and this in turn improves the query performance.

There is a ton of information published on partitioning, But if you new to partitioning in PostgreSQL, below are some great examples:

Now that we have some insight what table partitioning is, let’s do a real partitioning  using below scripts:

How to Partition a Table?

— Step 1.
— CREATE PARENT & CHILD TABLES

CREATE TABLE parent (
id int,
col_a varchar,
col_b varchar);
--Child Table 1
CREATE TABLE range1() INHERITS (parent);
--Child Table 2
CREATE TABLE range2() INHERITS (parent);
--Child Table 3
CREATE TABLE range3() INHERITS (parent);

Let’s review the schema now

— Step 2.
— CREATE PARTITION FUNCTION THAT WILL HOLD LOGIC OF ‘ON-INSERT’ TRIGGER

CREATE OR REPLACE FUNCTION partition_parent() RETURNS trigger as $$
  BEGIN
    IF (new.id < 10) THEN
         INSERT INTO range1 VALUES (new.*) ;
    ELSEIF (new.id >= 10 AND NEW.id < 20 ) then
         INSERT INTO range2 VALUES (new.*) ;
    ELSEIF (new.id >= 20 AND NEW.id < 30 ) then
         INSERT INTO range3 VALUES (new.*) ;
    ELSE
         RAISE EXCEPTION 'out of range';
END IF;

RETURN NULL;
END;
$$ language plpgsql;

— Step 3.
— CREATE TRIGGER AND ATTACH IT TO THE PARENT TABLE TO BE PARTITIONED

CREATE TRIGGER partition_parent_trigger
    BEFORE INSERT ON parent
        FOR EACH ROW EXECUTE PROCEDURE partition_parent();for each row execute PROCEDURE partition_parent();

— Step 4.
— INSERT SAMPLE ROWS

DO $$DECLARE
  BEGIN
     FOR i in 1..29 LOOP
          INSERT INTO parent(id, col_a, col_b) VALUES (i, 'a', 'b');
     END LOOP;
END $$;

— Optional Step (stats update)

ANALYZE parent;
-- Step 5.
-- TEST PARTITION ELIMINATION (PRUNING)

EXPLAIN ANALYZE
SELECT * from parent
   WHERE id = 5;

Query Plan 

In the next Post in this series, I’ll discuss the new ‘Declarative Partitioning‘ implementation

thanks for reading! 

PostgreSQL-Diagnostic-Queries – Nov 2019

psql queries to quickly Identify & resolve database performance problems

 

 

 

As a seasoned data store engineer, I often find myself in situations where a production application is down due to some sort of performance issue and I am being asked “What’s wrong with the database?”. In almost all these situations, the database (along with the DBA) is automatically considered guilty until proven innocent. As a DBA, I need the tools and knowledge to help quickly determine the actual problem, if there is one, because maybe there’s nothing wrong with the database or the database server. My favorite approach to start with data driven performance analysis using  PostgreSQL systems catalog

In below post, I am sharing bunch of PostgreSQL system catalog queries that can be used to troubleshoot database engine performance

Postgres system catalogs are a place where database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL’s system catalogs are regular tables.

Instance Details

 

1. Get server IP address, Postgres Version and Port number

SELECT inet_server_addr() AS "Server IP"
, version() AS "Postgres Version"
, setting AS "Port Number"
, current_timestamp::timestamp
FROM pg_settings WHERE name = 'port';

--Postgres query for the specific server version, in the standard major.minor.patch format

2. Get server version

SHOW server_version;

| Version | First Release | Final Release |
| -------- | ------------------ | --------------------- |
| 12 | October 3, 2019 | November 14, 2024 |
| 11 | October 18, 2018 | November 9, 2023 |
| 10 | October 5, 2017 | November 10, 2022 |
| 9.6 | September 29, 2016 | November 11, 2021 |
| 9.5 | January 7, 2016 | February 11, 2021 |
| 9.4 | December 18, 2014 | February 13, 2020 |
| 9.3 | September 9, 2013 | November 8, 2018 |
| 9.2 | September 10, 2012 | November 9, 2017 |
| 9.1 | September 12, 2011 | October 27, 2016 |

3. Get system information

SELECT inet_server_addr() AS Server_IP --server IP address
, inet_server_port() AS Server_Port --server port
, current_database() AS Current_Database --Current database
, current_user AS Current_User --Current user
, pg_backend_pid() AS ProcessID --Current user pid
, pg_postmaster_start_time() AS Server_Start_Time --Last start time
, current_timestamp::timestamp - pg_postmaster_start_time()::timestamp AS Running_Since;

-- Server up time

4. Get details of server parameter configuration from view pg_settings

SELECT * FROM pg_settings;							-- all parameters

SELECT name, unit, setting FROM pg_settings WHERE name ='port'                  
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='shared_buffers'        -- shared_buffers determines how much memory is dedicated for caching data
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='work_mem'              -- work memory required for each incoming connection
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='maintenance_work_mem'  -- work memory of maintenace type queries "VACUUM, CREATE INDEX etc."
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='wal_buffers'           -- Sets the number of disk-page buffers in shared memory for WAL
UNION ALL          
SELECT name, unit, setting FROM pg_settings WHERE name ='effective_cache_size'  -- used by postgres query planner
UNION ALL
SELECT name, unit, setting FROM pg_settings WHERE name ='TimeZone'              -- server time zone

-- This gives you a lot of useful information about your postgres instance

5. Get OS information

SELECT version();

-- Get OS Version

| OS | Wiki References |
| ------ | ----------------------------------------------------- |
| RedHat | wikipedia.org/wiki/Red_Hat_Enterprise_Linux |
| Windows| wikipedia.org/wiki/List_of_Microsoft_Windows_versions |
| Mac OS | wikipedia.org/wiki/MacOS |
| Ubuntu | wikipedia.org/wiki/Ubuntu_version_history |

6. Get location of data directory (this is where postgres stores the database files)

SELECT name, setting FROM pg_settings WHERE name = 'data_directory';
--OR
SHOW data_directory;

7. List all databases on current instance ALONGWITH CREATE TIMESTAMP

SELECT datname as database_name, (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification as create_timestamp 
FROM pg_database WHERE datistemplate = false;

CONNECTION DETAILS

8. Get max_connections configuration

SELECT name, setting, short_desc from pg_settingsWHERE name = 'max_connections';

9. Get total count of current user connections

SELECT COUNT(*) FROM pg_stat_activity;

10. Get total Active Vs Inactive connections

SELECT state, count(pid) 
FROM pg_stat_activity
GROUP BY state, datname
HAVING datname = '<your_database_name>'
ORDER BY count(pid) DESC;

-- One row per server process, showing database OID, database name, process ID, user OID, user name, current query, query's waiting status, time at which the current query began execution
-- Time at which the process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off.
-- Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on

Database Details

**** Switch to a user database that you are interested in *****

11. Get database current size (pretty size)

SELECT current_database(), pg_size_pretty(pg_database_size(current_database()));

12. Get details of TOP 20 objects in database

SELECT nspname AS schemaname
, cl.relname AS objectname
, CASE relkind WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
ELSE 'other'
END AS type
, s.n_live_tup AS total_rows
, pg_size_pretty(pg_total_relation_size(cl.oid)) AS size
FROM pg_class cl
LEFT JOIN pg_namespace n ON (n.oid = cl.relnamespace)
LEFT JOIN pg_stat_user_tables s ON (s.relid =cl.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND cl.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(cl.oid) DESC
LIMIT 20;

13. Get size of all tables

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r') a) a;

14. Get table metadata

SELECT relname, relpages, reltuples, relallvisible
, relkind, relnatts, relhassubclass, reloptions
, pg_table_size(oid)
FROM pg_class WHERE relname='<table_name_here>';

15. Get table structure / DESCRIBE a table

SELECT column_name, data_type
, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS where table_name = '<table_name_here>';

-- Does the table have anything unusual about it?
-- a. contains large objects
-- b. has a large proportion of NULLs in several columns
-- c. receives a large number of UPDATEs or DELETEs regularly
-- d. is growing rapidly
-- e. has many indexes on it
-- f. uses triggers that may be executing database functions, or is calling functions directly

CONNECTIONS

16. Get Lock connection count

SELECT COUNT(DISTINCT pid) AS count 
FROM pg_locks WHERE NOT GRANTED;

17. Get locks_relation_count

SELECT relation::regclass AS relname
, COUNT(DISTINCT pid) AS count
FROM pg_locks WHERE NOT GRANTED GROUP BY 1;

18. Get locks_statement_duration

SELECT a.query AS blocking_statement
, EXTRACT('epoch' FROM NOW() - a.query_start) AS blocking_duration
FROM pg_locks bl JOIN pg_stat_activity a
ON a.pid = bl.pid WHERE NOT bl.GRANTED;

INDEXING

19. Get missing indexes

SELECT relname
,seq_scan - idx_scan AS too_much_seq,
CASE
WHEN seq_scan - coalesce(idx_scan, 0) > 0
THEN 'Missing Index?'
ELSE 'OK'
end
,pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000
ORDER BY too_much_seq DESC;

20. Get Unused Indexes

SELECT indexrelid::regclass as index
, relid::regclass as table
, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND indisunique is false;

21. Get index usage stats

SELECT
t.tablename AS "relation",
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

QUERY PERFORMANCE

22. Get TOP 10 costly queries

SELECT  r.rolname,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu ,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
substring(query, 1, 800) AS short_query
FROM pg_stat_statements
JOIN pg_roles r ON r.oid = userid
ORDER BY total_time DESC
LIMIT 5;

CACHING

23. Get TOP cached tables

SELECT relname AS "relation"
, heap_blks_read AS heap_read
, heap_blks_hit AS heap_hit
, ((heap_blks_hit*100) / NULLIF((heap_blks_hit + heap_blks_read), 0)) AS ratio
FROM pg_statio_user_tables;

AUTOVACUUM

24. Last Autovaccum

SELECT relname as "relation"
, EXTRACT (EPOCH FROM current_timestamp-last_autovacuum) as since_last_av
, autovacuum_count as av_count
, n_tup_ins
, n_tup_upd
, n_tup_del
, n_live_tup
, n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY relname;

PARTITIONING

25. List all table partitions (as Parent/Child relationship)

SELECT
nmsp_parent.nspname AS parent_schema
, parent.relname AS parent
, child.relname AS child
, CASE child.relkind WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
ELSE 'other'
END AS type
, s.n_live_tup AS total_rows
FROM pg_inherits
JOIN pg_class parent
ON pg_inherits.inhparent = parent.oid
JOIN pg_class child
ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent
ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child
ON nmsp_child.oid = child.relnamespace
JOIN pg_stat_user_tables s
ON s.relid = child.oid
WHERE child.relkind = 'r'
ORDER BY parent,child;

 

 

 

 

 

“Hit Refresh” Book Review – Transforming Microsoft with a growth mindset

“A mind needs books as a sword needs a whetstone, if it is to keep its edge. – George R.R. Martin”

Yes, I admit that I sort of dropped the ball on reading. What was more surprising is that all this while I almost did nothing to correct it. Fortunately sanity prevailed and my wife who herself is a voracious reader and a writer (though her first masterpiece is still in making) help me remind about all the good things I am missing by not reading. So I picked up books again!

This blog is share reviews of whatever little I’ve read and learn from your thoughts.

So what is my latest read?

Hit Refresh: The Quest to Rediscover Microsoft’s Soul and Imagine a Better Future for Everyone – By Satya Nadella

516+-p6YgoL

 

First as an Ex Micrsoftie, I am in absolutely love with Microsoft and I take so much pride in talking about how the years that I spent in MS has helped me shaping my career. While some may disagree, but I firmly believe that impact that Microsoft had on our daily lives is beyond any other technology company. Not just the software, Microsoft clearly were the first company that provided the platform, tools and more importantly the inspiration to dream and design a digital world, as it we know today. Over the time, other tech companies came to their age and outsmarted Microsoft in multiple key areas including Search, Social and Mobile. And then Satya Nadella came to helm he embarked on a journey to re-discover the lost soul of company and make it relevant (and Cool) again.

‘Hit Refresh’ is primarily divided into three parts

  1. First is Satya’s personal story coming from India and arriving at Microsoft in 1992
  2. Second is story of Microsoft transformation and steps Satya took to curate the ‘Growth Mindset’ in employees
  3. Third (and favorite) part of where Satya talks about the disruptions and transformation that technology is going to create, which will overcome the existing limits of physics and chemistry and ultimately better the mankind

Author has been extremely honest and open in talking about the strengths of his competitors and acknowledges the need find the smart ways to partner with companies to learn from each other perspective. There are multiple places in book that made me realize that Satya is a leader who has no fear of being authentic and vulnerable. He actually talked about his biggest fumble at Grace Hopper conference and how working with ‘Frenemies’ Microsoft created more growth opportunities like Office for Mac!

The key take away the book offers to its readers is to stay optimistic about what’s to come. As the technology is advancing, the world is changing faster then ever. One  definite ways master this change is have a “Growth Mindset

“Don’t Be a Know-It-All, Be a Learn-It-All – Satya Nadella”

Learning beyond SQL…PostgreSQL – Indexes

It’s been some time since I’ve blogged, even though I’ve been reading a lot all this while. One of the reason is that I couldn’t find enough compelling topics to write and share. Microsoft has been moving too fast with their SQL Server releases (2012, 2014, 2016….and we’re talking  Linux beta right now) and I’ve always been catching up.

However, between all this, something has changed. Due to an ever-growing buzz around Open Source, I haltingly started looking into PostgreSQL.  Truth be told, I’m starting from ground zero (so nothing to loose) and will be writing on topics that might sound too simple for some of you, nevertheless you may still find ’em helpful.

So starting with Indexes in PostgreSQL

PostgreSQL offers several index types:

  • B-tree
  • Hash
  • GiST  and GIN

Each index type uses a different algorithm that is best suited to different types of queries. In this post we’ll talk about B-tree indices.

Why Index?

  • Speed up data retrievals
  • Indexes reference data locations, explicitly, for the indexed column, consequently reducing data retrieval time
  • Without indices, SQL performs sequential table scans in search for data (applies to SELECT and DMLs)
  • B-tree index is sorted in ascending order by default

Hands-on

#Create Index Syntax
CREATE INDEX name ON table USING btree (coulmn);

#Check of the existing indices on  a table (as they can also be created implicitly by PRIMARY or UNIQUE key definition)
SELECT * FROM pg_indexes WHERE schemaname = 'public';
SELECT * FROM pg_stat_all_indexes WHERE schemaname NOT IN ('pg_catalog','pg_toast');

#Query a table without a filter condition and get query plan using EXPLAIN
SELECT * FROM public.film;

Seq Scan on film  (cost=0.00..127.00 rows=2000 width=384)

1-18-2017-11-01-50-am

#Query a table with a filter condition and get query plan using EXPLAIN
SELECT title AS Name, release_year AS year 
FROM public.film
WHERE title in ('Clones Pinocchio','Vanilla Day');

Index Scan using idx_title on film (cost=0.28..21.63 rows=4 width=19)
Index Cond: ((title)::text = ANY (‘{“Clones Pinocchio”,”Vanilla Day”}’::text[]))

1-18-2017-11-02-14-am

Here, after specifying the WHERE condition Postgres Planner (aka Optimizer) decided to choose an index, instead of sequentially scanning the table. Postgres is able to find the targeted rows in an index, and then fetch them from disk selectively.

It’s a very wide topic, so I’ll write more about indexes. However to sum it up, PostgreSQL provides lot of flexibility with B-tree indexes so they can be optimized to suit your requirement and keep your queries snappy.