I have been an agent of change in my pod and the staff I have been waving is postgres :). We have been working out a new project and the OLTP data store is postgres.
Out of the many things I encountered this week two things really topped the list.
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child ,
pg_get_expr(child.relpartbound, child.oid, true) as child_expression,
child.reloptions
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
WHERE parent.relname='datanew';
The query was borrowed from the internet with minor changes.
Out of the many things I encountered this week two things really topped the list.
- Most OLTP workload are random IO. Postgres heep vs cluster key that sorts data
- Declarative partitioning is the way forward to keeping data sets manageble and assists with maintenance tasks.
The code snippet for bellow provides details of the partition child table properties.
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child ,
pg_get_expr(child.relpartbound, child.oid, true) as child_expression,
child.reloptions
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
WHERE parent.relname='datanew';
The query was borrowed from the internet with minor changes.
Comments
Post a Comment