--- title: "Postgres Cheat Sheet" description: "Your Postgres commands in one place. Learn how to use psql to list and create Postgres databases, show your tables, enter your Postgres terminal, and more." section: "Postgres basics" published: 2025-01-31T15:01:03.374Z updated: 2026-09-08T00:00:00.000Z --- *Updated at Sep 8, 2026* > **TimescaleDB is now Tiger Data.** # **Postgres Cheat Sheet** Every essential Postgres command, all in one place. Whether you're listing and creating databases, inspecting tables, managing users, building indexes, or running maintenance tasks, this reference has you covered, straight from the psql terminal or your favorite SQL client. Think of a Postgres cheat sheet as your quick-reference companion: a handy guide to the most common psql meta-commands (shortcuts like \dt or \l that start with a backslash) and SQL statements you'll reach for again and again to manage PostgreSQL databases, tables, users, indexes, and configuration, all from the command line. ## **Databases** PostgreSQL database management relies on a small set of psql meta-commands and SQL statements. You list databases with \l, create them with CREATE DATABASE, and switch between them with \c . These commands work in any psql session and apply to both local and remote PostgreSQL servers. ### **List PostgreSQL databases** `\l` List all databases using \l (or \list) in psql. `\l+` List all databases using \l+ with more details, including description, tablespace, and DB size, in psql. [psql list databases](https://www.youtube.com/embed/hlQER0f03YU?si=0_AHTBVJWX9yaWn7) ### **Help on CREATE DATABASE command syntax** `\h CREATE DATABASE` Display help on SQL command syntax (for example, CREATE DATABASE) in psql. ### **Create database** `CREATE DATABASE mytest;` Creates a new database called mytest. By default, the owner is the current login user. `\c test You are now connected to database "test" as user "postgres".` Connect to a PostgreSQL database called test as the postgres user in psql. ## **Tables** Table inspection in psql uses \d and its variants to show structure, constraints, and disk size. You can also export data to CSV, check indexes, and add comments directly from the terminal. ### **Show table** `\d TABLE_NAME` Show table definition including indexes, constraints, and triggers in psql. ### **Show details** `\d+ TABLE_NAME` Show a more detailed table definition including description (comments) and physical disk size in psql. ### **List tables from current schema** `\dt` List tables from the current schema in psql. ### **List tables from all schemas** `\dt *.*` List tables from all schemas in psql. ### **List tables for a schema** `\dt .*` List the tables in a specific schema in psql. ### **Copy table data to CSV file** `\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV` Export a table as CSV in psql to the current directory. ### **Check indexes for a table using SQL** `SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';` Show table indexes using SQL. ### **Collect statistics about table contents** `ANALYZE [__table_name__]` Analyze a table and store the results in the pg_statistic system catalog. With no parameter, ANALYZE examines every table in the current database (‘table_name’ in square brackets means optional argument). ### **Add a comment on a table or column** `COMMENT ON TABLE employee IS 'Stores employee records';` Add a comment on a table using SQL. `COMMENT ON COLUMN employee.emp_ssn IS 'Employee Social Security Number';` Add a comment on a column using SQL. ### **Approximate table row count** `SELECT reltuples AS card FROM pg_class WHERE relname = '';` Use this for fast (but not exact) counts from tables. It's helpful when a table has millions or billions of records and you want an estimated row count quickly. ## **Connecting** Connecting to PostgreSQL involves SSH access to the host, switching to the postgres system user, and entering the psql terminal. Once inside, you can check client and server versions and switch between databases. ### **Login using PostgreSQL user** `$ ssh -l postgres 200.34.22.75 postgres@200.34.22.75's password: Linux localhost 5.10.0-28-amd64 #1 SMP Debian 5.10.209-2 (2024-01-31) x86_64` Login as PostgreSQL superuser postgres on a remote PostgreSQL server using SSH. `root@localhost:~# su - postgres` Login as PostgreSQL superuser postgres on Linux. [Learn how to test your PostgreSQL connection](https://www.tigerdata.com/blog/how-to-test-your-postgresql-connection). ### **Enter PostgreSQL terminal** `postgres@localhost:~$ psql psql (17.2) Type "help" for help. postgres=#` Enter the PostgreSQL command line via the psql client. ### **Connect to a database** `\c test You are now connected to database "test" as user "postgres".` Connect to a PostgreSQL database called test as the postgres user in psql. ### **Check psql client version** `$ psql -V psql (PostgreSQL) 17.2` Check the psql client version. ### **Check Postgres server version** `select version(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (1 row)` Check the Postgres server version using SQL. **Connection pooling tip.** For applications managing many concurrent connections, pair your PostgreSQL setup with a connection pooler such as PgBouncer. Each PostgreSQL connection forks a new process and consumes memory, so pooling is a recommended production practice at scale. ## **Queries** PostgreSQL queries cover table creation, data insertion, conditional selects, and safe updates wrapped in transactions. The examples below use a simple employee table to demonstrate each pattern. ### **Create a new table** `CREATE TABLE IF NOT EXISTS employee ( emp_id SERIAL PRIMARY KEY, -- AUTO_INCREMENT integer, as primary key emp_name VARCHAR(50) NOT NULL, emp_salary NUMERIC(9,2) NOT NULL );` Creates a new table using SQL. **Modern alternative.** SERIAL is the traditional shorthand for an auto-incrementing integer. In PostgreSQL 10 and later, the SQL-standard equivalent is GENERATED ALWAYS AS IDENTITY. For example, emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY. Both work, and GENERATED ALWAYS AS IDENTITY is preferred for new schemas. ### **Display table** `\d employee Table "public.employee" Column | Type | Modifiers ------------+-----------------------+----------------------------------------------------------- emp_id | integer | not null default nextval('employee_emp_id_seq'::regclass) emp_name | character varying(50) | not null emp_salary | numeric(9,2) | not null Indexes: "employee_pkey" PRIMARY KEY, btree (emp_id)` Display a table definition in psql. ### **Insert query** `INSERT INTO employee (emp_name, emp_salary) VALUES ('John', 5000), ('Jack', 4568.0), ('Robert',7500.50);` Insert records into a table using SQL. ### **Conditional select query** `select * from employee where emp_salary >= 5000; emp_id | emp_name | emp_salary --------+----------+------------ 1 | John | 5000.00 3 | Robert | 7500.50 (2 rows)` Select data based on a filter condition (for example, emp_salary >= 5000) using SQL. ### **Conditional update query (safe update)** `BEGIN; UPDATE employee SET emp_salary = 6000 WHERE emp_name = 'John'; COMMIT;` Update a record based on a condition (for example, update emp_salary for employee John) using SQL. Records aren't committed to the database unless you issue a COMMIT. You can undo updates by issuing ROLLBACK instead of COMMIT. ## **Functions** ### **Create a new function** `CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;` Create a function to add two integers using SQL. This function takes two integers as parameters. IMMUTABLE means the function can't modify the database and always returns the same result when given the same argument values. ### **Calling a function** `select add(5,9); add ----- 14 (1 row)` Call a function using SQL. ### **List functions** `\df List of functions Schema | Name | Result data type | Argument data types | Type --------+------+------------------+---------------------+-------- public | add | integer | integer, integer | normal (1 row)` Display all functions in psql. `\df+` Display all functions with additional information, including owner, source code, and description, in psql. ### **Edit a function** `\ef ` Edit a function in the default editor in psql. ## **Views** Listing PostgreSQL views helps you understand database structure, optimize queries, analyze dependencies, document schemas, and maintain security and access control. ### **List views** `\dv` List views from the current schema in psql. `\dv *.*` List views from all schemas in psql. ## **Users** User management in PostgreSQL covers setting passwords, listing roles, and logging in with specific credentials. The pg_user catalog and \du meta-command give you a full picture of who has access and what privileges they hold. ### **Set or reset a Postgres user password** `\password username` Set or reset a password for a PostgreSQL database user in psql.` `For example, to change the password for the current user postgres: `\password postgres Enter new password: xxxx Enter it again: xxxx` ### **Show all users** `select * from pg_user;` Display PostgreSQL database users using SQL. `\du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- testrole | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}` Display PostgreSQL database roles in psql. ### **Login and enter the PostgreSQL terminal** `$ psql -U testuser mytest Password for user testuser: ...... psql (17.2) Type "help" for help.` Login to PostgreSQL with psql -U user database. ## **Indexes** Indexes in PostgreSQL speed up query execution by letting the planner avoid full table scans. You can create, inspect, and drop indexes using a combination of SQL statements and psql meta-commands. ### **Create a new index on a table** `create index idx_employee_emp_name on employee using btree (emp_name asc);` Create a new index on the emp_name column of the employee table using SQL. This index specifies btree as the index method and uses asc to store the index key column data in ascending order. ### **View indexes of a table** `\d employee postgres=# \d employee; Table "public.employee" Column | Type | Modifiers ------------+-----------------------+----------------------------------------------------------- emp_id | integer | not null default nextval('employee_emp_id_seq'::regclass) emp_name | character varying(50) | not null emp_salary | numeric(9,2) | not null Indexes: "employee_pkey" PRIMARY KEY, btree (emp_id) "idx_employee_emp_name" btree (emp_name)` List indexes of a table along with the table definition in psql. ### **List all indexes** `\di List of relations Schema | Name | Type | Owner | Table --------+-----------------------+-------+----------+---------- public | employee_pkey | index | postgres | employee public | idx_employee_emp_name | index | postgres | employee (2 rows)` List all indexes from all tables in psql. Indexes on TimescaleDB hypertables use the same CREATE INDEX syntax as regular PostgreSQL tables. By default, TimescaleDB automatically creates an index on the time column when you create a hypertable, but any other indexes (e.g., on a device or sensor ID) still need to be created explicitly, just as they would on a regular table. Behind the scenes, TimescaleDB applies each index across all chunks (the partitions that make up a hypertable), including new ones created in the future. [Try Tiger Cloud for free today](https://console.cloud.tigerdata.com/signup). ### **Drop an index from a table** `drop index idx_employee_emp_name;` Drop an existing index from a table using SQL. ### **List installed extensions** `\dx` List all installed PostgreSQL extensions in psql. `\dx+` List extensions with additional detail in psql. ## **Constraints** Postgres constraints are rules enforced on data columns within a table to maintain data integrity and prevent the insertion of invalid data. PostgreSQL supports primary key, unique, check, and foreign key constraints, each enforcing a different aspect of data validity. ### **Create a table with primary and unique constraints** `CREATE TABLE IF NOT EXISTS employee ( emp_id SERIAL PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, emp_ssn VARCHAR (30) NOT NULL UNIQUE, emp_salary NUMERIC(9,2) NOT NULL );` Creates a new table with primary and unique key constraints using SQL. A **Primary Key Constraint** enforces the uniqueness of a column or set of columns, ensuring each row in a table is uniquely identified. A **Unique Constraint** ensures all values in a column or set of columns are distinct, except for null values. ### **Avoid duplicate records** `INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Rohit', '1234', 5000.0); INSERT 0 1 INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Mason', '1234', 7500.0); ERROR: duplicate key value violates unique constraint "employee_emp_ssn_key" DETAIL: Key (emp_ssn)=(1234) already exists.` Insert records into a table with unique key constraints specified using SQL. This table uses emp_id as the primary key column and a unique constraint on emp_ssn to prevent duplicate social security numbers from being entered. ### **Create a table with a check constraint** `CREATE TABLE orders( ord_no integer, ord_date date, ord_qty numeric, ord_amount numeric CHECK (ord_amount>0) );` Creates a new table with a check constraint specified using SQL. `insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (1, '2019-08-29', 1, 10); INSERT 0 1 insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (2, '2019-08-29', 1, 0); ERROR: new row for relation "orders" violates check constraint "orders_ord_amount_check" DETAIL: Failing row contains (2, 2019-08-29, 1, 0).` Insert records into a table with check constraints specified using SQL. A **Check Constraint** verifies that all values in a column or set of columns satisfy a specified condition or expression. The check constraint on ord_amount > 0 means any record with ord_amount <= 0 will fail to insert. ### **Define a relation between two tables using a foreign key constraint** `CREATE TABLE IF NOT EXISTS department ( dept_id SERIAL PRIMARY KEY, dept_name VARCHAR(50) NOT NULL ); CREATE TABLE IF NOT EXISTS employee ( emp_id SERIAL PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, emp_ssn VARCHAR (30) NOT NULL UNIQUE, emp_salary NUMERIC(9,2) NOT NULL, emp_dept_id INTEGER REFERENCES department (dept_id) -- Foreign Key );` Creates the department and employee tables and defines a relation between an employee and a department using a foreign key (REFERENCES) in SQL. A **Foreign Key Constraint** establishes a link between data in two tables, enforcing referential integrity by preserving the relationships between the linked tables. ### **Check constraints on a table** `\d employee; Table "public.employee" Column | Type | Modifiers -------------+-----------------------+----------------------------------------------------------- emp_id | integer | not null default nextval('employee_emp_id_seq'::regclass) . . . Indexes: "employee_pkey" PRIMARY KEY, btree (emp_id) "employee_emp_ssn_key" UNIQUE CONSTRAINT, btree (emp_ssn) Foreign-key constraints: "employee_emp_dept_id_fkey" FOREIGN KEY (emp_dept_id) REFERENCES department(dept_id)` Display constraints on a table using the \d option in psql. ## **Identifiers** PostgreSQL provides a rich set of operators and functions for working with strings and numbers. The examples below cover concatenation, math operators, string manipulation, and quoting conventions. ### **String concatenate operator** `select 'Gordon' || ' ' || 'Moore' As fullName; fullname -------------- Gordon Moore (1 row)` Concatenates two or more strings using ||. This operator works on table columns as well, for example select first_name || ' ' || last_name As fullName from person. ### **Square and cube root operators** `select |/25 As sqrt; sqrt ------ 5 (1 row) `Square root operator. `select ||/125 As cubert; cubert ------ 5 (1 row)` Cube root operator. ### **Factorial function** `select factorial(5) As factorial; factorial ----------- 120 (1 row)` The factorial(n) function replaced the deprecated ! postfix operator, which was removed in PostgreSQL 14. ### **Binary complement operator** `select ~60 As compl; compl ---------- -61 (1 row)` Binary 2's complement. This operator flips bits, so if A = 60 (binary 0011 1100), then ~A = 1100 0011. ### **String lower and upper functions** `select lower('Rohit Kumawat') As lowerCase, upper('Rohit Kumawat') As upperCase; lowercase | uppercase ---------------+--------------- rohit kumawat | ROHIT KUMAWAT (1 row)` Postgres lower() and upper() functions using SQL. ### **Number of characters in a string** `select char_length('Arizona') as num_chars; num_chars ----------- 7 (1 row)` Count the number of characters in a string using SQL. ### **Location of a specified substring** `select position('pan' in 'japan') As pos; pos ----- 3 (1 row)` Find the location of a specified substring using SQL (uses 1-based indexing). ### **Extract a substring** `select substring('postgres' from 3 for 3) As sub_string; sub_string ------------ stg (1 row)` Extract a substring from postgres starting at the third character for a length of three characters using SQL. ### **Insert a newline in SQL output** `postgres=# select 'line 1'||E'\n'||'line 2' As newline; newline --------- line 1 + line 2 (1 row)` Insert a new line using E'\n'. You can also use the chr() function. The E prefix enables escape string constants, which support sequences including \b (backspace), \f (form feed), \n (newline), \r (carriage return), and \t (tab). ### **Quote identifier** `UPDATE "my table" SET "a&b" = 0;` Double quotes act as delimited identifiers, allowing table or column names that would otherwise be invalid, such as those containing spaces or ampersands. Double quotes also escape reserved keywords in PostgreSQL. Quoting also makes identifiers case-sensitive, so "my table" and “My Table” are treated as different names. ### **Dollar-quoted string constant** `select $$Maria's dogs$$ As col; col -------------------- Maria's dogs (1 row)` Use dollar-quoted string constants instead of single quotes when a string contains many single quotes or backslashes. PostgreSQL treats everything between the $$ delimiters as a literal string. ### **Toggle query timing** `\timing` Toggle the display of how long each SQL statement takes to execute in psql. Useful for quick performance checks without running EXPLAIN. ## **Maintenance** ### **Garbage collect (reclaim storage)** `VACUUM [__Table__] vacuum(verbose, analyze) employee; INFO: vacuuming "public.employee" INFO: scanned index "employee_pkey" to remove 1 row versions . . . sample, 1 estimated total rows VACUUM` Use the VACUUM command to reclaim storage from deleted rows in the employee table using SQL. Table rows that are deleted or made obsolete by an update aren't physically removed from their table until a VACUUM command runs. Run VACUUM periodically, especially on frequently-updated tables. The ‘verbose’ option prints a detailed vacuum activity report for each table, and ‘analyze’ option updates statistics for the table. ### **Gather statistics** `ANALYZE [__table__] analyze verbose employee; INFO: analyzing "public.employee" INFO: "employee": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows ANALYZE` Analyze a table and store the results in the pg_statistic system catalog using SQL. ANALYZE gathers statistics for the query planner to create the most efficient query execution plans. Accurate statistics help the planner choose the most appropriate query plan, improving query processing speed. With no table name specified, ANALYZE examines every table in the current database. The verbose option prints a detailed analyze activity report for each table. ## **Monitoring** Monitoring active PostgreSQL sessions helps you identify long-running queries, idle connections, and blocking processes before they affect application performance. ### **Session monitor** `SELECT pid, datname, usename, application_name, client_hostname, state, client_port, backend_start, query_start, query FROM pg_stat_activity;` Monitor Postgres sessions using SQL. Key columns to know: - **pid:** The backend process ID - **datname:** The database name - **usename:** The user running the query - **application_name:** The client application name - **state:** The state of the session (active, waiting, idle, and so on) - **query:** The query being executed ### **Cancel a running query** `SELECT pg_cancel_backend(pid);` Cancel a running query by providing its pid. This is useful for terminating long-running queries using SQL. ### **Biggest PostgreSQL tables and indexes by size** `SELECT nspname || '.' || relname AS "Object Name", relkind As "Object Type", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;` List the top 20 largest tables and indexes, excluding catalog tables, using SQL. ## **Backup** ### **Database backup with default options** `$ pg_dump mydb > mydb.bak.sql` Create a backup for a database called mydb in a plain-text SQL script file (mydb.bak.sql) using pg_dump. Backups in TimescaleDB are fully automated. [Learn how TimescaleDB handles database backups and disaster recovery](https://www.tigerdata.com/blog/database-backups-and-disaster-recovery-in-postgresql-your-questions-answered/). ### **Database backup with customized options** `$ pg_dump -c -C -F p -f mydb.bak.sql mydb` Creates a backup for a database called mydb in plain text format with drop and create database commands included in the output file mydb.bak.sql using pg_dump. Backup options: - **-c/ --clean:** output commands to clean (drop) database objects before writing commands to create them - **-C/--create:** begin output with a CREATE DATABASE command and reconnect to the created database - **-F:** format of the output (p means plain SQL output, c means custom archive format suitable for pg_restore) - **-f:** backup output file name ### **Remote backup** `$ pg_dump -h -p -U -f mydb.bak mydb` Run pg_dump on the client computer to back up data on a remote Postgres server. Use -h to specify the IP address of your remote host and -p to identify the port on which PostgreSQL is listening. ### **All databases backup** `$ pg_dumpall > alldb.bak.sql` Back up all databases along with database roles and cluster-wide information using pg_dumpall. ## **Restore** ### **Restore from a backup file (.sql)** `$ psql -U username -d db_name -f filename.sql` Restore a plain-text backup (.sql) generated by pg_dump or pg_dumpall using the psql utility. Use -d to specify the target database. You can omit -d if the file already includes its own connection commands, as with pg_dump -C or pg_dumpall output. ### **Restore from a custom archive backup file (.bak)** `$ pg_restore -d db_name /path/to/your/file/db_name.bak -c -U db_user` Restore a custom archive backup (.bak) using the pg_restore utility. The target database must already exist, since` -c` alone drops and recreates objects within it, but doesn't create the database itself. [We put together a guide to help you restore your PostgreSQL database](https://www.tigerdata.com/learn/a-guide-to-pg_restore-and-pg_restore-example). ## **Configuration** ### **Stop, start, and restart the PostgreSQL service** `service postgresql stop` Stop the PostgreSQL service through the root user on Linux. `service postgresql start` Start the PostgreSQL service through the root user on Linux. `service postgresql restart` Restart the PostgreSQL service through the root user on Linux. If you're running from a non-root user, prefix your command with sudo, and make sure the non-root user is already in the sudoers list. Some distributors don't provide these commands, so check your distribution's documentation. ### **Display configuration parameters** `show all;` List all current runtime configuration parameters in psql. ### **Display configuration parameters using SQL** `select * from pg_settings;` List all current runtime configuration parameters using SQL, with additional details including descriptions. ### **Show the current setting for max_connections** `SELECT current_setting('max_connections'); current_setting ----------------- 100 (1 row)` Display the current value set for the max_connections parameter using SQL. ### **Show the PostgreSQL config file location** `show config_file; config_file ------------------------------------------ /etc/postgresql/17/main/postgresql.conf (1 row)` Show the PostgreSQL configuration file location in psql. The PostgreSQL configuration files are stored in the directory shown by the command above. The main configuration file is postgresql.conf. [Find out how to connect using pg_service.conf](https://www.tigerdata.com/blog/connecting-to-postgres-with-psql-and-pg_service-conf). ### **Display contents of the Postgres config file** `postgres@localhost:~$ less /etc/postgresql/17/main/postgresql.conf . . . . data_directory = '/var/lib/postgresql/17/main' # use data in another directory` `hba_file = '/etc/postgresql/17/main/pg_hba.conf' # host-based authentication file` `ident_file = '/etc/postgresql/17/main/pg_ident.conf' # ident configuration file` `listen_addresses = '*' # what IP address(es) to listen on, # comma-separated list of addresses, # defaults to 'localhost', use '*' for all port = 5432 . . . .` Key directives in postgresql.conf: - **data_directory**, which tells Postgres where the database files are stored - **hba_file**, which points to the host-based authentication file - **port**, which sets the TCP port number (default is 5432) ## **Keep your Postgres skills sharp** This cheat sheet covers the commands you'll reach for most often across databases, tables, queries, indexes, constraints, maintenance, monitoring, backup, restore, and configuration. Bookmark it and return whenever you need a quick reference. For production PostgreSQL workloads, pairing these commands with a managed service removes the operational overhead of backups, connection pooling, and scaling. [Try Tiger Cloud free](https://console.cloud.tigerdata.com/signup) to see how TimescaleDB handles the infrastructure so you can focus on your data. --- ## **Frequently asked questions** **What is the difference between \l and \l+ in psql?** \l lists all databases with their names, owners, encodings, and collation settings. \l+ adds extra columns showing the tablespace, size, and description (comments) for each database. Use \l+ when you need to compare database sizes or check which tablespace a database is using. **How do you create a read-only user in PostgreSQL?** Create the user with CREATE USER readonly_user WITH PASSWORD 'yourpassword';, then grant connection access with GRANT CONNECT ON DATABASE mydb TO readonly_user;, and grant schema usage and select privileges with GRANT USAGE ON SCHEMA public TO readonly_user; followed by GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;. This gives the user read access without write permissions. Note that this grants access only to tables that exist at the time you run it. To automatically extend read access to tables created in the future, also run ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;. **What is the difference between VACUUM and ANALYZE in PostgreSQL?** VACUUM reclaims storage occupied by dead row versions left behind by updates and deletes. ANALYZE collects statistics about table contents and stores them in pg_statistic so the query planner can choose efficient execution plans. Running VACUUM ANALYZE together handles both tasks in a single pass. **How do you check which queries are currently running in PostgreSQL?** Query the pg_stat_activity view with SELECT pid, usename, state, query FROM pg_stat_activity WHERE state = 'active';. This returns the process ID, username, session state, and the SQL text of every active query. You can then cancel a specific query using SELECT pg_cancel_backend(pid); with the relevant process ID. **What is the difference between a primary key and a unique constraint in PostgreSQL?** A primary key enforces uniqueness and also creates a NOT NULL constraint on the column, and each table can have only one primary key. A unique constraint enforces uniqueness but allows null values (multiple nulls are permitted), and a table can have multiple unique constraints. Use a primary key for the main row identifier and unique constraints for other columns that must not repeat. **When should you use pg_dump versus pg_dumpall?** Use pg_dump to back up a single database, which gives you a portable file you can restore to any PostgreSQL server. Use pg_dumpall when you need to back up every database in a cluster along with global objects such as roles and tablespaces. For most application backups, pg_dump is the right choice. **How do you find the largest tables in a PostgreSQL database?** Query pg_class joined with pg_namespace and use pg_size_pretty(pg_relation_size(C.oid)) to format the sizes. Filter out pg_catalog and information_schema to exclude system objects, then order by pg_relation_size(C.oid) DESC and limit to the top results. The monitoring section of this cheat sheet includes the full query. **What does the IMMUTABLE keyword mean in a PostgreSQL function?** IMMUTABLE tells PostgreSQL that the function always returns the same result for the same input values and never modifies the database. The query planner can use this guarantee to pre-evaluate the function at planning time rather than executing it once per row, which can significantly speed up queries that call the function repeatedly. **How do you safely update records in PostgreSQL without risking data loss?** Wrap your UPDATE statement in an explicit transaction by issuing BEGIN before the update and COMMIT after you've verified the result. If something looks wrong, issue ROLLBACK instead of COMMIT to undo all changes made since BEGIN. This pattern prevents partial updates from being committed to the database.