pfctdayelise + database   26

Comparing Database Types: How Database Types Evolved to Meet Different Needs | Prisma
Many types of databases exist, each with their own benefits. In this guide, we'll compare the relational, document, key-value, graph, and wide-column databases and talk about what each of them offer.

Database types, sometimes referred to as database models or database families, are the patterns and structures used to organize data within a database management system. Many different database types have been developed over the years. Some are mainly historic predecessors to current databases, while others have stood the test of time. In the last few decades, new types have been developed to address changing requirements and different use patterns.

Your choice of database type can have a profound impact on what kind of operations your application can easily perform, how you conceptualize your data, and the features that your database management system offers you during development and runtime. In this guide, we'll take a look at how database types have evolved over time and what advantages and trade-offs are present in each design.
september 2019 by pfctdayelise
3 Hazards of Transactional Test Isolation
1. In-Flight Transactions Can Interact
2. Your Application Code Will Need to Learn about Savepoints
3. Savepoints Are Not Transactions
testing  database 
august 2019 by pfctdayelise
Sqitch by theory
Sane database change management

Sqitch is a database change management application. What makes it different from your typical migration-style approaches? A few things:

No opinions
Sqitch is not integrated with any framework, ORM, or platform. Rather, it is a standalone change management system with no opinions about your database engine, application framework, or development environment.

Native scripting
Changes are implemented as scripts native to your selected database engine. Writing a PostgreSQL application? Write SQL scripts for psql. Writing a MySQL-backed app? Write SQL scripts for mysql.

Dependency resolution
Database changes may declare dependencies on other changes—even on changes from other Sqitch projects. This ensures proper order of execution, even when you’ve committed changes to your VCS out-of-order.

No numbering
Change deployment is managed by maintaining a plan file. As such, there is no need to number your changes, although you can if you want. Sqitch doesn’t much care how you name your changes.

Iterative development
Up until you tag and release your application, you can modify your change deployment scripts as often as you like. They’re not locked in just because they’ve been committed to your VCS. This allows you to take an iterative approach to developing your database schema. Or, better, you can do test-driven database development.
database  postgres 
march 2018 by pfctdayelise
SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers Oracle, MySQL, PostgreSQL, SQL Server, ...
Table of Contents

Preface — Why is indexing a development task?

Anatomy of an Index — What does an index look like?

The Leaf Nodes — A doubly linked list

The B-Tree — It's a balanced tree

Slow Indexes, Part I — Two ingredients make the index slow

The Where Clause — Indexing to improve search performance

The Equals Operator — Exact key lookup

Primary Keys — Verifying index usage

Concatenated Keys — Multi-column indexes

Slow Indexes, Part II — The first ingredient, revisited

Functions — Using functions in the where clause

Case-Insensitive Search — UPPER and LOWER

User-Defined Functions — Limitations of function-based indexes

Over-Indexing — Avoid redundancy

Bind Variables — For security and performance

Searching for Ranges — Beyond equality

Greater, Less and BETWEEN — The column order revisited

Indexing SQL LIKE Filters — LIKE is not for full-text search

Index Combine — Why not using one index for every column?

Partial Indexes — Indexing selected rows

NULL in the Oracle Database — An important curiosity

NULL in Indexes — Every index is a partial index

NOT NULL Constraints — affect index usage

Emulating Partial Indexes — using function-based indexing

Obfuscated Conditions — Common anti-patterns

Dates — Pay special attention to DATE types

Numeric Strings — Don't mix types

Combining Columns — use redundant where clauses

Smart Logic — The smartest way to make SQL slow

Math — Databases don't solve equations

Testing and Scalability — About hardware

Data Volume — Sloppy indexing bites back

System Load — Production load affects response time

Response Time and Throughput — Horizontal scalability

The Join Operation — Not slow, if done right

Nested Loops — About the N+1 selects problem in ORM

Hash Join — Requires an entirely different indexing approach

Sort-Merge Join ‌— Like a zipper on two sorted sets

Clustering Data — To reduce IO

Index Filter Predicates Intentionally Used — to tune LIKE

Index-Only Scan — Avoiding table access

Index-Organized Table — Clustered indexes without tables

Sorting and Grouping — Pipelined order by: the third power

Indexed Order By — where clause interactions

ASC/DESC and NULL FIRST/LAST — changing index order

Indexed Group By — Pipelining group by

Partial Results — Paging efficiently

Selecting Top-N Rows — if you need the first few rows only

Fetching The Next Page — The offset and seek methods compared

Window-Functions — Pagination using analytic queries

Insert, Delete and Update — Indexing impacts on DML statements

Insert — cannot take direct benefit from indexes

Delete — uses indexes for the where clause

Update — does not affect all indexes of the table
database  sql  postgres 
february 2018 by pfctdayelise
Readings in Database Systems, 5th Edition
Preface [HTML] [PDF]
Background introduced by Michael Stonebraker [HTML] [PDF]
Traditional RDBMS Systems introduced by Michael Stonebraker [HTML] [PDF]
Techniques Everyone Should Know introduced by Peter Bailis [HTML] [PDF]
New DBMS Architectures introduced by Michael Stonebraker [HTML] [PDF]
Large-Scale Dataflow Engines introduced by Peter Bailis [HTML] [PDF]
Weak Isolation and Distribution introduced by Peter Bailis [HTML] [PDF]
Query Optimization introduced by Joe Hellerstein [HTML] [PDF]
Interactive Analytics introduced by Joe Hellerstein [HTML] [PDF]
Languages introduced by Joe Hellerstein [HTML] [PDF]
Web Data introduced by Peter Bailis [HTML] [PDF]
A Biased Take on a Moving Target: Complex Analytics
by Michael Stonebraker [HTML] [PDF]
A Biased Take on a Moving Target: Data Integration
by Michael Stonebraker
november 2017 by pfctdayelise
OSS Database Survey
by Selena Deckelmann. may date quickly..
freesoftware  database 
january 2010 by pfctdayelise
Online Etymology Dictionary
This is a map of the wheel-ruts of modern English. Etymologies are not definitions; they're explanations of what our words meant and how they sounded 600 or 2,000 years ago.
etymology  linguistics  database  words 
february 2008 by pfctdayelise
Austronesian Basic Vocabulary Database: Main
This database contains 105,058 lexical items from 503 languages spoken throughout the Pacific region. These languages all belong to the Austronesian language family.
indonesian  database  linguistics 
february 2008 by pfctdayelise

Copy this bookmark: