SQL DATABASE SYSTEMS

Additional psql Meta-Commands

# PostgreSQL-specific commands
\dt+                  # List tables with size info
\d+ tablename         # Describe table with extra details
\dn                   # List schemas
\df                   # List functions
\dv                   # List views
\dx                   # List installed extensions
\o filename           # Redirect output to file
\h COMMAND            # SQL command help
\?                    # List all psql commands

Additional MySQL Commands

-- MySQL-specific commands
SHOW COLUMNS FROM tablename;       # Show column details
SHOW CREATE TABLE tablename;       # Show CREATE statement
SHOW FULL TABLES;                  # List tables with type (BASE/VIEW)
SHOW PROCESSLIST;                  # Show running queries
SHOW VARIABLES LIKE 'pattern%';    # Show server variables
SHOW STATUS LIKE 'pattern%';       # Show server status

PostgreSQL Connection Setup

# Standard connection format
psql "postgresql://username:password@hostname:port/dbname"

# With SSL
psql "postgresql://username:password@hostname:port/dbname?sslmode=require"

# Command-line options
psql -h hostname -p 5432 -U username -d dbname

Environment Variables:

export PGHOST=localhost
export PGPORT=5432
export PGUSER=myuser
export PGPASSWORD=mypass        # Not recommended for security
export PGDATABASE=mydb
export PGSSLMODE=require
export PGPASSFILE=~/.pgpass     # Path to password file

~/.pgpass Password File:

# Format: hostname:port:database:username:password
localhost:5432:mydb:myuser:secret123
*:5432:*:myuser:secret123       # Wildcard support

# Set permissions (required)
chmod 0600 ~/.pgpass

MySQL Connection Setup

# Standard format
mysql -h hostname -P 3306 -u username -p dbname

# URI format (MySQL 8.0+)
mysql mysql://username:password@hostname:port/dbname

~/.my.cnf Configuration File:

[client]
user=myuser
password=secret123
host=localhost
port=3306
database=mydb

[mysql]
no-auto-rehash
connect_timeout=2

# Set permissions (required)
chmod 700 ~/.my.cnf

Create & Drop Databases

CREATE DATABASE mydb;
CREATE DATABASE mydb
  WITH ENCODING 'UTF8'
  LC_COLLATE='en_US.UTF-8';
CREATE DATABASE mydb
  OWNER myuser
  TEMPLATE template0;

CREATE DATABASE mydb;
CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

Users, Roles & Permissions

PostgreSQL User Management:

-- Create user/role (users and roles are the same in PostgreSQL)
CREATE USER myuser WITH PASSWORD 'secret123';
CREATE ROLE myrole WITH LOGIN PASSWORD 'secret123';
CREATE ROLE admin WITH CREATEDB CREATEROLE;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT SELECT, INSERT, UPDATE ON TABLE users TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT USAGE ON SCHEMA public TO myuser;
GRANT EXECUTE ON FUNCTION myfunc() TO myuser;

-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;
REVOKE INSERT ON TABLE users FROM myuser CASCADE;

-- Role membership (group roles)
CREATE ROLE developers;
GRANT developers TO alice, bob;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO developers;

-- Alter user
ALTER USER myuser WITH PASSWORD 'newpass';
ALTER ROLE myuser WITH SUPERUSER;

-- Drop user
DROP USER myuser;
DROP ROLE IF EXISTS myrole;

MySQL User Management:

-- Create user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'secret123';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'secret123';  -- Any host

-- Grant privileges
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'myuser'@'localhost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'myuser'@'localhost';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';
REVOKE INSERT ON mydb.users FROM 'myuser'@'localhost';

-- Alter user
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpass';

-- Drop user
DROP USER 'myuser'@'localhost';
DROP USER IF EXISTS 'myuser'@'localhost';

-- View privileges
SHOW GRANTS FOR 'myuser'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

Backup & Restore

PostgreSQL pg_dump / pg_restore:

# Dump database (SQL format)
pg_dump -U username -h localhost dbname > backup.sql
pg_dump -U username -d dbname -F p -f backup.sql

# Dump database (custom format - compressed)
pg_dump -U username -d dbname -F c -f backup.dump

# Dump specific tables
pg_dump -U username -d dbname -t table1 -t table2 > tables.sql

# Dump schema only
pg_dump -U username -d dbname --schema-only > schema.sql

# Dump data only
pg_dump -U username -d dbname --data-only > data.sql

# Dump all databases
pg_dumpall -U postgres > all_databases.sql

