PostgreSQL DBA

What commands are used to control transactions in PostgreSQL? ans: There are 3 main commands to control transactions in PostgreSQL: BEGIN TRANSACTION or simply BEGIN: To start a transaction. COMMIT or END TRANSACTION: To save the changes. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command. ROLLBACK: It’s used to undo transactions that have not already been saved to the database.

What is partitioning in PostgreSQL? It’s the process of splitting a large table into smaller pieces. It can be done through several methods, including range partitioning, list partitioning, and hash partitioning.

What is an index in PostgreSQL? An index is a special data structure related to a table and used for storing its important parts and enabling faster data search and retrieval. This is achieved by creating a sorted data structure that allows PostgreSQL to locate rows more quickly compared to a full table scan. Indexes are especially efficient for large databases, where they significantly enhance query performance.

What is Multi-version Concurrency Control in PostgreSQL? Multi-version Concurrency Control or MVCC is an advanced technique in PostgreSQL. It enhances database performance in multi-user scenarios. This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows. Put simply, MVCC creates multiple versions of a single database record, enabling various transactions to access different versions of one database record without conflicting with one another, thereby allowing simultaneous transitions.

What is a constraint in PostgreSQL? Ans: A set of conditions defining the type of data that can be input into each column of a table. Constraints are used to ensure data integrity in a table and prevent undesired actions.

https://dev.to/nightbird07/exploring-the-limitations-of-postgres-partitioning-lessons-learned-and-best-practices-5cj9 Exploring the Limitations of Postgres Partitioning: Lessons Learned and Best Practices

https://sparktpoint.com/postgresql-partition-by/ PARTITION BY in PostgreSQL

https://www.postgresql.fastware.com/postgresql-insider-prt-ove Partitioning

https://www.timescale.com/learn/when-to-consider-postgres-partitioning When to Consider Postgres Partitioning

https://rasiksuhail.medium.com/guide-to-postgresql-table-partitioning-c0814b0fbd9b#:~:text=Hash%20partitioning%20in%20PostgreSQL%20is,Partition%20makes%20querying%20faster. Guide to PostgreSQL Table Partitioning

https://www.cybertec-postgresql.com/en/how-to-corrupt-your-postgresql-database/ How to corrupt your PostgreSQL database

https://www.highgo.ca/2021/01/27/avoiding-identifying-and-dealing-with-postgresql-database-corruption-part-1/ Avoiding, Identifying, and dealing with PostgreSQL database corruption

https://www.linkedin.com/pulse/understanding-data-corruption-postgresql-shyam-kumar-9kvwc Understanding Data Corruption in PostgreSQL

https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-i-paging-2f748a078d22 Deep dive into Storage Engine of DBMS Episode I(Paging, Buffer Pool Manager, Write Ahead Log)

https://www.youtube.com/watch?v=sP5nbdNxaQc A PostgreSQL fork for horizontal scalability

The postmaster ID will be assigned once the cluster is started based on this ID other background processes will starts and running on different ID with the same parent ID. This postmaster ID will changes when the cluster is stop and starts.

https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases A list of PostgreSQL derived forks and rebranded distributions in alphabetical order.