Navicat Blog

Creating Views in Navicat 16 May 19, 2023 by Robert Gravelle

As part of the process of normalizing database tables, redundant columns are extracted from higher level tables into separate subsidiary ones. This often occurs due to some fields having a one to many relationship with the parent entity. For example, take the following model that was generated using Navicat Data Modeler:

ups_model (189K)

Multi-Version Concurrency Control in PostgreSQL May 12, 2023 by Robert Gravelle

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.

Setting Query Timeouts in PostgreSQL May 5, 2023 by Robert Gravelle

At the top of Navicat Monitor 3's Query Analyzer screen, there's a chart that shows queries with the longest wait times:

Screenshot_Navicat_Monitor_LongRunningQueries (102K)

It's essential to identify laggard queries because they can bring everything crashing to a crawl.

Implement Audit Trail Logging Using Triggers Apr 28, 2023 by Robert Gravelle

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.

Selecting Distinct Values From a Relational Database Apr 14, 2023 by Robert Gravelle

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.

Navicat Blogs
Feed Entries
Blog Archives