sql-basics.md

SQL with Postgresql

Agenda

• Local env setup
  • Bonus: CloudSQL methods
• Configuration: most useful configs for balanced performance
• Basics of query dynamics:
  • How a Query Works
  • JOINs
  • CTEs
  • Transactions
  • Functions
  • Window Functions
  • Aggregations
  • Data Generation
  • Constraints
• Useful Queries For Daily Business
  • Some of main 4-Cast queries
• Performance Hints: How to explain the Queries
  • Indexes
  • Statistics, Vacuum, Analyze, Re-Index
• Maintenance:
  • Backup & Restore
  • Storage: All About Sizes (see: link)
  • Upgrades
  • Migrations
• Bonus:
  • Transactions
  • Isolation Levels
  • psql Tips
  • Columnar

Local env setup

➜ docker run --name demo_server -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=demo -e POSTGRES_USER=admin -p 54455:5432 postgis/postgis:14-3.2-alpine -d

# overwrite `application_name` to enable transparant monitoring
➜ docker exec -it demo_server psql -U admin -d 'dbname=demo application_name=ilhan_cli_app'

# OR
➜ docker -e PGAPPNAME=ilhan_cli_app exec -it demo_server psql -U admin -d demo

CloudSQL

Get cloud proxy binaries from link

# Expose database to local access
PRROJECT_ID=... \
REGION=... \
INSTANCE_NAME=... \
./cloud_sql_proxy -instances=${PRROJECT_ID:-myproject}:{REGION:-myregioon}:{INSTANCE_NAME:-myinstance}=tcp:54552

• Use clone functionality to have an exact copy of current database
• Run experimentsorheavy qureies (over yearly reporting queries) against clone db
• Delete clones as soon as you finished your experiment

Configuration

Use SHOW all; to get all available configurations.

Important values:

• effective_cache_size
• max_connextions
• maintenance_work_mem
• statement_timeout
• log_min_duration_statement
• max_locks_per_transaction

Recommendations

• Use ~/.psqlrc for personalized values
• Use LOCAL setting changes for transaction based changes
• SET ... is only effectivfe for current session
• Database Connections
  • Keep max_connextions low
  • Use pg_stat_* for overviews. PS: Google CloudSQL's query-insights also uses pg_stat_activity

SQL Query Dynamics

• Query Execution Order: Top to down execution order => FROM, JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> DISTINCT [ON] -> LIMIT
• When more than one table reference is listed in the FROM clause, the tables are cross-joined (the Cartesian product)
• Table Functions can be handy for query re-use

JOINs

JOIN clauses nest left-to-right by default. Parentheses can be used to control the JOIN order

• CROSS JOIN: For every possible combination of rows from T1 and T2 a row consisting of all columns in T1 followed by all columns in T2. T1(N rows), T2(M rows) => N * M rows.
• FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE. It is also equivalent to FROM T1, T2
• The words INNER and OUTER are optional in all forms.
• INNER is the default.
• LEFT, RIGHT and FULL imply an OUTER JOIN.
• JOIN T1, T2 USING (a, b) is equevalant to .. ON T1.a = T2.a AND T1.b = T2.b. This is handy when joined column names are same.
• NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables.
• USING is safe for schema changes, NATURAL raises conflicting risks

Query Generics

• q1 UNION [ALL] q2, q1 INTERSECT [ALL] q2 and q1 EXCEPT [ALL] q2 can be used to combine result of two queries
• Ordering (like ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]) happens at the end of data fetching and is one of the most expensive operations
• VALUES (VALUES (1, 'one'), (2, 'two'), (3, 'three')) can be used to generate intermediate tables to ease queries
• RETURNING * give control over output of any query
• Use range data types when applicable (see link)

demo=# create table api(jdoc jsonb); CREATE TABLE demo=# CREATE INDEX idxgin ON api USING GIN (jdoc); CREATE INDEX demo=#

CTEs

CTEs can split complex queries into multiple small portions

• CTEs can be used RECURSIVE over row's own value. TIP: It is iterable, not recursion.

Performance: Indexes

