Whereas most database systems employ locks for concurrency control, PostgreSQL does things a little differently: it maintains data consistency by using a multi-version model, otherwise known as Multi-Version Concurrency Control, or MVCC for short. As a result, when querying a database, each transaction sees a snapshot of data as it was some time before, regardless of the current state of the underlying data. This prevents the transaction from viewing inconsistent data that could be caused by other concurrent transaction updates on the same data, and provides transaction isolation for each database session. This blog article will provide a brief overview of how the MVCC protocol works as well as cover some of the pros and cons of the MVCC approach.
At the top of Navicat Monitor 3's Query Analyzer screen, there's a chart that shows queries with the longest wait times:
It's essential to identify laggard queries because they can bring everything crashing to a crawl.
The idea behind database auditing is to know who accessed your database tables and when, along with what modifications were made to them. It's not only considered to be the standard minimum requirement for any enterprise level application, but is also a legal requirement for many domains such as banking and cybersecurity. Database Audit Trails are essential in investigating all sorts of application issues such as unauthorized access, problematic configuration changes, and many more.
In today's blog, we're going to add logging to the MySQL Sakila Sample Database to audit the rental table. It's a key table because the database represents the business processes of a DVD rental store.
A table column, such as one that stores first names, may contain many duplicate values. If you're interested in listing the different (distinct) values, there needs to be a way to do so without resorting to complex SQL statements. In ANSI SQL compliant databases like PostgreSQL, SQL Server, and MySQL, the way to select only the distinct values from a column is to use the SQL DISTINCT clause. It removes duplicates from the result set of a SELECT statement, leaving only unique values. In this blog article, we'll learn how to use it.
Stored Procedures, Functions, and Views
Welcome to the 3rd and final installment on SQL naming conventions. In Part 1, we covered the rules for naming tables, while Part 2 explored conventions for column names. This installment will offer some guidelines for naming other database objects such as Stored Procedures, Functions, and Views.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