# Restore from SQL dump
psql -U username -d dbname < backup.sql

# Restore from custom format
pg_restore -U username -d dbname backup.dump
pg_restore -U username -d dbname -C backup.dump  # Create database
pg_restore -U username -d dbname -j 4 backup.dump  # Parallel (4 jobs)

MySQL mysqldump:

# Dump database
mysqldump -u username -p dbname > backup.sql
mysqldump -u username -p --databases db1 db2 > backup.sql

# Dump all databases
mysqldump -u username -p --all-databases > all_databases.sql

# Dump specific tables
mysqldump -u username -p dbname table1 table2 > tables.sql

# Dump schema only
mysqldump -u username -p --no-data dbname > schema.sql

# Dump data only
mysqldump -u username -p --no-create-info dbname > data.sql

# Dump with additional options
mysqldump -u username -p --single-transaction --routines --triggers dbname > backup.sql

# Ignore specific tables
mysqldump -u username -p dbname --ignore-table=dbname.table1 > backup.sql

# Restore from dump
mysql -u username -p dbname < backup.sql
mysql -u username -p < all_databases.sql

Create Tables

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Table with Constraints:

-- PostgreSQL
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(10, 2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT valid_status CHECK (status IN ('pending', 'paid', 'shipped'))
);

-- MySQL
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT valid_status CHECK (status IN ('pending', 'paid', 'shipped'))
) ENGINE=InnoDB;

Data Types Comparison

Alter Tables

SELECT Statements Both

-- Basic SELECT
SELECT * FROM users;
SELECT id, username, email FROM users;
SELECT DISTINCT country FROM users;

-- WHERE clause
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE country = 'USA' AND is_active = TRUE;
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');
SELECT * FROM users WHERE phone IS NULL;

-- ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY country ASC, username ASC;

-- LIMIT and OFFSET
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Skip 20, get next 10

JOIN Operations Both

-- INNER JOIN
SELECT u.username, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- RIGHT JOIN
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- CROSS JOIN
SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;

-- Self JOIN
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Multiple JOINs
SELECT u.username, o.total, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

GROUP BY & Aggregates Both

-- GROUP BY
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country;

-- Multiple grouping columns
SELECT country, city, COUNT(*) as count
FROM users
GROUP BY country, city;

-- GROUP BY with aggregates
SELECT
    user_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent,
    AVG(total) as avg_order,
    MAX(total) as max_order,
    MIN(total) as min_order
FROM orders
GROUP BY user_id;

-- HAVING clause (filter after grouping)
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

-- HAVING with multiple conditions
SELECT user_id, SUM(total) as total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000 AND COUNT(*) > 5
ORDER BY total_spent DESC;

String Aggregation:

-- PostgreSQL
SELECT STRING_AGG(username, ', ') FROM users;

-- MySQL
SELECT GROUP_CONCAT(username SEPARATOR ', ') FROM users;

INSERT

-- Single row (both databases)
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- Multiple rows
INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

-- INSERT from SELECT
INSERT INTO users_archive
SELECT * FROM users WHERE created_at < '2020-01-01';

-- INSERT with RETURNING
INSERT INTO users (username, email)
VALUES ('dave', 'dave@example.com')
RETURNING id, username, created_at;

-- Get last inserted ID
INSERT INTO users (username, email)
VALUES ('dave', 'dave@example.com');
SELECT LAST_INSERT_ID();

-- INSERT with DEFAULT values
INSERT INTO users DEFAULT VALUES;

-- INSERT IGNORE (skip duplicates)
INSERT IGNORE INTO users (username, email)
VALUES ('john', 'john@example.com');

UPDATE Both

-- Basic UPDATE
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET email = 'new@example.com', is_active = FALSE
WHERE id = 1;

-- Update with calculation
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

Update with JOIN:

UPDATE orders o
SET status = 'completed'
FROM users u
WHERE o.user_id = u.id
  AND u.country = 'USA';

UPDATE orders o
INNER JOIN users u ON o.user_id = u.id
SET o.status = 'completed'
WHERE u.country = 'USA';

DELETE Both

-- Basic DELETE
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM users WHERE created_at < '2020-01-01';

-- Delete all rows
DELETE FROM users;
TRUNCATE TABLE users;  -- Faster, resets auto-increment

UPSERT (Insert or Update)

PostgreSQL ON CONFLICT:

-- Insert or do nothing
INSERT INTO users (id, username, email)
VALUES (1, 'john', 'john@example.com')
ON CONFLICT (id) DO NOTHING;

-- Insert or update (UPSERT)
INSERT INTO users (id, username, email)
VALUES (1, 'john', 'newemail@example.com')
ON CONFLICT (id)
DO UPDATE SET
    email = EXCLUDED.email,
    updated_at = CURRENT_TIMESTAMP;

-- UPSERT with WHERE clause
INSERT INTO users (username, email)
VALUES ('john', 'john@example.com')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
WHERE users.email != EXCLUDED.email;

-- UPSERT with RETURNING
INSERT INTO users (username, email)
VALUES ('john', 'john@example.com')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id, username, email;

MySQL ON DUPLICATE KEY UPDATE:

-- Insert or update
INSERT INTO users (id, username, email)
VALUES (1, 'john', 'newemail@example.com')
ON DUPLICATE KEY UPDATE
    email = VALUES(email),
    updated_at = CURRENT_TIMESTAMP;

-- Using aliases (MySQL 8.0.19+)
INSERT INTO products (id, name, stock)
VALUES (1, 'Widget', 10) AS new
ON DUPLICATE KEY UPDATE
    stock = stock + new.stock;

-- INSERT IGNORE (insert or skip)
INSERT IGNORE INTO users (username, email)
VALUES ('john', 'john@example.com');

-- REPLACE (delete and insert)
REPLACE INTO users (id, username, email)
VALUES (1, 'john', 'john@example.com');

Bulk Data Loading

PostgreSQL COPY:

-- Export to CSV
COPY users TO '/tmp/users.csv' CSV HEADER;
COPY (SELECT * FROM users WHERE country = 'USA') TO '/tmp/usa_users.csv' CSV;

-- Import from CSV
COPY users FROM '/tmp/users.csv' CSV HEADER;
COPY users (username, email) FROM '/tmp/users.csv' CSV;

-- psql meta-command (client-side COPY)
\copy users TO 'users.csv' CSV HEADER
\copy users FROM 'users.csv' CSV HEADER

MySQL LOAD DATA:

-- Import from CSV
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Import with column mapping
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
(username, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d');

Common Table Expressions (CTEs) Both

-- Simple CTE
WITH active_users AS (
    SELECT * FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE country = 'USA';

-- Multiple CTEs
WITH
    active_users AS (
        SELECT * FROM users WHERE is_active = TRUE
    ),
    recent_orders AS (
        SELECT * FROM orders WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
    )
SELECT u.username, COUNT(o.id) as order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- Recursive CTE (organizational hierarchy)
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level employees
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees reporting to previous level
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Window Functions Both

-- ROW_NUMBER: unique sequential number
SELECT
    username,
    email,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;

-- RANK & DENSE_RANK: ranking with gaps / without gaps
SELECT
    username,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM users;

-- PARTITION BY: window per group
SELECT
    country,
    username,
    score,
    RANK() OVER (PARTITION BY country ORDER BY score DESC) as country_rank
FROM users;

-- Running totals
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Moving average (last 7 rows)
SELECT
    date,
    price,
    AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7
FROM stock_prices;

-- LAG & LEAD: access previous/next rows
SELECT
    date,
    price,
    LAG(price, 1) OVER (ORDER BY date) as prev_price,
    LEAD(price, 1) OVER (ORDER BY date) as next_price,
    price - LAG(price, 1) OVER (ORDER BY date) as price_change
FROM stock_prices;

-- NTILE: divide into buckets
SELECT
    username,
    score,
    NTILE(4) OVER (ORDER BY score DESC) as quartile
FROM users;

Subqueries Both

-- Scalar subquery (returns single value)
SELECT username, email
FROM users
WHERE id = (SELECT user_id FROM orders ORDER BY total DESC LIMIT 1);

-- IN subquery
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE total > 1000);

-- EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Correlated subquery
SELECT
    u.username,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

-- Subquery in FROM clause
SELECT country, AVG(user_count) as avg_users_per_city
FROM (
    SELECT country, city, COUNT(*) as user_count
    FROM users
    GROUP BY country, city
) AS city_counts
GROUP BY country;

Set Operations

-- UNION (remove duplicates) - Both databases
SELECT username FROM users WHERE country = 'USA'
UNION
SELECT username FROM users WHERE country = 'Canada';

-- UNION ALL (keep duplicates) - Both databases
SELECT username FROM users WHERE country = 'USA'
UNION ALL
SELECT username FROM users WHERE country = 'Canada';

PostgreSQL Set Operations:

-- INTERSECT (PostgreSQL only)
SELECT username FROM users WHERE is_active = TRUE
INTERSECT
SELECT username FROM users WHERE country = 'USA';

-- EXCEPT (PostgreSQL only)
SELECT username FROM users
EXCEPT
SELECT username FROM banned_users;

MySQL Alternatives:

-- INTERSECT alternative (MySQL)
SELECT DISTINCT u1.username
FROM users u1
INNER JOIN users u2 ON u1.username = u2.username
WHERE u1.is_active = TRUE AND u2.country = 'USA';

-- EXCEPT alternative (MySQL)
SELECT u1.username
FROM users u1
LEFT JOIN banned_users bu ON u1.username = bu.username
WHERE bu.username IS NULL;

PostgreSQL Index Types

-- B-tree index (default, most common)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(username);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Multi-column index
CREATE INDEX idx_users_country_city ON users(country, city);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users_email ON users(email) WHERE is_active = TRUE;
CREATE INDEX idx_recent_orders ON orders(user_id)
    WHERE created_at > '2023-01-01';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_users_year ON users(EXTRACT(YEAR FROM created_at));

-- GIN index (for arrays, JSONB, full-text search)
CREATE INDEX idx_users_tags ON users USING GIN(tags);  -- tags is an array
CREATE INDEX idx_products_data ON products USING GIN(data);  -- data is JSONB
CREATE INDEX idx_documents_search ON documents USING GIN(to_tsvector('english', content));

-- GiST index (for geometric data, full-text search)
CREATE INDEX idx_locations_point ON locations USING GIST(coordinates);

-- Hash index (equality comparisons only)
CREATE INDEX idx_users_id_hash ON users USING HASH(id);

-- BRIN index (block range index, for very large tables)
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- Covering index (include non-key columns)
CREATE INDEX idx_users_username_incl ON users(username) INCLUDE (email, created_at);

-- Concurrent index creation (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Drop index
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;

MySQL Index Types

-- B-tree index (default, most common)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(username);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
ALTER TABLE users ADD UNIQUE KEY idx_email (email);

-- Multi-column index
CREATE INDEX idx_users_country_city ON users(country, city);

-- Prefix index (index first N characters)
CREATE INDEX idx_users_email_prefix ON users(email(10));

-- FULLTEXT index (for full-text search)
CREATE FULLTEXT INDEX idx_products_description ON products(description);
CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);

-- Spatial index (for geometric data)
CREATE SPATIAL INDEX idx_locations_point ON locations(coordinates);

-- Descending index (MySQL 8.0+)
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);

-- Invisible index (MySQL 8.0+, index exists but not used)
CREATE INDEX idx_users_phone ON users(phone) INVISIBLE;
ALTER TABLE users ALTER INDEX idx_users_phone VISIBLE;

-- Drop index
DROP INDEX idx_users_email ON users;
ALTER TABLE users DROP INDEX idx_users_email;

Index Management

-- List indexes
\di  -- In psql
SELECT * FROM pg_indexes
WHERE tablename = 'users';

-- Index size
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'users';

-- Rebuild index
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Check index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- List indexes
SHOW INDEXES FROM users;

-- Index statistics
SELECT
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'users';

-- Analyze table (update index statistics)
ANALYZE TABLE users;

-- Optimize table (rebuild indexes)
OPTIMIZE TABLE users;

EXPLAIN & Query Optimization

PostgreSQL EXPLAIN ANALYZE:

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN ANALYZE (actually runs the query)
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- JSON output
EXPLAIN (FORMAT JSON) SELECT * FROM users;

MySQL EXPLAIN:

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN ANALYZE (MySQL 8.0.18+, actually runs query)
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Extended EXPLAIN
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

PostgreSQL JSON/JSONB

-- Create table with JSONB
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    data JSONB
);

-- Insert JSON data
INSERT INTO products (name, data) VALUES
    ('Widget', '{"price": 19.99, "tags": ["electronics", "gadget"], "stock": 100}'),
    ('Gadget', '{"price": 29.99, "tags": ["electronics"], "stock": 50}');

-- Query JSON fields
SELECT * FROM products WHERE data->>'price' = '19.99';
SELECT * FROM products WHERE (data->>'stock')::int > 50;

-- JSON operators
-- -> : Get JSON object field (returns JSON)
-- ->> : Get JSON object field as text
-- #> : Get JSON object at path (returns JSON)
-- #>> : Get JSON object at path as text
-- @> : Contains (left JSON contains right)
-- ? : Key exists

SELECT name, data->'price' FROM products;  -- Returns JSON
SELECT name, data->>'price' FROM products;  -- Returns text
SELECT name, data#>'{tags,0}' FROM products;  -- First tag

-- Check if key exists
SELECT * FROM products WHERE data ? 'price';
SELECT * FROM products WHERE data ?& ARRAY['price', 'stock'];

-- Contains queries
SELECT * FROM products WHERE data @> '{"tags": ["electronics"]}';
SELECT * FROM products WHERE data->'tags' @> '["gadget"]';

-- Update JSON
UPDATE products
SET data = data || '{"featured": true}'
WHERE name = 'Widget';

UPDATE products
SET data = jsonb_set(data, '{price}', '24.99')
WHERE name = 'Widget';

-- Delete JSON key
UPDATE products SET data = data - 'featured';

-- Create index for JSON queries
CREATE INDEX idx_products_data ON products USING GIN(data);
CREATE INDEX idx_products_price ON products ((data->>'price'));

PostgreSQL Full-Text Search

-- Create table with text search
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);

-- Basic full-text search
SELECT * FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgresql & database');

-- Create tsvector column and index
UPDATE documents
SET search_vector = to_tsvector('english', title || ' ' || content);

CREATE INDEX idx_documents_search ON documents USING GIN(search_vector);

-- Query with tsvector column
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'postgresql & database');

-- Ranking results
SELECT
    title,
    ts_rank(search_vector, query) as rank
FROM documents,
     to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Prefix search
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'postgre:*');

-- Auto-update search_vector with trigger
CREATE FUNCTION documents_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON documents FOR EACH ROW EXECUTE FUNCTION documents_search_trigger();

MySQL JSON

-- Create table with JSON
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    data JSON
);

-- Insert JSON data
INSERT INTO products (name, data) VALUES
    ('Widget', '{"price": 19.99, "tags": ["electronics", "gadget"], "stock": 100}'),
    ('Gadget', '{"price": 29.99, "tags": ["electronics"], "stock": 50}');

-- Query JSON fields
SELECT * FROM products WHERE JSON_EXTRACT(data, '$.price') = 19.99;
SELECT * FROM products WHERE data->>'$.price' = '19.99';  -- MySQL 5.7+
SELECT * FROM products WHERE JSON_UNQUOTE(data->'$.stock') > 50;

-- JSON operators (MySQL 5.7+)
-- -> : Get JSON value (returns JSON with quotes)
-- ->> : Get JSON value and unquote

SELECT name, data->'$.price' FROM products;
SELECT name, data->>'$.price' FROM products;

-- JSON functions
SELECT JSON_KEYS(data) FROM products;
SELECT JSON_LENGTH(data->'$.tags') FROM products;
SELECT JSON_TYPE(data->'$.price') FROM products;

-- JSON array operations
SELECT * FROM products WHERE JSON_CONTAINS(data->'$.tags', '"electronics"');

-- Update JSON
UPDATE products
SET data = JSON_SET(data, '$.featured', TRUE)
WHERE name = 'Widget';

UPDATE products
SET data = JSON_INSERT(data, '$.discount', 10)
WHERE name = 'Widget';

UPDATE products
SET data = JSON_REPLACE(data, '$.price', 24.99)
WHERE name = 'Widget';

-- Remove JSON key
UPDATE products
SET data = JSON_REMOVE(data, '$.featured')
WHERE name = 'Widget';

-- Create index (functional index on JSON path, MySQL 8.0.13+)
CREATE INDEX idx_products_price ON products ((CAST(data->>'$.price' AS DECIMAL(10,2))));

-- Virtual generated column (alternative for indexing)
ALTER TABLE products ADD COLUMN price DECIMAL(10,2) AS (data->>'$.price');
CREATE INDEX idx_products_price ON products(price);

MySQL Full-Text Search

-- Create FULLTEXT index
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT KEY idx_fulltext (title, content)
) ENGINE=InnoDB;

-- Natural language search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL database');

-- Boolean search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- Boolean operators:
-- + : Must be present
-- - : Must not be present
-- * : Wildcard
-- " " : Phrase

-- Wildcard search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('data*' IN BOOLEAN MODE);

-- Phrase search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"MySQL database"' IN BOOLEAN MODE);

