LTREE Extension - Hierarchical Tree

Giriş

Açıklaması şöyle

What’s ltree?

ltree is a Postgres extension for representing and querying data stored in a hierarchical tree-like structure.
...

ltree enables powerful search functionality that can be used to model, query and validate hierarchical and arbitrarily nested data structures. 

Kurulum

Şöyle yaparız

CREATE EXTENSION IF NOT EXISTS LTREE;

LTREE Sütun Tipi

LTREE Sütun Tipi kullanılır

Index Yaratma

GIST veya BTREE tipinden index yaratılır

Örnek

Şöyle yaparız. Sütun ismi path, tablo ismi test

CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Örnek

Şöyle yaparız

CREATE INDEX tree_path_idx ON tree USING GIST(path);

Tablo Yaratma

Örnek

Şöyle yaparız

CREATE TABLE test (path LTREE);
INSERT INTO test
VALUES
    ('Top'),
    ('Top.Science'),
    ('Top.Science.Astronomy'),
    ('Top.Science.Astronomy.Astrophysics'),
    ('Top.Science.Astronomy.Cosmology'),
    ('Top.Hobbies'),
    ('Top.Hobbies.Amateurs_Astronomy'),
    ('Top.Collections'),
    ('Top.Collections.Pictures'),
    ('Top.Collections.Pictures.Astronomy'),
    ('Top.Collections.Pictures.Astronomy.Stars'),
    ('Top.Collections.Pictures.Astronomy.Galaxies'),
    ('Top.Collections.Pictures.Astronomy.Astronauts');
-- Optionally, create indexes to speed up certain operations
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Örnek

Şöyle yaparız

CREATE TABLE tree(
  id SERIAL PRIMARY KEY,
  letter CHARACTER,
  path LTREE
);
CREATE INDEX tree_path_idx ON tree USING GIST(path);

LTREE operators

Bazıları şöyle

_eq

_gt 

_is_null

_ancestor

_descendant 

__matches 

_matches_fulltext 

_any

VACUUM - Tabloyu Bloke Etmez, Disk Alanını da Geri Vermez

Giriş

Açıklaması şöyle. VACUUM komutu bir tablo üzerinde vakumlama işlemini elle başlatır. 

PostgreSQL provides two types of vacuums: manual and auto.

VACUUM Komutları Neden Lazım?

Açıklaması şöyle. PostgreSQL silinmesi istenen satırları hemen silmiyor. Hemen silinmemesiyle ilgili olarak MVCC yazısına da bakabilirsiniz. Bu yüzden bu satırları ara ara VACUMM ile silmek gerekiyor.

When your Java application executes a DELETE or UPDATE statement against a PostgreSQL database, a deleted record is not removed immediately nor is an existing record updated in its place. Instead, the deleted record is marked as a dead tuple and will remain in storage. The updated record is, in fact, a brand new record that PostgreSQL inserts by copying the previous version of the record and updating requested columns. The previous version of that updated record is considered deleted and, as with the DELETE operation, marked as a dead tuple.

There is a good reason why the database engine keeps old versions of the deleted and updated records in its storage. For starters, your application can run a bunch of transactions against PostgreSQL in parallel. Some of those transactions do start earlier than others. But if a transaction deletes a record that still might be of interest to a few transactions started earlier, then the record needs to be kept in the database (at least until the point in time when all earlier started transactions finish). This is how PostgreSQL implements MVCC (multi-version concurrency protocol).

It’s clear that PostgreSQL can’t and doesn’t want to keep the dead tuples forever. This is why the database has its own garbage collection process called vacuuming. There are two types of VACUUM — the plain one and the full one. The plain VACUUM works in parallel with your application workloads and doesn’t block your queries. This type of vacuuming marks the space occupied by dead tuples as free, making it available for new data that your app will add to the same table later. The plain VACUUM doesn’t return the space to the operating system so that it can be reused by other tables or 3rd party applications (except in some corner cases when a page includes only dead tuples and the page is in the end of a table).