• B-Tree: Use for operations like < OR <= OR = OR >= OR > together with BETWEEN, IN, IS NULL or IS NOT NULL
• Hash: Use for = operator. ie: UUID
• GiST: A combination of index algorithms. Use for ...
• GIN: Inverted index. Use for text-search, ranking ...
• BRIN: Best for repeating data sets (dates, integers, etc)

Maintenance: Backup & Respore

• SQL Dump & Restore

  • Exporting: man pg_dump (also see pg_dumpall)
  • Importing: man pg_restore (or psql {DB_NAME} < dump.file). Don't forget to ANALYZE after imports!

• File system level backup

• Continuous archiving => use cloud :)

Bonus

psql

psql [-h .. -d .. -U .. -P ..] OR psql 'dbname=.. user=.. application_name=..' OR APPNAME=.. PGPASSWORD=.. PGHOST=.. psql

• \? -> List of all available apis (like help)
• \d[+] -> show table definition
• \di -> list indexes \di <schema>.<tablename> for table specific
• \dp -> list access priviliges in tables, views, functions etc
• \g -> equavalant to ;
• \sf -> show function definition script
• \sv -> show view definition script
• \g -> equavalant to ;
• \q -> quit
• \i <filename>.sql -> execute sql file from the session
• \o <filename> -> send all query results to specified file
• \copy ... -> perform sql copy for data ingestion/extraction

Columnar

Relational databases write data to disk in a row-based aproach

Row Based Saved as tuples of row into disk. Needs a scan on disk in complex queries. select value from data where name like 'fra%'; will first fetch all the table data to memory. This is due to storage structure undertheneeth.

Name	City	...	Some Value
Frank	Berlin	...	123
...	...	...	...
Franz	Potsdam	...	321

Column Based Each column saved to disk as a block. Thus, compression is highly possible. Also, select value from data where name like 'fra%'; will fetch only data of value column.

Name	City	...	Some Value
Franz	Potsdam	...	125
Frank	col2_val	...	222
Henrik	col2_val	...	121
...	...	...	...
Zeynep	Bitlis	XYZ	111

Example

Potensial Candidates

• ClickHous: Fully opensource analytics data capable of petabyte scale queries in seconds.
• Citus Data: Fully open source Postgresql extension with distributed capacity.

Take-aways

• Delegate heavy-lifting to database server
• Minimize network latency, computing time & costs
• Reduces compelxity
• Rethink your data design! Use jsonb, generated-columns and other features
• Use columnar data storage for long-term queries (like over-yearly calculations)

HELPER QUERIES

Basic JOIN Examples

demo=# CREATE TABLE t1(num integer, name text);
demo=# INSERT INTO T1(num, name)VALUES(1, 'a'),(2, 'b'),(3, 'c');
demo=# CREATE TABLE t2(num integer, name text);
demo=# INSERT INTO T2(num, name) VALUES (1, 'xxx'),(3, 'yyy'),(5, 'zzz');
...
-- demo=# INSERT INTO T1(num, name) SELECT n, random()::text FROM generate_series(1, 10000) n;
-- demo=# INSERT INTO T2(num, name) SELECT n, random()::text FROM generate_series(1, 10000) n;

demo=# SELECT * FROM t1 CROSS JOIN t2;

demo=# SELECT * FROM t1 INNER JOIN t2 USING (num);

demo=# SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;

demo=# SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.name = 'xxx'; -- vs JOIN

Table Functions

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

Columnar DB Example (Citus Data)

Citus is an fully opensource Postgresql extension maintained by Microsoft for distributed PG.

docker run --name columnar_db -e POSTGRES_PASSWORD=skecret -e POSTGRES_DB=demo -e POSTGRES_USER=admin -p 1111:5432 citusdata/citus:11.0

docker exec -it columnar_db psql -U admin -d demo

-- Table/schema definition
CREATE TABLE IF NOT EXISTS data_table_normal(
    repo_name text not null, 
    commit_datetime timestamp not null, 
    description text, 
    payload jsonb);

CREATE TABLE IF NOT EXISTS data_table_columnar(
    repo_name text not null, 
    commit_datetime timestamp not null, 
    description text, 
    payload jsonb) USING columnar;


