Pull to refresh
69.58

SQL *

Domain-specific languageused in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system

Show first
Rating limit
Level of difficulty

Evaluating Performance: CosmosDB vs. Azure SQL

Level of difficulty Easy
Reading time 4 min
Views 4.9K

In the evolving landscape of database technology, choosing the right database management system is crucial for the efficiency and scalability of applications. This article presents a detailed comparison of the performance between Microsoft's CosmosDB and MS SQL Server. We'll examine how each database performs under various load conditions and share some interesting findings.

Read more
Total votes 3: ↑3 and ↓0 +3
Comments 0

PostgreSQL 17: Part 3 or Commitfest 2023-11

Level of difficulty Medium
Reading time 11 min
Views 521


The November commitfest is ripe with new interesting features! Without further ado, let's proceed with the review.


If you missed our July and September commitfest reviews, you can check them out here: 2023-07, 2023-09.


ON LOGIN trigger
Event triggers for REINDEX
ALTER OPERATOR: commutator, negator, hashes, merges
pg_dump --filter=dump.txt
psql: displaying default privileges
pg_stat_statements: track statement entry timestamps and reset min/max statistics
pg_stat_checkpointer: checkpointer process statistics
pg_stats: statistics for range type columns
Planner: exclusion of unnecessary table self-joins
Planner: materialized CTE statistics
Planner: accessing a table with multiple clauses
Index range scan optimization
dblink, postgres_fdw: detailed wait events
Logical replication: migration of replication slots during publisher upgrade
Replication slot use log
Unicode: new information functions
New function: xmltext
AT LOCAL support
Infinite intervals
ALTER SYSTEM with unrecognized custom parameters
Building the server from source

Read more →
Total votes 3: ↑3 and ↓0 +3
Comments 0

PostgreSQL 17: Part 2 or Commitfest 2023-09

Reading time 11 min
Views 962


We continue to follow the news of the PostgreSQL 17 development. Let's find out what the September commitfest brings to the table.


If you missed our July commitfest review, you can check it out here: 2023-07.


Removed the parameter old_snapshot_threshold
New parameter event_triggers
New functions to_bin and to_oct
New system view pg_wait_events
EXPLAIN: a JIT compilation time counter for tuple deforming
Planner: better estimate of the initial cost of the WindowAgg node
pg_constraint: NOT NULL constraints
Normalization of CALL, DEALLOCATE and two-phase commit control commands
unaccent: the target rule expressions now support values in quotation marks
COPY FROM: FORCE_NOT_NULL * and FORCE_NULL *
Audit of connections without authentication
pg_stat_subscription: new column worker_type
The behaviour of pg_promote in case of unsuccessful switchover to a replica
Choosing the disk synchronization method in server utilities
pg_restore: optimization of parallel recovery of a large number of tables
pg_basebackup and pg_receivewal with the parameter dbname
Parameter names for a number of built-in functions
psql: \watch min_rows

Read more →
Total votes 3: ↑3 and ↓0 +3
Comments 2

UX Designer Job Interview: 10 questions to answer, 5 questions to ask

Level of difficulty Easy
Reading time 6 min
Views 595

No matter how many degrees you have or how high your experience level is, your recruiters need to evaluate your knowledge of UX design as a whole. But keep in mind that a job interview is not an exam, so here you are expected not to recite the textbook definitions learned by heart, but rather share your personal understanding of UX and your role as a designer in general. Consider talking about how you define UX, what creates value in the design, what are the necessary parts of a UX design process, what are the current trends in UX. You might also be asked to explain the difference between UI and UX to see how you understand the role of each in the development process.

Read more
Rating 0
Comments 0

MSSQL: Table Rebuild and Reorg in highload 24/7 Environments

Level of difficulty Medium
Reading time 14 min
Views 1.7K

How do you deal with index fragmentation if your SQL server is working in high load environment with 24/7 workload without any maintenance window? What are the best practices for index rebuild and index reorganize? What is better? What is possible if you have only Standard Edition on some servers? But first, let's debunk few myths.

Myth 1. We use SSD (or super duper storage), so we should not care about the fragmentation. False. Index rebuild compactifies a table, with compression it makes it sometimes several times smaller, improving the cache hits ratio and overall performance (this happens even without compression).

Myth 2. Index rebuild shorten SSD lifespan. False. One extra write cycle is nothing for the modern SSDs. If your tempdb is on SSD/NVMe, it is under much harder stress than data disks.

Myth 3. On Enterprise Edition there is a good option: ONLINE=ON, so I just create a script with all tables and go ahead. False. There are tons of potential problems created by INDEX REBUILD even with ONLINE and RESUMABLE ON - so never run index rebuilds without controlling the process.

Finally, we will tackle the REBUILD vs REORGANIZE subject and what is possible to achieve if you have only Standard Edition.

Read more
Total votes 3: ↑3 and ↓0 +3
Comments 0

PostgreSQL 17: Part 1 or Commitfest 2023-07

Level of difficulty Medium
Reading time 8 min
Views 805
image

We continue to follow the news in the world of PostgreSQL. The PostgreSQL 16 Release Candidate 1 was rolled out on August 31. If all is well, PostgreSQL 16 will officially release on September 14.


What has changed in the upcoming release after the April code freeze? What's getting into PostgreSQL 17 after the first commitfest? Read our latest review to find out!