VACUUM ve VACUUM FULL Farkı Nedir?

Açıklaması şöyle. Yani VACUUM tablonun kullandığı disk alanını işletim sistemine geri vermez ama tabloları da bloke etmez. VACUUM FULL ise disk alanını geri verir ama tabloyu bloke eder.

VACUUM

The VACUUM process removes DEAD tuples for future usage, but it does not return the space to the operating system.

Therefore, if you perform a bulk data deletion or updates, you might be using too much storage due to space occupied by these DEAD tuples. 

VACUUM FULL

The VACUUM FULL process returns the space to the operating system, ... It does the following tasks.

1. VACUUM FULL process obtains an exclusive lock on the table.

2. It creates a new empty storage table file.

3. Copy the live tuples to the new table storage.

4. Removes the old table file and frees the storage.

5. It rebuilds all associated table indexes, updates the system catalogs and statistics.

VACUUM şeklen şöyle

Örnek

İsmi SampleTable olan tabloyu vakumlamak için şöyle yaparız

VACUUM SampleTable;

Örnek

Şöyle yaparız

VACUUM ANALYZE table_name;

Sütun Tipleri - OID

Giriş

Açıklaması şöyle.

A column of type Oid is just a reference to the binary contents which are actually stored in the system's pg_largeobject table. In terms of storage, an Oid a 4 byte integer. On the other hand, a column of type bytea is the actual contents.

PG_LARGEOBJECT Sistem Tablosu yazısına bakabilirsiniz.

Örnek

Eğer OID tipindeki sütunları görmek istersek şöyle yaparız

SELECT * FROM information_schema.columns WHERE data_type = 'oid';

CREATE COLLATION

Giriş

Kendi Collation nesnemizi yaratmak için kullanılır. 

Örnek

Mevcut collation nesnelerini kullanmak için şöyle yaparız.

SELECT label FROM personal.storage_disks ORDER BY label ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "POSIX" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "default" ASC;

Örnek

Şöyle yaparız

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

Örnek - Case Insensitive Unique Index

Açıklaması şöyle

Postgres is case sensitive. Use the following case_insensitive collation to assist with case-insensitive operations.

To perform case-insensitive uniqueness enforcement for a particular column, specify COLLATE case_insensitive after the column name.

Şöyle yaparız

/* See https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC */
CREATE COLLATION case_insensitive
(provider = icu, locale='und-u-ks-level2', deterministic=false);

Sonra şöyle yaparız

CREATE UNIQUE INDEX users_email_unique ON users(email COLLATE case_insensitive);

İndeks'in kullanıldığını görmek için şöyle yaparız

EXPLAIN
SELECT * FROM users
WHERE lower(email) = lower('Seed-system-user')
AND deleted_at IS NULL;

Çıktı şöyle olmalı

-> Bitmap Index Scan on users_email_unique

JSON Indexing

Giriş

Açıklaması şöyle

PostgreSQL offers two types of indexes to work with JSON data.

- B-Tree index

- GIN index/Full-text search index.

1. B-Tree Index

Örnek

Index olmadan bir sorgu çalıştıralım

EXPLAIN ANALYZE SELECT * FROM books_data WHERE data ->> 'author' = 'Jessica Evans';

Çıktı olarak şunu alırız. Burada Parallel Seq Scan kullanıldığı görülüypr

Gather  (cost=1000.00..36481.22 rows=3864 width=279) ...
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on books_data  ...
         Filter: ((data ->> 'author'::text) = 'Jessica Evans'::text)
         Rows Removed by Filter: 257582
 Planning Time: 0.087 ms
 Execution Time: 127.673 ms
(8 rows)

Bir index oluşturalım

CREATE INDEX author_index ON books_data ((data ->> 'author')); 

Çıktı olarak şunu alırız. Burada Index Scan kullanıldığı görülüyor

Index Scan using author_index on books_data  ...
   Index Cond: ((data ->> 'author'::text) = 'Jessica Evans'::text)
 Planning Time: 0.298 ms
 Execution Time: 0.091 ms