-- Payload generation. Generates ~43210000 Rows.
INSERT INTO data_table_columnar (repo_name, commit_datetime, description, payload)
SELECT 'repository/repo_' || i::text AS repo_name,
        commit_datetime,
        'rand description for ' || i::text AS description,
        jsonb_build_object( 'firstName', md5(random()::text), 'lastName', md5(random()::text))::JSONB
FROM generate_series(0, 10000) i,
     generate_series('2022-09-07'::TIMESTAMP, '2022-09-22'::TIMESTAMP, '5 minute'::INTERVAL) AS commit_datetime;

INSERT 0 43.214.321 - 43.210.000
Time: 147564.170 ms (02:27.564)

...

-- Columnar table

INSERT 0 43214321
Time: 199620.979 ms (03:19.621)

...
demo=# select count(*) from data_table_normal;
  count   
----------
 43214321
(1 row)

Time: 1378.463 ms (00:01.378)
demo=# SELECT pg_size_pretty( pg_total_relation_size('data_table_normal'));
 pg_size_pretty 
----------------
 7854 MB
(1 row)

Time: 0.352 ms
demo=# select count(*) from data_table_columnar;
  count   
----------
 43214321
(1 row)

Time: 2103.933 ms (00:02.104)
demo=# SELECT pg_size_pretty( pg_total_relation_size('data_table_columnar'));
 pg_size_pretty 
----------------
 1542 MB
(1 row)

Time: 0.434 ms
demo=# \d+
                                         List of relations
 Schema |        Name         | Type  | Owner | Persistence | Access method |  Size   | Descri
ption 
--------+---------------------+-------+-------+-------------+---------------+---------+-------
------
 public | citus_tables        | view  | admin | permanent   |               | 0 bytes | 
 public | data_table_columnar | table | admin | permanent   | columnar      | 1542 MB | 
 public | data_table_normal   | table | admin | permanent   | heap          | 7854 MB | 
(3 rows)

demo=# explain analyze select payload from data_table_normal ;
                                                              QUERY PLAN                      
                                        
----------------------------------------------------------------------------------------------
----------------------------------------
 Seq Scan on data_table_normal  (cost=0.00..1437128.56 rows=43214356 width=102) (actual time=2
2.704..55457.831 rows=43214321 loops=1)
 Planning Time: 0.134 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.613 ms, Inlining 2.260 ms, Optimization 12.979 ms, Emission 7.326 ms, 
Total 23.178 ms
 Execution Time: 107863.805 ms
(7 rows)

Time: 107870.451 ms (01:47.870)
demo=# 

demo=# explain analyze select payload from data_table_columnar;
                                                                      QUERY PLAN              
                                                         
----------------------------------------------------------------------------------------------
---------------------------------------------------------
 Custom Scan (ColumnarScan) on data_table_columnar  (cost=0.00..49155.68 rows=43214321 width=3
2) (actual time=19.421..61603.608 rows=43214321 loops=1)
   Columnar Projected Columns: payload
 Planning Time: 0.827 ms
 Execution Time: 113544.684 ms
(4 rows)

Time: 113545.865 ms (01:53.546)

WHERE Columnar Shines

Even though everytinh in both tables is almost identical (only value contens are different, sizes & types are same), normal table answers in 1321 miliseconds but columnar only takes 20!
A factor of 66 times faster.

demo=# explain (analyze, verbose, settings) select payload from data_table_normal where commit_datetime BETWEEN '2022-07-08' AND '2022-07-12';
                                                                                              
  QUERY PLAN                                                                                  
               
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
---------------
 Gather  (cost=1000.00..1276074.83 rows=1 width=102) (actual time=1316.101..1320.049 rows=0 lo
ops=1)
   Output: payload
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.data_table_normal  (cost=0.00..1275074.73 rows=1 width=102)
 (actual time=1278.873..1278.875 rows=0 loops=3)
         Output: payload
         Filter: ((data_table_normal.commit_datetime >= '2022-07-08 00:00:00'::timestamp witho
ut time zone) AND (data_table_normal.commit_datetime <= '2022-07-12 00:00:00'::timestamp witho
ut time zone))
         Rows Removed by Filter: 14404774
         Worker 0:  actual time=1262.761..1262.763 rows=0 loops=1
           JIT:
             Functions: 4
             Options: Inlining true, Optimization true, Expressions true, Deforming true
             Timing: Generation 0.441 ms, Inlining 35.237 ms, Optimization 17.584 ms, Emission
 11.027 ms, Total 64.290 ms
         Worker 1:  actual time=1258.086..1258.088 rows=0 loops=1
           JIT:
             Functions: 4
             Options: Inlining true, Optimization true, Expressions true, Deforming true
             Timing: Generation 0.413 ms, Inlining 33.450 ms, Optimization 17.026 ms, Emission
 10.646 ms, Total 61.536 ms
 Planning Time: 0.100 ms
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.792 ms, Inlining 71.094 ms, Optimization 52.565 ms, Emission 31.588 ms
, Total 157.038 ms
 Execution Time: 1321.065 ms
