PostgreSQL For MySQL DBAS, Second Edition

Databases run better with Percona

PostgreSQL for MySQL DBAs [email protected] @Stoker Second Edition

Reasons for learning PostgreSQL if you have a MySQL background
Episode 1

©2023 Percona | Confidential Who Am I I am Dave
Stokes Technology Evangelist at Percona Author of MySQL & JSON - A Practical Programming Guide Over a decade on the Oracle MySQL Community Team Started with MySQL 3.29 [email protected] @Stoker https://speakerdeck.com/stoker

©2024 Percona PostgreSQL For MySQL DBAs Why This Series Was
Created • Are you a MySQL DBA looking to learn more about databases? • And curious about PostgreSQL? • Are you managing a PostgreSQL system and want to cross-train a MySQL DBA? This series is designed for anyone interested in any of the above. 5

©2024 Percona This presentation covers what you need to get
a working copy of a PostgreSQL server, installing a test database to learn the basics, and to guide you over some of the areas that are uncharted waters for MySQL DBAs. All examples are made with an Ubuntu 20.04 LTS and Percona Software for PostgreSQL, available at https://www.percona.com/postgresql/software. But the content should work on any working instance of PostgreSQL 14 or later. The Goal of the PostgreSQL for MySQL DBA series 6

Please continue with Episode 2 to see how to PostgreSQL
is different from MySQL, and not in a bad way. End of Episode 1

Thank You! [email protected] @Stoker Speakerdeck.com/Stoker

PostgreSQL is different and not in a bad way Episode
2

Different != Better, just different PostgreSQL is different

©2024 Percona Materialized Views MERGE() – process transactions logs, like
from cash registers, as a batch rather than multiple round trips between application and database TWO JSON data types Many different types of indexes • Ability to index only parts of an index • Can ‘roll your own’ indexes (another series on just indexes is being produced) Things That Are Different 12

©2024 Percona Better SQL standard compliance More complete Window Functions
Sequences • Similar to MySQL AUTO_INCREMENT • Great for generating test data Basis for many projects • FerretDB - MongoDB protocol • Pg_vector - You do not need a specialized vector database Things That Are Different 13

©2024 Percona The Heap Vacuum Table Bloat Transaction ID wrap
around Things that are really different 14

©2024 Percona SELECT fa.actor_id, SUM(length) FILTER (WHERE rating = 'R'),
SUM(length) FILTER (WHERE rating = 'PG') FROM film_actor AS fa LEFT JOIN film AS f ON f.film_id = fa.film_id GROUP BY fa.actor_id A Cool Example of things you can do with PG 15

©2024 Percona test=# CREATE TABLE staff (id) as SELECT 'Employee'
|| x FROM generate_series(1,500) as g(x); You can easily generate test data with a series 16 test=# select * from staff limit 5; id ----------- Employee1 Employee2 Employee3 Employee4 Employee5 (5 rows)

Please continue with Episode 3 to see how to install
PostgreSQL End of Episode 2

Installing PostgreSQL Episode 3

©2024 Percona • Install Percona Release Tool • Install Percona
PostgreSQL Episode 2 Outline 21

©2024 Percona $ sudo apt update $ sudo apt install
-y wget gnupg2 curl lsb-release $ wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb $ sudo dpkg -i percona-release_latest.generic_all.deb $ sudo apt update See https://docs.percona.com/postgresql/16/ for details and how to install on RPM based distributions Install Prerequisites 22

©2024 Percona $ sudo percona-release setup ppg-16 $ sudo apt
install -y percona-postgresql-16 $ sudo systemctl status postgresql.service Install PostgreSQL 23

Please continue with Episode 4 to see how to install
a test database End of Episode 3

Installing a Test Database Episode 4

©2024 Percona Download and unzip the above $ sudo su
- postgres $ psql postgres=# CREATE DATABASE dvdrental; postgres=# exit; $ pg_restore -U postgres -d dvdrental dvdrental.tar Yes, there are ports of the Sakila DB to PG but we want familiar without an exact match https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip 28

©2024 Percona PostgreSQL Sample Database Tables - There are 15
tables in the DVD Rental database: • actor – stores actors data including first name and last name. • film – stores film data such as title, release year, length, rating, etc. • film_actor – stores the relationships between films and actors. • category – stores film’s categories data. • film_category- stores the relationships between films and categories. • store – contains the store data including manager staff and address. • inventory – stores inventory data. • rental – stores rental data. • payment – stores customer’s payments. • staff – stores staff data. • customer – stores customer data. • address – stores address data for staff and customers • city – stores city names. • country – stores country names. DVDRental Database, very Sakila-ish 29

©2024 Percona (Command Line) $ sudo su - postgres $
createuser –interactive -s <user> The -s is for ‘superuser’ * For the time being use the login for your Linux account as the <user>. Use whoami to double check! Create your test account 30 * Superuser setting bypasses almost all permission checks. Yup, this can be dangerous but you are an experienced DBA and are not going to do anything dumb, right?