(4 rows)

2. GIN index/Full-text search index.

GIN yazısına taşıdım

JSON Path - XPath Benzeri Bir Arama Dili

Giriş

Açıklaması şöyle

JSON Path is a powerful tool for searching and manipulating a JSON object in SQL using JavaScript-like syntax:

- Dot (.) is used for member access

- Square brackets ("[]") are used for array access

- SQL/JSON arrays are 0-indexed, unlike regular SQL arrays that start from 1

Açıklaması şöyle

The jsonpath type implements support for the JSONPath standard in PostgreSQL to efficiently query JSON data. It provides similar functionality to XPath for XML. It’s a very powerful language, and allows you to traverse json structures level by level and retrieve arbitrary sub-items using multiple assessor operators and nested filter expressions. You can have strict or lax error handling. If lax, the default, then the path engine adapts the queried data to the path, otherwise with strict, an exception occurs. 

Built-In Functions

1. size

Açıklaması şöyle

JSONPath also includes powerful built-in functions like size() to find the length of arrays. 

Örnek

Elimizde şöyle bir tablo olsun

CREATE TABLE LunchOrders(student_id INT, orders JSONB); 

Bu tabloya veri girelim. Json verisi tek tırnak içindedir.

INSERT INTO LunchOrders VALUES(100, '{
  "order_date": "2020-12-11",
  "order_details": {
    "cost": 4.25,
    "entree": ["pizza"],
    "sides": ["apple", "fries"],
    "snacks": ["chips"]}
  }'      
);

INSERT INTO LunchOrders VALUES(100, '{
  "order_date": "2020-12-12",
  "order_details": {
    "cost": 4.89,
    "entree": ["hamburger"],
    "sides": ["apple", "salad"],
    "snacks": ["cookie"]}
  }'      
);

Şöyle yaparız

SELECT * FROM lunchorders WHERE orders @@ '$.order_details.snacks.size() > 0';

2. Comparison Without Type Casting

JSON Path ile şöyle yaparız

SELECT * FROM lunchorders WHERE orders @@ '$.order_details.cost > 4.50';

JSON Operators kullansaydık şöyle yapmak gerekirdi. ->> ile Filtering Query kullanılıyor

SELECT * FROM lunchorders WHERE  (orders -> 'order_details' ->> 'cost')::numeric > 4.50;

NOT NULL Constraint

Giriş

Not null kısıtı create table esnasında veya daha sonra alter table ile eklenebilir.

Örnek

Şöyle yaparız

create table <name> (
  ... columns ...
  constraint either_email check (email is not null or p2pmail is not null)
);

Örnek - alter table

Elimizde iki tane sütun olsun. Herhangi birisinin dolu olduğunu garanti etmek istersek şöyle yaparız. Böylece email veya p2pmail sütunlarından birisinin olması yeterli olur

alter table <name>
  add constraint either_email
    check (email is not null or p2pmail is not null);

Eğer sadece birisinin girildiğini garanti etmek istersek şöyle yaparız

alter table <name>
  add constraint either_email
    check (email is null <> p2pmail is not null);

Açıklaması şöyle

The second form is possibly a little confusing at first glance: what it does is compare both columns' null status -- they aren't allowed to be the same.

JSON Functions and Operators

Ortak get/extract Operators

Açıklaması şöyle

