https://www.postgresql.org/docs/current/
https://www.postgresql.org/docs/current/sql-createdatabase.html
https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
https://www.postgresql.org/docs/current/user-manag.html
https://www.postgresql.org/docs/current/ddl-schemas.html
https://www.postgresql.org/docs/current/sql-createrole.html
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
https://www.postgresql.org/docs/current/runtime-config.html
https://www.postgresql.org/docs/current/monitoring-stats.html
https://www.postgresql.org/docs/current/backup.html
https://www.postgresql.org/docs/current/high-availability.html

PostgreSQL 是 relational database:
    strong SQL
    ACID transaction
    MVCC
    index / constraint / foreign key
    JSONB / full-text search / extension
    logical replication / physical streaming replication

适合:
    OLTP system
    order / payment / user / inventory
    relational data with strong consistency
    reporting query on moderate data size
    application that needs SQL correctness

不适合:
    unbounded analytical scan on massive data without warehouse design
    extremely high write fan-out without partition / queue / sharding plan
    schema-less dumping ground
    using one primary as unlimited scale point

核心原则:
    design schema and indexes with access pattern
    transaction boundary should match business invariant
    avoid long-running transactions
    every production DB needs backup restore test
    monitor connection count / lock / replication lag / bloat
    use least privilege roles
    migration must be reviewed and reversible when possible

DBA 是什么:
    PostgreSQL 原生 CREATE DATABASE property 里没有叫 DBA 的字段。
    官方概念是 owner / role / privilege。

    很多管理平台或云数据库控制台会显示 DBA:
        usually means database administrator
        sometimes means the business owner of this database
        sometimes maps to PostgreSQL database owner role
        sometimes maps to an ops team / account outside PostgreSQL

    实战理解:
        owner:
            PostgreSQL 内部的 owning role
            can alter/drop database if it has required permissions

        DBA:
            人或团队的职责标签
            responsible for backup, access review, migration review, performance, incident
            should not be confused with application runtime user

    推荐:
        database owner role:
            dba_order

        migration role:
            order_migrator

        application role:
            order_app

        read-only role:
            order_readonly

tablespace 是什么:
    tablespace 是 PostgreSQL 里的 storage location abstraction。
    它把 database object 的 physical files 放到指定 filesystem directory。

    常见用途:
        put large index on faster disk
        separate hot table / cold archive data
        isolate IO for a specific workload
        manage storage layout on self-managed PostgreSQL

    注意:
        tablespace 不是 schema
        tablespace 不是 database
        tablespace 不是 backup boundary
        moving objects to another tablespace still needs operation planning
        managed cloud database may restrict custom tablespace usage

    默认:
        如果 CREATE DATABASE 不指定 TABLESPACE，
        database uses template database's tablespace,
        usually pg_default.

CREATE ROLE dba_order LOGIN;
CREATE ROLE order_migrator LOGIN;
CREATE ROLE order_app LOGIN;
CREATE ROLE order_readonly LOGIN;

CREATE DATABASE orders
  OWNER dba_order
  TEMPLATE template0
  ENCODING 'UTF8'
  LC_COLLATE 'C'
  LC_CTYPE 'C'
  TABLESPACE pg_default
  CONNECTION LIMIT 200;

production notes:
    owner should not be application runtime user
    app role should get only needed privileges
    connection limit protects the cluster from one database consuming all connections
    locale/collation should be chosen before production data is loaded

config notes:
    do not copy memory values blindly
    shared_buffers / work_mem depend on RAM and concurrency
    work_mem is per operation, not global
    log slow query before guessing indexes
    max_connections should be controlled with pooler when app count grows

auth rules:
    prefer scram-sha-256
    require TLS for remote production connections
    keep CIDR narrow
    avoid trust in production

database:
    top-level logical database
    connection targets one database

schema:
    namespace inside database
    groups tables / views / functions
    common examples:
        public
        app
        audit
        reporting

table:
    rows and columns

tablespace:
    storage location for physical files

CREATE SCHEMA app AUTHORIZATION dba_order;
CREATE SCHEMA audit AUTHORIZATION dba_order;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE app.orders (
  id uuid PRIMARY KEY,
  user_id text NOT NULL,
  status text NOT NULL,
  amount_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE app.orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('PENDING', 'PAID', 'CANCELLED'));

ALTER TABLE app.orders
  ADD CONSTRAINT orders_amount_positive
  CHECK (amount_cents > 0);

constraint principles:
    database should enforce invariants
    application validation is not enough
    use NOT NULL / CHECK / UNIQUE / FK where they represent real business rules

CREATE INDEX orders_user_created_idx
  ON app.orders (user_id, created_at DESC);

CREATE INDEX orders_status_created_idx
  ON app.orders (status, created_at DESC);

index principles:
    index supports query shape
    compound index order matters
    index speeds reads but slows writes
    unused indexes cost storage and write IO
    review EXPLAIN before adding random indexes

