PostgreSQL 9.5 Features

PostgreSQL 9.5 Features

• 1.
  PostgreSQL 9.5 Features (for Developers) saifulmuhajir@tokopedia
• 2.
  UPSERT ● most wantedfeatures ● combination of INSERT and UPDATE ● row oriented, unlike MERGE, which is BATCH --Conflict on unique constraint on columns type & number, update with new name INSERT INTO upsert_tab (type, number, name) VALUES (1, 1, 'Name 3') ON CONFLICT (type, number) DO UPDATE SET name = EXCLUDED.name; INSERT INTO upsert_test (type, number, name) VALUES (1, 1, 'Name 3') ON CONFLICT (type, number) WHERE type = 1 DO UPDATE SET name = EXCLUDED.name;
• 3.
  BRIN (Block RangeIndex) ● tiny index designed for large tables ● store metadata on a range of pages ● ideal for naturally-ordered tables ● index is ONLY 0.003% the size of the heap ● inexpensive to update ● slower lookups than btree CREATE INDEX idx_order_date_brin ON orders USING BRIN (order_date);
• 4.
  GROUPING SETS, CUBEand ROLLUP ● multiple GROUP BY combinations in a single query ● avoids the need for UNION ALL and recomputation ● empty fields are left NULL
• 5.
  SELECT office, NULLas department, COUNT(*) FROM employee GROUP BY office UNION ALL SELECT NULL as office, department, COUNT(*) FROM employee GROUP BY department ORDER BY 1; Before GROUP BY with UNION ALL
• 6.
  SELECT office, department,COUNT(*) FROM employee GROUP BY GROUPING SETS (office, department) ORDER BY office, department; Postgres 9.5 GROUPING SETS (summaries)
• 7.
  SELECT office, department,COUNT(*) FROM employee GROUP BY CUBE (office, department) ORDER BY office, department; Postgres 9.5 CUBE (all categories)
• 8.
  SELECT office, department,COUNT(*) FROM employee GROUP BY ROLLUP (office, department) ORDER BY office, department; Postgres 9.5 ROLLUP (group columns in sequence left to right)
• 9.
  Row-Level SECURITY POLICY ●SELECT, INSERT, UPDATE, or DELETE permission control on rows with USING ● INSERT or UPDATE control over added and modified rows with CHECK ● expressions can contain checks for the current user, subqueries, time comparisons, and function calls ● enabled with GUC row_security, CREATE POLICY, and ALTER TABLE […] ENABLE ROW LEVEL SECURITY
• 10.
  CREATE POLICY policy_user_logON log FOR ALL TO PUBLIC USING (username = current_user); Postgres 9.5 ROLLUP (group columns in sequence left to right)
• 11.
  SKIP LOCKED ● ignorethe LOCKED rows and continue SELECT [...] SKIP LOCKED
• 12.
  TABLESAMPLE ● select randomrows from a table ● algorithms used: SYSTEM & BERNOULLI SELECT * FROM some_table TABLESAMPLE SYSTEM/BERNOULLI(percentage);
• 13.
  IMPORT FOREIGN SCHEMA Before9.5: ● foreign table needed to be defined referencing the destination columns & data types ● problem with lots of tables and definition update 9.5: ● import is easy ● filter can be applied
• 14.
  IMPORT FOREIGN SCHEMApublic FROM SERVER db_server INTO awesome; IMPORT FOREIGN SCHEMA public EXCEPT (reports, audit) FROM SERVER db_server INTO awesome; IMPORT FOREIGN SCHEMA public LIMIT TO (customers, purchases) FROM SERVER db_server INTO awesome; Import Foreign Schema http://www.postgresql. org/docs/current/static/sql- importforeignschema.html
• 15.
  FOREIGN TABLE INHERITANCE ●foreign tables can either inherit local tables, or be inherited from ● in-core sharding! http://www.depesz.com/2015/04/02/waiting-for-9-5-allow-foreign-tables-to- participate-in-inheritance/ http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-foreign-table-inheritance/
• 16.
  # CREATE TABLEmaster_customers (LIKE remote.customers); # INSERT INTO master_customers VALUES (99, 'Jolly',$$Cineplanet$$, '2014-10-30', '2016-10-29', true, 'running', 'premium'); # ALTER TABLE remote.customers INHERIT master_customers; # SELECT tableoid::regclass, * FROM master_customers; tableoid | id | name | company | registered_date | expiry_date | active | status | account_level -----------------+----+-------+---------------+-----------------+-------------+--------+---------+--------------- master_customers | 99 | Jolly | Cineplanet | 2014-10-30 | 2016-10-29 | t | running | premium remote.customers | 1 | James | Hughson Corp | 2014-05-03 | 2016-05-02 | t | idle | premium local_customers | 16 | Bruce | Jo's Cupcakes | 2015-01-15 | 2017-01-14 | t | running | basic (3 rows) # EXPLAIN ANALYSE SELECT tableoid::regclass, * FROM master_customers; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Result (cost=0.00..140.80 rows=1012 width=145) (actual time=0.014..0.595 rows=3 loops=1) -> Append (cost=0.00..140.80 rows=1012 width=145) (actual time=0.012..0.591 rows=3 loops=1) -> Seq Scan on master_customers (cost=0.00..1.48 rows=48 width=145) (actual time=0.012..0.013 rows=1 loops=1) -> Foreign Scan on customers (cost=100.00..124.52 rows=484 width=145) (actual time=0.567..0.567 rows=1 loops=1) -> Seq Scan on local_customers (cost=0.00..14.80 rows=480 width=145) (actual time=0.007..0.008 rows=1 loops=1) Planning time: 0.256 ms Execution time: 1.040 ms (7 rows)
• 17.
  JSONB MODIFICATION ● replacementof or addition to JSONB documents with jsonb_set ● remove JSONB documents, including array, with subtraction ( - ) ● merging JSONB documents with concatenation (|| operator) ● format JSONB documents with jsonb_pretty ● remove key/value pairs with NULL values with jsonb_strip_nulls
• 18.
  SORTING OPTIMIZATION “The abbreviatedkeys optimization can be expected to greatly enhance the performance of sorts in PostgreSQL, including those used for CREATE INDEX. Reportedly, in some cases, CREATE INDEX on text columns can be as much as an entire order of magnitude faster (3x is a more typical improvement). Numeric sorts also support the optimization. Abbreviated keys will complement grouping sets very nicely. Reporting queries will be very significantly faster with PostgreSQL 9.5.” ~shamelessly copied from https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#Sorting_optimization_. 28Abbreviated_Keys.29
• 19.
  Parallel Vacuuming vacuumdb -j4productiondb
• 20.
  Thank you Saiful Muhajir