Database internals

SQL Server Datatypes: Common Modeling Dilemmas

by Jeffrey J. Keller
7 Jul 2015 When designing a database, early decisions can have a huge impact on the performance and storage requirements. These decisions can be difficult to change later, as most subsequent work will depend on the physical model. This article highlights some common design decisions, flaws, and misconceptions. Creating a Primary Key: Uniqueidentifer or Integer When defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.
Read more
Database internals

A Handy Guide to Solving PostgreSQL Problems

by Łukasz Woźniak
10 Jun 2015 Written in the frequently-asked-questions style, this book provides the best troubleshooting techniques for PostgreSQL database administrators. It covers all major aspects of managing a PostgreSQL database; from database installation through optimizing performance, to dealing with transaction locks or fixing replication, to handling hardware and software disasters. So if you’re getting an error message when working with PostgreSQL, let this book help you solve your problem. “Troubleshooting PostgreSQL” is the latest book by Hans-Jürgen Schönig, the recognized authority in the PostgreSQL community.
Read more
Database internals

PostgreSQL Database Replication

by Eduardo Piombino
2 Jun 2015 Motivation The main idea behind any type of database replication is to be able to tell your database to report to someone (usually to another database) via any of the mechanisms described below whenever changes to your main database have been made. Those changes will then be processed and applied to this second database, and if everything goes well, you will end up having an up to date replica of your main database running somewhere else.
Read more
Database internals

Why Do We Need Transaction Isolation Levels?

by Jakub Barejka
7 May 2015 Imagine that you’re implementing a system for a large bookstore. Many operations have to take place at the same time, multiple customers may simultaneously want to purchase the same book, prices of some books may change, new products are still being delivered, etc. As you know, a single action done by a user is run as a transaction in a database. Let's see what can happen if you allow multiple transactions to work on the same data.
Read more
Database internals

Slony-I Replication – Your Initial Setup

by Eduardo Piombino
24 Apr 2015 Slony-I is a popular PostgreSQL replication system. Basically, it allows you to automatically copy data from one database to other databases. It can also do a couple of other things, but in this article we will concentrate on replication and provide you with a step by step guide on how to configure your first Slony-I cluster in Windows. It will involve a master node and two slave nodes. The master node will be the database that will be replicated and the slaves will get updated as the master gets modified.
Read more
Database internals

Designing & Deploying a PostgreSQL Database in the Cloud

by Rafał Strzaliński
26 Mar 2015 Our users sometimes say to us: “Vertabelo is awesome but... how can I add the data?” Oh, come on! Vertabelo is not a database engine or a database administration tool. At least not at the moment. For now, Vertabelo is intended mainly to design database models. This doesn't mean that we don’t care about what you can do next with models created in our application. Of course, we do! That’s why we provide our users with some useful tools on our website and in our GitHub repository.
Read more
Database internals

Formatting Ad-Hoc Reports in PostgreSQL

by Rafał Strzaliński
18 Mar 2015 OR/M and noSQL guys see SQL as an awkward way to store and retrieve objects from the database. Here at Vertabelo, we see SQL as a useful language to query data. Making reports in pure SQL is a pure pleasure. Running an interactive query is almost the same experience as asking a human to provide information. At some point you’ll have a need to write a report in a few seconds.
Read more
Database internals

Virtual Column

by Patrycja Dybka
23 Feb 2015 The concept of views and function-based indexes has been known for many years. One of the brand new solutions is a virtual column – a feature introduced in Oracle 11g. Apart from database giant, some well known DB vendors, like MariaDB and SQL Server, support the idea of computed columns. So let’s give virtual columns a try and examine their basic usage. Generally, there are two kinds of virtual columns:
Read more
Database internals

Oracle Synonyms

by Patrycja Dybka
1 Oct 2014 Synonyms are a very powerful feature of Oracle. They are auxiliary names that relate to other database objects: tables, procedures, views, etc. They work like Unix hard links; a pointer to point to an object that exists somewhere else. Synonyms can be created as PRIVATE (by default) or PUBLIC. public synonyms are available to all users in the database. private synonyms exist only in specific user schema (they are available only to a user and to grantees for the underlying object) The syntax for a synonym is as follows:
Read more
Database internals

Date Arithmetic in PostgreSQL

by Agnieszka Kozubek
20 May 2014 If your default programming language, like mine, is Java, you most likely wince at the very thought of date arithmetic. (It’s changed for the better with Java 8 but Vertabelo is not there yet.) The date arithmetic API in PostgreSQL is like a breath of fresh air. Interval Arithmetic You can represent a time interval: select interval '2 days'; '2 days' select interval '3 hours'; 03:00:00
Read more