©2024 Percona $sudo su - postgres $psql postgres=# CREATE USER
<user> WITH SUPERUSER; Create your user account, version 2 w/psql 31

©2024 Percona $ psql -d dvdrental (psql 16.1) Type “help”
for help. dvdrental=# -d dvdrental - which database to use First use of test database 32

©2024 Percona SELECT * FROM actor LIMIT 10; Find the
top grossing movie for each year, the actors who appeared in the most films, and stuff like that. You now have a safe environment in which to make queries and learn the basics of PostgreSQL. Try some simple SQL commands 33

©2024 Percona So in this episode we installed PostgreSQL, installed
a test database similar to the MySQL Sakila database, created a user account, and made a simple query. Congratulations! 34

Please continue with Episode 5 for PostgreSQL commands End of
Episode 4

PostgreSQL Commands Episode 5

©2024 Percona Contents of this episode • Look at some
PSQL commands • Warn you about some PSQL commands PostgreSQL Commands 39

This is usually the first command a MySQL DBA types
at a psql prompt. What! No SHOW TABLES!!

©2024 Percona dvdrental=# show create table actor; ERROR: syntax error
at or near "create" LINE 1: show create table actor; ^ dvdrental=# \d actor; Table "public.actor" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+----------------------------------------- actor_id | integer | | not null | nextval('actor_actor_id_seq'::regclass) first_name | character varying(45) | | not null | last_name | character varying(45) | | not null | last_update | timestamp without time zone | | not null | now() Indexes: "actor_pkey" PRIMARY KEY, btree (actor_id) "idx_actor_last_name" btree (last_name) Referenced by: TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT Triggers: last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE FUNCTION last_updated() Nor is there a SHOW CREATE TABLE <table> 42

©2024 Percona \c dbname Switch connection to a new database
\l List available databases \dt List available tables \d table_name Describe a table such as a column, type, modifiers of columns, etc. \dn List all schemes of the currently connected database \df List available functions in the current database \dv List available views in the current database \du List all users and their assign roles SELECT version(); Retrieve the current version of PostgreSQL server \g Execute the last command again \s Display command history \s filename Save the command history to a file \i filename Execute psql commands from a file \? Know all available psql commands \h Get help Eg:to get detailed information on ALTER TABLE statement use the \h ALTER TABLE \e Edit command in your own editor \a Switch from aligned to non-aligned column output \H Switch the output to HTML format \q Exit psql shell Many PostgreSQL commands will see weird to MySQLers 45

Please continue with Episode 6 to learn a simple way
to backup data End of Episode 5

Episode 6 Simple backup and restore

©2024 Percona The ability to recover lost data efficiently is
essential to keeping a database (and your boss) happy. Backups are important! 50

©2024 Percona $ pg_dump dvdrental > backup.sql • pg_dump is
the name of the ‘backup’ program * • dvdrental is name of the database to be backed up • Dumping the output to file backup.sql * Equivalent to mysqldump A Simple Backup 51

©2024 Percona $ sudo su - postgres $ psql (psql
16.1 (Ubuntu 2:14.3-3-focal)) Type “help” for help. dvdrental=# CREATE DATABASE newdvd; dvdrental=# \q $ ^d Lets restore that backup to a new database 52

©2024 Percona $ psql -d newdvd -f backup.sql • -d
<database name) • -f <file created by pg_dump) Restoration 53

©2024 Percona You must do backups regularly! You must be
ready to restore a complete instance, a single database, a single table. DBAs are only as good as their last backups! Chech out • pg_rman • pgBackRest There are many options for backup tools! Use two or more! 54

Please continue with Episode 7 to learn some more commands
End of Episode 6

Episode 7 More commands

©2024 Percona dvdrental=# \d actor Table "public.actor" Column | Type
| Collation | Nullable | Default -------------+-----------------------------+-----------+----------+----------------------------------------- actor_id | integer | | not null | nextval('actor_actor_id_seq'::regclass) first_name | character varying(45) | | not null | last_name | character varying(45) | | not null | last_update | timestamp without time zone | | not null | now() Indexes: "actor_pkey" PRIMARY KEY, btree (actor_id) "idx_actor_last_name" btree (last_name) Referenced by: TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT Triggers: last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE FUNCTION last_updated() \d actor 65

©2024 Percona dvdrental=# CREATE USER percona; dvdrental-# WITH SUPERUSER ENCRYPTED
PASSWORD 'percona'; CREATE ROLE dvdrental=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- percona | Superuser | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} User details 66

Please continue with Episode 8 to see how to clean
up table space End of Episode 7

Episode 8 Vacuum

©2024 Percona In normal PostgreSQL operation, tuples that are deleted
or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently updated tables. -PG Documentation * A tuple is PostgreSQL's internal representation of a row in a table. VACUUM reclaims storage occupied by dead tuples*. 72 MySQL uses as different MVCC approach that automatically takes care of dead tuples and vacuuming will seem very odd to a MySQL DBA