(24 rows)

Time: 1321.777 ms (00:01.322)
demo=# explain (analyze, verbose, settings) select payload from data_table_columnar where commit_datetime BETWEEN '2022-07-08' AND '2022-07-12';
                                                                                              
 QUERY PLAN                                                                                   
             
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-------------
 Custom Scan (ColumnarScan) on public.data_table_columnar  (cost=0.00..491.56 rows=216072 widt
h=32) (actual time=19.127..19.129 rows=0 loops=1)
   Output: payload
   Filter: ((data_table_columnar.commit_datetime >= '2022-07-08 00:00:00'::timestamp without t
ime zone) AND (data_table_columnar.commit_datetime <= '2022-07-12 00:00:00'::timestamp without
 time zone))
   Columnar Projected Columns: commit_datetime, payload
   Columnar Chunk Group Filters: ((commit_datetime >= '2022-07-08 00:00:00'::timestamp without
 time zone) AND (commit_datetime <= '2022-07-12 00:00:00'::timestamp without time zone))
   Columnar Chunk Groups Removed by Filter: 4322
 Planning Time: 1.034 ms
 Execution Time: 19.149 ms
(8 rows)

Time: 20.810 ms
demo=# 

B-tree Indexes

• Defaut index type.
• Mechanism behind constraint and database internals (schema/table/function etc relation uniqueness)

Below is a demonstration of aa query with and without index.
Look carefully at the query plan of second (inclusive) index!

-- Create table with 10.001.000 rows 
CREATE TABLE t_demo_2 AS
          SELECT id, val, md5(random()::text) as other 
          FROM generate_series(1, 1000) AS id, 
          generate_series(1000, 11000) AS val;

-- W/O Index
explain analyze select id from t_demo_2 where id BETEWEEN 1987 AND 2022;

-- Create index
create INDEX idx ON t_demo_2 (id) INCLUDE (val);
ANALYZE t_demo_2;
explain analyze select id, val from t_demo_2 where id BETEWEEN 1987 AND 2022;

...

DROP INDEX idx;

-- Index including extra value
create INDEX idx ON t_demo_2 (id) INCLUDE (val);
ANALYZE t_demo_2;
explain analyze select id, val from t_demo_2 where id BETEWEEN 1987 AND 2022;

BRIN Index

CREATE TABLE t_demo AS
          SELECT * FROM generate_series(1, 10000000) AS id;

-- query w/o index
explain analyze verbose  SELECT * FROM t_demo WHERE id between 32 and 120;
...
Time: 222.151 ms

-- Create index (default: b-tree)
CREATE INDEX idx_id ON t_demo (id);
ANALYZE t_demo; -- important!!

-- Run query with a predicate similar to previous one
explain analyze verbose  SELECT * FROM t_demo WHERE id between 12 and 100;
...
Time: 2.673 ms

-- Get index size
SELECT pg_size_pretty( pg_total_relation_size('idx_id'));

DROP INDEX idx_id;


CREATE INDEX idx_brn_id ON t_demo USING BRIN (id);
ANALYZE t_demo; -- important!!

-- Run query with a predicate similar to previous one
explain analyze verbose  SELECT * FROM t_demo WHERE id between 42 and 140;
...
Time: 5.512 ms

-- Get index size
SELECT pg_size_pretty( pg_total_relation_size('idx_brn_id'));

Also see helper-queries