PostgreSQL Training Notes

PostgreSQL_Training_Notes.md

##Config ###Recovery Mode (Single User Mode, --single)

• no network
• skips auth

###About pstgresql.conf <- Base Config pstgresql.auto.conf <- Overrides base config

• Set with ALTER SYSTEM 9.4+

pg_hba.conf - Auth conf pg_ctl - reloads conf

###shared_buffers

• Increase Shared Buffers in postgresql.conf (1/4 of Mem but, don't exceed 8gb)

###checkpoint_segments (9.5+) -max_wal_size Max size of WAL between checkpoints (soft limit) -min_wal_size (default: 80MB)

###Logs Logs to sdout by default

log_min_duration_statement

• log queries that take longer than N ms
• off by default (-1)
• log all (0)

statement_timeout - timeout statements that take to long to execute

###Network max_connections

• default 100

superuser_reserved_connections

• defaults 3
• need to connect as (cannot connect as regular and change to super)

i.e. default 100 - 3 = 97 for regular users

###Planner This is the search strategy that defines the estimated cost of the opperations so that the db can decide the most efficient search route.

###Write Ahead Log fsync - leave it alone wal_buffers - buffer before writing to WAL, should not be larger than a WAL file

##Authentication Roles - (USERS + GROUPS) pg_hba.conf SQL cmds GRANT REVOKE

CREATE ROLE username; <- Non-login role CREATE ROLE username WITH LOGIN; or CREATE USER username; <- Creates ROLE and USER that can login CREATE USER username WITH PASSWORD 'password';

echo -n '<username>'password | md5sum <- encrypted password ALTER ROLE username WITH ENCRYPTED PASSWORD '<encrypted password>';

CREATE ROLE admin WITH SUPERUSER;

##Privileges Truncate delete data from table, does not need to be table owner. TEMP create temporary table.

PUBLIC grant to everyone (without exception). syn for everyone.

Only owner (and superuser) can change it. -Maybe out app should utilize superusers since tables can only have a single owner.

\dp

<- view permissions for object.

There are also Column based permissions, but may interact with table-wide permissions.

##Debug \set ON_ERROR_STOP stops execution of statements if error

##Statement INSERT - Standard sql insert statement.

COPY

• One Statement with CSV. Must be in order. If you want to use a file, the file must be on the same server as the database.
• A work around to the location of the file is to cat the file and pipe it to psql. cat file.csv | psql -c "COPY <table name> FROM STDIN;"
• Can also be used to execute arbitrary SQL statements: COPY (SELECT a, b, FROM table WHERE...) TO '/path/to/file.csv';

##UploadingData

Speed up

• Remove indexes
• Remove foreign key constraints
• increase maintenance_work_mem
• increase checkpoint_segments
• disable WAL archiving and streaming replication

• wal_level = minimal
• archive_mode = off
• max_wal_senders = 0

• Read/Write from different disks

Run ANALYZE after uploading data

##Transactions ###Savepoints You can rollback to a savepoint within a transaction.

SAVEPOINT <name>; ROLLBACK TO <name>; RELEASE SAVEPOINT <name>;

##Time & Timezones Time stops in a transaction

##VACUUM Cleans up rows that have been updated. PG has auto vacuum, best to leave it alone.

##Data Types Floats can store 'Nan', 'Infinite', and '-Infinite'

• NaN can represent "5/0"

TEXT, CHAR, VARCHAR

• Text data types are all the same under the hood.
• The difference is that char and varchar allows you to specify the lengths. If you exceed the length PG will complain and not truncate.
• Varchar trims, char does not
• These fields are 4b each and their actual values are stored on a toast-table.

Binary '\000' - null terminator (ASCII) | '\00' (HEX) For this reason PG uses BYTEA datatype, this is 2 fields which stores the length of the data and one that stores the whole data. Then PG knows how long to look and then doesn't stop at the null terminator.

##Index Multiple columns will be used when BOTH columns are used for the query. Creating a table locks the table. However, create index concurrently... needs to scan twice but doesnt block.

Should run REINDEX from time to prevent bloating in index.

##Background Writer Flattens out the I/O activity durring Checkpoints by writing Pages (pages in shared mem) from WAL in the background.

##Backups Backing Up pg_dump

• full backup
• doesn't dump meta info unles you include -g for global
• Has 4 formats, you want custom (default is plain text)

Restoring

• plain text is a list of sql commands
• other formats need pg_restore Make sure to test your backup (using restore)

• Use exit-on-error to ensure that it fails fast
• -l loads dump

---

How DB Works

###Memory Usage

------------------------------------------
|128mb Shared Buffer |       |           |
----------------------       |           |
|              4-8gb of Pages|           |
------------------------------           |
|                                 64gb DB|
------------------------------------------

###Transactions Transaction Log

• DB creates a transaction log as this is more performant that trying to execute every change.

WAL (Transaction Log)

• Write Ahead Log. This is the file that creates disk changes.
• Length is equal to availble disk space.
• 3 Files each is 16mb. The end of the last one is a CHECKPOINT.
• Once the last file fills up, then the log starts to write to disk. Note: this only writes changes, not transactions.
• After the changes are commited, then the log's space can be reused.
• File writing (I/O) only occures durring the CHECKPOINT. Everything stops and the changes are commited.

If the db crashs, then the db re-applies all the changes from the last good checkpoint.

###Recursive SQL

SELECT field, grains_single,
   SUM(grains_single) OVER (ORDER BY field) AS grains_sum
  FROM (
  WITH  RECURSIVE rice AS
       (SELECT 1 AS field, 1::DECIMAL AS grains_single
 UNION  ALL
SELECT  field + 1, grains_single * 2
  FROM  rice
 WHERE  field < 64)
SELECT  *
  FROM  rice) AS recursion;