There are six get/extract operators (accessors, and serializers) which work for both json and jsonb data types (“->”, “->>”, “#>”, “#>>”). Half of these (“->”, “#>”) return json, and other half return text (“->>”, “#>>”). Note that they return NULL if the JSON doesn’t have the right structure for the request.

These operators (also range and path operators) are used in a SELECT statement ...

Sadece Jsonb Operators

Açıklaması şöyle

There are also 12 additional operators that work for jsonb only (“@>”, “<@”, “?”, “?|”, “?&”, “||”, “-”, “#-”, “@?”, “@@”).

These include:

- Containment operators (“@>” and “<@”)

- Existence operators (“?”, “?|”, “?&”)

- Concatenation operator (“||”)

- Deletion operators (“-”, “#-”)

- Path checking operators (“@?”, “@@”).

The containment and path checking operators all return boolean results, while the other operators all return jsonb results.

Processing Functions

 JSON Processing Functions yazısına taşıdım

Aggregation

Açıklaması şöyle

There are also json specific aggregation functions ( json_agg etc), which allow you to aggregate (compute a single result from multiple inputs) other data types or json/jsonb into json/jsonb. You can also use standard aggregation functions after casting json to the correct type. 

1.  -> Select Key/Value Pair

Json'daki bir alanı çekmek için kullanılır. Key denilen şey tablodaki json/jsonb sütun ismi. Value denilen şey de Json'daki alan ismidir. Value değerine text olarak değil, kendi tipinde erişiriz.

Json'daki alan isimler F1 -> F2 -> F3 şeklinde fluent olarak kullanılabilir.

Örnek

Elimizde şöyle bir tablo olsun

CREATE TABLE LunchOrders(student_id INT, orders JSONB); 

Bu tabloya veri girelim. Json verisi tek tırnak içindedir.

INSERT INTO LunchOrders VALUES(100, '{
  "order_date": "2020-12-11",
  "order_details": {
    "cost": 4.25,
    "entree": ["pizza"],
    "sides": ["apple", "fries"],
    "snacks": ["chips"]}
  }'      
);

INSERT INTO LunchOrders VALUES(100, '{
  "order_date": "2020-12-12",
  "order_details": {
    "cost": 4.89,
    "entree": ["hamburger"],
    "sides": ["apple", "salad"],
    "snacks": ["cookie"]}
  }'      
);

Örnek

Sadece bir alanı sorgulamak için şöyle yaparız.

SELECT orders -> 'order_date' FROM lunchorders WHERE student_id = 100;

Örnek

İki alanı sorgulamak için şöyle yaparız. Burada order_details altındaki sides dizisi fluent olarak sorgulanıyor

SELECT orders -> 'order_date', orders-> 'order_details' -> 'sides'
  FROM lunchorders WHERE student_id = 100;

Eğer sides dizisinin ilk elemanını istersek şöyle yaparız

SELECT orders -> 'order_date', orders-> 'order_details' -> 'sides' -> 0
  FROM lunchorders WHERE student_id = 100;

2.  ->> Filter Query Results - Nesnenin Alanına Erişir WHERE Cümlesinde Kullanılabilir

 ->> Operator - Filter Query Results yazısına taşıdım

4. #>> Filter Query Results In a Nested Object

Açıklaması şöyle

#>> operator that extracts the JSON sub-object at the specified path as text

Açıklaması şöyle

The jsonb_extract_path_text is a Postgres function that is equivalent to the  #>> operator

Şöyle yaparız

private EntityManager entityManager;

public List<Item> findAll(String expression) {
  return entityManager
    .createNativeQuery("SELECT * FROM item i WHERE " 
    + "i.jsonb_content#>>'{string_value}' LIKE '" 
    + expression + "'", Item.class)
    .getResultList();
}

Aynı şey SQL ile şöyledir

select item0_.jsonb_content as jsonb_co2_0_ 
from item item0_ 
where jsonb_extract_path_text(item0_.jsonb_content,?) like ?

5. @> Check If  an Object Contains a Value

WHERE cümlesinde Array tipler için kullanılır

Örnek

Şöyle yaparız

SELECT orders FROM lunchorders
  WHERE orders -> 'order_details' -> 'sides' @> '["salad"]'; 

orders
----------
{"order_date": "2020-12-12", "order_details": {"cost": 4.89, "sides": ["apple", "salad"],
  "entree": ["hamburger"], "snacks": ["cookie"]}}
(1 row) 

Örnek

Şöyle yaparız.

SELECT * FROM books_data WHERE data @> '{"author" : "Mark Figueroa"}';