BEGIN;

UPDATE app.orders
SET status = 'PAID', updated_at = now()
WHERE id = '00000000-0000-0000-0000-000000000001'
  AND status = 'PENDING';

INSERT INTO audit.order_events(order_id, event_type, created_at)
VALUES ('00000000-0000-0000-0000-000000000001', 'ORDER_PAID', now());

COMMIT;

transaction rules:
    keep transaction short
    do not wait for external API inside transaction
    update rows in consistent order to reduce deadlock
    handle serialization / deadlock retry in application

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM app.orders
WHERE user_id = 'user_1'
ORDER BY created_at DESC
LIMIT 20;

read plan:
    actual time
    rows estimated vs actual
    buffers hit/read
    index scan vs seq scan
    sort / hash aggregate memory

connection principles:
    app should use pool
    too many connections hurt memory and scheduling
    use PgBouncer when many app instances connect to one DB
    set statement_timeout / idle_in_transaction_session_timeout

ALTER ROLE order_app SET statement_timeout = '30s';
ALTER ROLE order_app SET idle_in_transaction_session_timeout = '60s';

roles:
    postgres:
        emergency superuser only

    dba_order:
        database owner / DBA role

    order_migrator:
        migration role
        owns schema changes

    order_app:
        runtime role
        least privilege DML

    order_readonly:
        read-only reporting / support

REVOKE ALL ON DATABASE orders FROM PUBLIC;
GRANT CONNECT ON DATABASE orders TO order_app, order_readonly;

GRANT USAGE ON SCHEMA app TO order_app, order_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO order_app;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO order_readonly;

security checklist:
    no app superuser
    no shared DBA password in app config
    TLS enabled for remote connections
    pg_hba.conf reviewed
    default PUBLIC privileges reviewed
    secrets in secret manager
    audit privileged access

backup types:
    logical backup:
        pg_dump / pg_restore
        good for smaller DB / object-level migration

    physical backup:
        base backup + WAL archive
        required for point-in-time recovery

    managed cloud snapshot:
        convenient but restore test still required

pg_dump \
  --format=custom \
  --file=orders.dump \
  --dbname=postgresql://dba_order@db.example.com/orders

pg_restore \
  --clean \
  --if-exists \
  --dbname=postgresql://dba_order@restore.example.com/orders \
  orders.dump

reliability checklist:
    backup schedule defined
    WAL archive enabled if PITR is required
    restore test automated
    replica lag monitored
    failover runbook documented
    maintenance window defined

-- active sessions
SELECT pid, usename, datname, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = 'orders';

-- database stats
SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit
FROM pg_stat_database
WHERE datname = 'orders';

-- table bloat signals
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

metrics:
    connections / active sessions
    TPS / commit / rollback
    cache hit ratio
    slow query count
    lock wait
    dead tuples / autovacuum
    replication lag
    disk usage / disk latency
    WAL generation rate

alerts:
    connection usage > 80%
    replication lag high
    disk free low
    long transaction age high
    lock wait spike
    autovacuum not keeping up
    backup failed

docker run --rm --name pg-dev \
  -e POSTGRES_DB=orders \
  -e POSTGRES_USER=dba_order \
  -e POSTGRES_PASSWORD=dba_password \
  -p 5432:5432 \
  postgres:16

psql "postgresql://dba_order:dba_password@localhost:5432/orders"

SELECT
  d.datname AS database_name,
  pg_catalog.pg_get_userbyid(d.datdba) AS owner,
  d.datconnlimit AS connection_limit,
  t.spcname AS tablespace,
  d.datcollate AS collate,
  d.datctype AS ctype
FROM pg_database d
JOIN pg_tablespace t ON t.oid = d.dattablespace
WHERE d.datname = current_database();

interpretation:
    owner:
        PostgreSQL internal database owner role

    tablespace:
        default storage location for objects created in this database

    dba:
        if your UI shows DBA, map it back to owner/admin responsibility

CREATE SCHEMA app AUTHORIZATION dba_order;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE app.orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id text NOT NULL,
  status text NOT NULL CHECK (status IN ('PENDING', 'PAID', 'CANCELLED')),
  amount_cents integer NOT NULL CHECK (amount_cents > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX orders_user_created_idx
  ON app.orders (user_id, created_at DESC);

database properties:
    owner / DBA responsibility documented
    application user is not owner
    tablespace decision documented
    encoding / collation chosen before production
    connection limit set

schema:
    schema names clear
    constraints enforce invariants
    indexes match query patterns
    migrations reviewed

security:
    least privilege roles
    TLS enabled
    pg_hba.conf reviewed
    PUBLIC privileges revoked where needed
    secrets managed outside git

reliability:
    backups enabled
    restore tested
    replication lag monitored
    failover runbook exists

operations:
    slow query logging enabled
    pg_stat_activity dashboard exists
    lock wait / long transaction alert exists
    autovacuum health monitored