©2023 Percona | Confidential The Heap - https://medium.com/quadcode-life/structure-of-heap-table-in-postgresql-d44c94332052 • Tuples
(rows) are stored in a heap by object identifier (OID) • Data is unordered (use order by) • Updated/replaced rows are kept in the heap until vacuumed (more later) • The OID is a 32 bit INTEGER that can wrap around and make older data useless 73

©2023 Percona | Confidential test=# create table foo (id int,
value int); CREATE TABLE test=# insert into foo values (1,1); INSERT 0 1 test=# update foo set value=2 where id =1; UPDATE 1 test=# update foo set value=3 where id =1; UPDATE 1 test=# update foo set value=4 where id =1; UPDATE 1 75 test=# select relname,n_dead_tup from pg_stat_all_tables where relname = 'foo'; relname | n_dead_tup ---------+------------ foo | 3 (1 row) test=# VACUUM foo; VACUUM test=# select relname, n_dead_tup from pg_stat_all_tables where relname = 'foo'; relname | n_dead_tup ---------+------------ foo | 0 (1 row)

©2024 Percona PostgreSQL's VACUUM command has to process each table
on a regular basis for several reasons: • To recover or reuse disk space occupied by updated or deleted rows. • To update data statistics used by the PostgreSQL query planner. • To update the visibility map, which speeds up index-only scans. • To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound. Remember to vacuum regularly 76

©2024 Percona Vacuum maintains a visibility map for each table
to keep track of which pages contain only tuples that are known to be visible to all active transactions (and all future transactions, until the page is again modified). This has two purposes. • vacuum itself can skip such pages on the next run, since there is nothing to clean up. • Second, it allows PostgreSQL to answer some queries using only the index, without reference to the underlying table. Since PostgreSQL indexes don't contain tuple visibility information, a normal index scan fetches the heap tuple for each matching index entry, to check whether it should be seen by the current transaction. An index-only scan, on the other hand, checks the visibility map first. If it's known that all tuples on the page are visible, the heap fetch can be skipped. This is most useful on large data sets where the visibility map can prevent disk accesses. The visibility map is vastly smaller than the heap, so it can easily be cached even when the heap is very large. The Visibility Map 77

©2024 Percona PostgreSQL's MVCC transaction semantics depend on being able
to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction XID is “in the future” and should not be visible to the current transaction. XIDs have limited size of 32 bits so a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound. XID counter wraps around to zero transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions. Transaction IDs or XIDs 78

©2024 Percona This form of the command can operate in
parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. It also allows us to leverage multiple CPUs in order to process indexes. This feature is known as parallel vacuum. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed. Plain VACUUM (w/o FULL) simply reclaims space and makes it available for re-use. 79

©2024 Percona PostgreSQL has an optional but highly recommended feature
called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. test=# SHOW autovacuum; autovacuum ------------ on (1 row) Autovacuum 80

Please see the next episode on materialized views or visit
Percona.com for more content. End of Episode 8

Episode 9 Views, Materialized Views, & Watch

©2024 Percona test=# create table base (a int, b int,
c int); CREATE TABLE test=# insert into base values (1,2,3),(4,5,6),(7,8,9); INSERT 0 3 test=# create view v1 as SELECT a, b, c*4 from base; CREATE VIEW test=# select * from v1; a | b | ?column? ---+---+---------- 1 | 2 | 12 4 | 5 | 24 7 | 8 | 36 (3 rows) test=# select * from v1 where a > 6; a | b | ?column? ---+---+---------- 7 | 8 | 36 A View Is An Abstraction of a Table, Used Like a Table 85 Traditionally views are used to simplify complex table or obfuscate the underlying table.

©2024 Percona test=# create materialized view v2 as SELECT a,
b, c*4 from base; SELECT 3 test=# select * from v2 where a > 6; a | b | ?column? ---+---+---------- 7 | 8 | 36 (1 row) Materialized Views 86 test=# update base set b=99 where a = 7; UPDATE 1 test=# select * from v2 where a > 6; a | b | ?column? ---+---+---------- 7 | 8 | 36 test=# refresh materialized view v2; REFRESH MATERIALIZED VIEW test=# select * from v2 where a > 6; a | b | ?column? ---+----+---------- 7 | 99 | 36 (1 row) The value of column b does not appear in the view until the view is refreshed.

Please let us we can cover in this series to
help you learn PostgreSQL. End of Episode 9

What Else? What other things about PostgreSQL are you curious
about? Please let me know!

Thank You! [email protected] @Stoker Speakerdeck.com/Stoker Percona.com

PostgreSQL For MySQL DBAS, Second Edition

PostgreSQL For MySQL DBAS, Second Edition

More Decks by David Stokes

Other Decks in Technology

Featured

Transcript