Tweet
Tweet

Slide 1

Slide 1 text

Databases run better with Percona

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

©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

Slide 5

Slide 5 text

©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

Slide 6

Slide 6 text

©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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

PostgreSQL is different and not in a bad way Episode 2

Slide 11

Slide 11 text

Different != Better, just different PostgreSQL is different

Slide 12

Slide 12 text

©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

Slide 13

Slide 13 text

©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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

©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

Slide 16

Slide 16 text

©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)

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

Installing PostgreSQL Episode 3

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

©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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

Installing a Test Database Episode 4

Slide 28

Slide 28 text

©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

Slide 29

Slide 29 text

©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

Slide 30

Slide 30 text

©2024 Percona (Command Line) $ sudo su - postgres $ createuser –interactive -s The -s is for ‘superuser’ * For the time being use the login for your Linux account as the . 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?

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

©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

Slide 33

Slide 33 text

©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

Slide 34

Slide 34 text

©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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

PostgreSQL Commands Episode 5

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

©2024 Percona dvdrental=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | actor | table | postgres public | address | table | postgres public | category | table | postgres public | city | table | postgres public | country | table | postgres public | customer | table | postgres public | film | table | postgres public | film_actor | table | postgres public | film_category | table | postgres public | inventory | table | postgres public | language | table | postgres public | payment | table | postgres public | rental | table | postgres public | staff | table | postgres public | store | table | postgres (15 rows) Use \dt instead of SHOW TABLES 41

Slide 42

Slide 42 text

©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 42

Slide 43

Slide 43 text

©2024 Percona dvdrental=# SELECT * FROM actor ORDER BY last_name, first_name LIMIT 10; actor_id | first_name | last_name | last_update ----------+------------+-----------+------------------------ 58 | Christian | Akroyd | 2013-05-26 14:47:57.62 182 | Debbie | Akroyd | 2013-05-26 14:47:57.62 92 | Kirsten | Akroyd | 2013-05-26 14:47:57.62 118 | Cuba | Allen | 2013-05-26 14:47:57.62 145 | Kim | Allen | 2013-05-26 14:47:57.62 194 | Meryl | Allen | 2013-05-26 14:47:57.62 76 | Angelina | Astaire | 2013-05-26 14:47:57.62 112 | Russell | Bacall | 2013-05-26 14:47:57.62 190 | Audrey | Bailey | 2013-05-26 14:47:57.62 67 | Jessica | Bailey | 2013-05-26 14:47:57.62 (10 rows) But basic SQL is still the same 43

Slide 44

Slide 44 text

©2024 Percona dvdrental=# CREATE DATABASE test; CREATE DATABASE dvdrental=# \c test; You are now connected to database "test" as user "postgres". test=# \c dvdrental You are now connected to database "dvdrental" as user "postgres". dvdrental=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+----------------------------+----------------------------+---------------- ------- dvdrental | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (5 rows) \c to change databases 44

Slide 45

Slide 45 text

©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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

Episode 6 Simple backup and restore

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

©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

Slide 52

Slide 52 text

©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

Slide 53

Slide 53 text

©2024 Percona $ psql -d newdvd -f backup.sql ● -d

Slide 54

Slide 54 text

©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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

Episode 7 More commands

Slide 59

Slide 59 text

©2024 Percona dvdrental=# SELECT * FROM actor LIMIT 5; actor_id | first_name | last_name | last_update ----------+------------+--------------+------------------------ 1 | Penelope | Guiness | 2013-05-26 14:47:57.62 2 | Nick | Wahlberg | 2013-05-26 14:47:57.62 3 | Ed | Chase | 2013-05-26 14:47:57.62 4 | Jennifer | Davis | 2013-05-26 14:47:57.62 5 | Johnny | Lollobrigida | 2013-05-26 14:47:57.62 (5 rows) \g has two different uses 59 dvdrental=# \g dvdrental=# SELECT * FROM actor LIMIT 5; actor_id | first_name | last_name | last_update ----------+------------+--------------+------------------------ 1 | Penelope | Guiness | 2013-05-26 14:47:57.62 2 | Nick | Wahlberg | 2013-05-26 14:47:57.62 3 | Ed | Chase | 2013-05-26 14:47:57.62 4 | Jennifer | Davis | 2013-05-26 14:47:57.62 5 | Johnny | Lollobrigida | 2013-05-26 14:47:57.62 (5 rows)

Slide 60

Slide 60 text

©2024 Percona dvdrental=# SELECT * FROM actor LIMIT 3 \g actor_id | first_name | last_name | last_update ----------+------------+-----------+------------------------ 1 | Penelope | Guiness | 2013-05-26 14:47:57.62 2 | Nick | Wahlberg | 2013-05-26 14:47:57.62 3 | Ed | Chase | 2013-05-26 14:47:57.62 (3 rows) \g can be as a command terminator 60

Slide 61

Slide 61 text

©2024 Percona dvdrental=# CREATE DATABASE test; CREATE DATABASE dvdrental=# \c test; You are now connected to database "test" as user "postgres". test=# \c dvdrental You are now connected to database "dvdrental" as user "postgres". dvdrental=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+----------------------------+----------------------------+---------------- ------- dvdrental | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (5 rows) \c to change databases 61

Slide 62

Slide 62 text

©2024 Percona dvdrental=# \d List of relations Schema | Name | Type | Owner --------+----------------------------+----------+---------- public | actor | table | postgres public | actor_actor_id_seq | sequence | postgres public | actor_info | view | postgres public | address | table | postgres public | address_address_id_seq | sequence | postgres public | category | table | postgres public | category_category_id_seq | sequence | postgres public | city | table | postgres public | city_city_id_seq | sequence | postgres public | country | table | postgres public | country_country_id_seq | sequence | postgres public | customer | table | postgres public | customer_customer_id_seq | sequence | postgres public | customer_list | view | postgres public | film | table | postgres public | film_actor | table | postgres public | film_category | table | postgres public | film_film_id_seq | sequence | postgres public | film_list | view | postgres public | inventory | table | postgres public | inventory_inventory_id_seq | sequence | postgres public | language | table | postgres public | language_language_id_seq | sequence | postgres public | nicer_but_slower_film_list | view | postgres public | payment | table | postgres public | payment_payment_id_seq | sequence | postgres public | rental | table | postgres public | rental_rental_id_seq | sequence | postgres public | sales_by_film_category | view | postgres public | sales_by_store | view | postgres public | staff | table | postgres public | staff_list | view | postgres public | staff_staff_id_seq | sequence | postgres public | store | table | postgres public | store_store_id_seq | sequence | postgres (35 rows) \d to see contents of the database 62

Slide 63

Slide 63 text

©2024 Percona dvdrental=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | actor | table | postgres public | address | table | postgres public | category | table | postgres public | city | table | postgres public | country | table | postgres public | customer | table | postgres public | film | table | postgres public | film_actor | table | postgres public | film_category | table | postgres public | inventory | table | postgres public | language | table | postgres public | payment | table | postgres public | rental | table | postgres public | staff | table | postgres public | store | table | postgres (15 rows) \dt to see only the tables in a database 63

Slide 64

Slide 64 text

©2024 Percona \ds - sequences \du - users \dv - views \df - functions \d - details on a table 64

Slide 65

Slide 65 text

©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

Slide 66

Slide 66 text

©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

Slide 67

Slide 67 text

©2024 Percona dvdrental=# SELECT * FROM actor LIMIT 5; actor_id | first_name | last_name | last_update ----------+------------+--------------+------------------------ 1 | Penelope | Guiness | 2013-05-26 14:47:57.62 2 | Nick | Wahlberg | 2013-05-26 14:47:57.62 3 | Ed | Chase | 2013-05-26 14:47:57.62 4 | Jennifer | Davis | 2013-05-26 14:47:57.62 5 | Johnny | Lollobrigida | 2013-05-26 14:47:57.62 (5 rows) dvdrental=# \a Output format is unaligned. dvdrental=# SELECT * FROM actor LIMIT 5; actor_id|first_name|last_name|last_update 1|Penelope|Guiness|2013-05-26 14:47:57.62 2|Nick|Wahlberg|2013-05-26 14:47:57.62 3|Ed|Chase|2013-05-26 14:47:57.62 4|Jennifer|Davis|2013-05-26 14:47:57.62 5|Johnny|Lollobrigida|2013-05-26 14:47:57.62 (5 rows) dvdrental=# \H Output format is html. Output Alignment 67

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

Episode 8 Vacuum

Slide 72

Slide 72 text

©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

Slide 73

Slide 73 text

©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

Slide 74

Slide 74 text

©2023 Percona | Confidential Vacuum - https://www.geeksforgeeks.org/postgresql-autovacuum/

Slide 75

Slide 75 text

©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)

Slide 76

Slide 76 text

©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

Slide 77

Slide 77 text

©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

Slide 78

Slide 78 text

©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

Slide 79

Slide 79 text

©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

Slide 80

Slide 80 text

©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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

Episode 9 Views, Materialized Views, & Watch

Slide 85

Slide 85 text

©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.

Slide 86

Slide 86 text

©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.

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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