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 on this topic.
Continue reading “PostgreSQL Table Partitioning Part III – Partition Elimination”
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:
Continue reading “PostgreSQL Table Partitioning Part II – Declarative Partitioning”
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? Continue reading “PostgreSQL Table Partitioning Part I – Implementation Using Inheritance”
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
Continue reading “PostgreSQL-Diagnostic-Queries”
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.