-- Ranking results
SELECT
    title,
    MATCH(title, content) AGAINST('MySQL database') as relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL database')
ORDER BY relevance DESC;

PostgreSQL Administration

-- VACUUM (reclaim space from dead tuples)
VACUUM;                      -- All tables
VACUUM users;                -- Specific table
VACUUM VERBOSE users;        -- With progress
VACUUM FULL users;           -- Aggressive, locks table, rewrites entire table
VACUUM ANALYZE users;        -- Vacuum + update statistics

-- ANALYZE (update statistics for query planner)
ANALYZE;                     -- All tables
ANALYZE users;               -- Specific table

-- Active connections
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start
FROM pg_stat_activity
WHERE state = 'active';

-- Kill connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

-- Database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- Table sizes
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Cache hit ratio
SELECT
    sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) as cache_hit_ratio
FROM pg_stat_database;

MySQL Administration

-- SHOW STATUS (server statistics)
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Key status variables
SHOW STATUS LIKE 'Connections';              -- Total connection attempts
SHOW STATUS LIKE 'Threads_connected';        -- Current connections
SHOW STATUS LIKE 'Slow_queries';             -- Slow query count
SHOW STATUS LIKE 'Uptime';                   -- Server uptime in seconds

-- SHOW VARIABLES (server configuration)
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- SHOW PROCESSLIST (active connections)
SHOW FULL PROCESSLIST;

-- Kill connection
KILL 12345;  -- Kill query
KILL CONNECTION 12345;  -- Kill connection

-- Database size
SELECT
    table_schema as 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema;

-- Table sizes
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as 'Size (MB)',
    table_rows
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;

Performance Diagnostics

PostgreSQL Long-running queries:

SELECT
    pid,
    now() - query_start as duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

MySQL Long-running queries:

SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
AND TIME > 300
ORDER BY TIME DESC;

Replication

PostgreSQL Streaming Replication:

-- Create replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';

-- Check replication status (on primary)
SELECT * FROM pg_stat_replication;

-- Check replication lag
SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as send_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as replay_lag
FROM pg_stat_replication;

MySQL Replication:

-- Create replication user (on master)
CREATE USER 'replicator'@'%' IDENTIFIED BY 'secret123';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

-- Get master status
SHOW MASTER STATUS;

-- Start replication (on slave)
START SLAVE;

-- Check replication status
SHOW SLAVE STATUS\G

-- Stop replication
STOP SLAVE;

Performance Tuning

PostgreSQL Configuration Tips:

# postgresql.conf recommendations
shared_buffers = 25% of RAM (up to 40%)
effective_cache_size = 50-75% of RAM
work_mem = Total RAM / max_connections / 2-4
maintenance_work_mem = RAM / 16
max_connections = Based on application needs (use pgbouncer for pooling)
random_page_cost = 1.1 for SSD, 4.0 for HDD

MySQL Configuration Tips:

# my.cnf recommendations
innodb_buffer_pool_size = 70-80% of RAM
innodb_log_file_size = 256M - 2G
max_connections = Based on application needs
innodb_flush_log_at_trx_commit = 2 (for better performance, less durability)
query_cache_type = OFF (deprecated in MySQL 5.7.20, removed in 8.0)

Common Pitfalls

Enhanced CLI Tools

# pgcli (PostgreSQL) - Enhanced CLI with auto-completion
pip install pgcli
pgcli -h localhost -U username -d dbname

# mycli (MySQL) - Enhanced CLI with auto-completion
pip install mycli
mycli -h localhost -u username -D dbname

# Features for both:
# - Auto-completion (tables, columns, keywords)
# - Syntax highlighting
# - Multi-line editing
# - Query history
# - Pretty formatting

Connection Pooling with PgBouncer

# Install (Ubuntu/Debian)
sudo apt-get install pgbouncer

# Configure /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

# Connect through PgBouncer
psql -h localhost -p 6432 -U username -d mydb

# Pool modes:
# - session: Connection held until client disconnects
# - transaction: Connection held during transaction
# - statement: Connection held during statement (not recommended)

Transaction Management Both

-- Basic transaction (both databases)
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Error occurs
ROLLBACK;

-- Savepoints
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    SAVEPOINT my_savepoint;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    ROLLBACK TO my_savepoint;
COMMIT;

-- Transaction isolation levels (PostgreSQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Transaction isolation levels (MySQL)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- Default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Migration Tools

pgloader mysql://user:pass@localhost/mydb postgresql://user:pass@localhost/mydb