Read more →
Rating 0
Comments 0

PostgreSQL 16: Part 5 or CommitFest 2023-03

Level of difficulty Medium
Reading time 28 min
Views 1K

The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let’s take a look at some exciting new updates it introduced.

I hope that this review together with the previous articles in the series (2022-072022-092022-112023-01) will give you a coherent idea of the new features of PostgreSQL 16.

Read more
Total votes 10: ↑10 and ↓0 +10
Comments 0

PostgreSQL 16: Part 4 or CommitFest 2023-01

Reading time 16 min
Views 1.4K


We continue to follow the news of the PostgreSQL 16 release, and today, the results of the fourth CommitFest are on the table. Let's have a look.


If you missed the previous CommitFests, check out our reviews for 2022-07, 2022-09 and 2022-11.

Read more →
Total votes 7: ↑7 and ↓0 +7
Comments 0

Database selection cheat sheet: SQL or NoSQL?

Reading time 9 min
Views 4.1K

This is a series of articles dedicated to the optimal choice between different systems on a real project or an architectural interview.

This topic seemed relevant to me because such tasks can be encountered both at work and at an interview for System Design Interview and you will have to choose between these two types of DBMS. I plunged into this issue and will tell you what and how. What is better in each case, what are the advantages and disadvantages of these systems and which one to choose, I will show with several examples at the end of the article.

SQL or NoSQL?

Read more
Total votes 1: ↑1 and ↓0 +1
Comments 0

PostgreSQL 16: Part 3 or CommitFest 2022-11

Reading time 10 min
Views 1.3K

image


We continue to follow the news of the upcoming PostgreSQL 16. The third CommitFest concluded in early December. Let's look at the results.


If you missed the previous CommitFests, check out our reviews: 2022-07, 2022-09.


Here are the patches I want to talk about:


meson: a new source code build system
Documentation: a new chapter on transaction processing
psql: \d+ indicates foreign partitions in a partitioned table
psql: extended query protocol support
Predicate locks on materialized views
Tracking last scan time of indexes and tables
pg_buffercache: a new function pg_buffercache_summary
walsender displays the database name in the process status
Reducing the WAL overhead of freezing tuples
Reduced power consumption when idle
postgres_fdw: batch mode for COPY
Modernizing the GUC infrastructure
Hash index build optimization
MAINTAIN ― a new privilege for table maintenance
SET ROLE: better role change management
Support for file inclusion directives in pg_hba.conf and pg_ident.conf
Regular expressions support in pg_hba.conf

Read more →
Total votes 1: ↑1 and ↓0 +1
Comments 1

PostgreSQL 16: Part 2 or CommitFest 2022-09

Reading time 13 min
Views 1.6K


It's official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release.


Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code.


If you missed the July CommitFest, our previous article will get you up to speed in no time.


Here are the patches I want to talk about:


SYSTEM_USER function
Frozen pages/tuples information in autovacuum's server log
pg_stat_get_backend_idset returns the actual backend ID
Improved performance of ORDER BY / DISTINCT aggregates
Faster bulk-loading into partitioned tables
Optimized lookups in snapshots
Bidirectional logical replication
pg_auth_members: pg_auth_members: role membership granting management
pg_auth_members: role membership and privilege inheritance
pg_receivewal and pg_recvlogical can now handle SIGTERM

Read more →
Total votes 1: ↑1 and ↓0 +1
Comments 0

Queries in PostgreSQL. Nested Loop

Reading time 17 min
Views 2.3K

So far we've discussed query execution stagesstatistics, and the two basic data access methods: Sequential scan and Index scan.

The next item on the list is join methods. This article will remind you what logical join types are out there, and then discuss one of three physical join methods, the Nested loop join. Additionally, we will check out the row memoization feature introduced in PostgreSQL 14.

Read more
Total votes 4: ↑4 and ↓0 +4
Comments 0

Queries in PostgreSQL. Sort and merge

Reading time 19 min
Views 1.6K


In the previous articles, we have covered query execution stages, statistics, sequential and index scan, and two of the three join methods: nested loop and hash join.


This last article of the series will cover the merge algorithm and sorting. I will also demonstrate how the three join methods compare against each other.

Read more →
Total votes 3: ↑3 and ↓0 +3
Comments 0

Queries in PostgreSQL. Sequential Scan

Reading time 15 min
Views 2.2K

Queries in PostgreSQL. Sequential scan


In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of, and how it is executed.


In this article, I will demonstrate how the planner calculates execution costs. I will also discuss access methods and how they affect these costs, and use the sequential scan method as an illustration. Lastly, I will talk about parallel execution in PostgreSQL, how it works, and when to use it.


I will use several seemingly complicated math formulas later in the article. You don't have to memorize any of them to get to the bottom of how the planner works; they are merely there to show where I get my numbers from.

Read more →
Total votes 3: ↑3 and ↓0 +3
Comments 0

Queries in PostgreSQL. Statistics

Reading time 18 min
Views 5.4K

In the last article we reviewed the stages of query execution. Before we move on to plan node operations (data access and join methods), let's discuss the bread and butter of the cost optimizer: statistics.

Dive in to learn what types of statistics PostgreSQL collects when planning queries, and how they improve query cost assessment and execution times.

Read more
Total votes 4: ↑3 and ↓1 +2
Comments 0

Authors' contribution