Database   188844

« earlier    

Storing hierarchical data: Materialized Path – Bojan Živanović
Web applications often need a way to represent and store hierarchies.
A menu with its submenus. A category with its subcategories. A comment and its replies.
Storing the hierarchy, and later reconstructing it from the stored data is just a part of the puzzle. We also need a way to find the parents or children of an item. We need to be able to re-parent an item (move it to another part of the hierarchy). Finally, there is the need to order items in a way that reflects their position in the hierarchy.
database  programming 
5 hours ago by epochblue
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 
18 hours ago by pfctdayelise
Database Design - Introduction
Looks good!
• Introduction
Basic UML & SQL
• Models
• Classes & schemes
• Rows & tables
• Associations
• Keys
UML design
• Many-to-many
• Many-to-many 2
• Subkeys
• Repeated attributes
• Multivalued attributes
• Domains
• Enumerated domains
• Subclasses
• Aggregation
• Recursive associations
• Normalization
SQL technique
• Queries
• Join
• Multiple joins
• Join types
• Functions
• Subqueries
• Union & minus
• Views & indexes
design  database  programming  sql  db  dba  tables 
19 hours ago by w1nt3rmut3
6 Real World Tips to Keep Your MS SQL Server Database Healthy
Learn how to maintain the health of your SQL server database with these quick and simple tips.
Healthy  SQL  server  database  Shrink  the  Perform  Regular  Backups  Verify  Monitor  Deadlocks  fix 
22 hours ago by DataNumen
Symphytum Project
Neat, simple collection tracker. An easy way to make a "database" of fields, with a GUI. It's not relational and doesn't have a way to view the form without potentially changing the interface (eg: dragging a text box somewhere else, etc) but it's definitely easy to use. And it works on any OS.
filemaker  database  collections  software 
yesterday by robduarte
GitHub - bloomberg/comdb2: Bloomberg's distributed RDBMS
Comdb2 is a clustered RDBMS built on Optimistic Concurrency Control techniques. It provides multiple isolation levels, including Snapshot and Serializable Isolation. Read/Write transactions run on any node, with the client library transparently negotiating connections to lowest cost (latency) node which is available. The client library provides transparent reconnect.

Work on Comdb2 was started at Bloomberg LP in 2004 and it has been under heavy development since.
yesterday by kbonnet
Knex.js - A SQL Query Builder for Javascript
Knex.js is a "batteries included" SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift
javascirpt  query  db  database  sql 
yesterday by steffenfiedler
Your Database Should Work Like a CDN | Cockroach Labs
Optimizing distributed database performance is complicated––with tons of moving parts, it’s a difficult knot to begin untangling. While there are many strategies to squeeze the most out of a complicated piece of software, one of the most prudent is to look at how other types of tech have solved their performance problems.  Database  Databases 
yesterday by GameGamer43
Distributed Counters  |  Firebase
"In Cloud Firestore, you can only update a single document about once per second, which might be too low for some high-traffic applications."

Confirmed at

It's not a plan limitation. You can't pay to remove it.

Workaround: create "shards". WTF.
against  Firestore  database  limitation 
yesterday by dandv
Mastering ActiveRecord and Arel
Excellent guide to Arel & ActiveRecord. Wish it was not in presentation form, though.
ruby  rails  guide  presentation  slides  programming  development  database 
yesterday by Mr0grog

« earlier    

related tags

1980s  advice  against  analytics  apache  api  appsec  architecture  archive  argument  art  authentication  backups  boxoffice  browser  brussels  business  c#  cap  cartoon  cassandra  class  cli  clojure  collections  comparison  compression  compromised  connection  contacts  coolstuff  critique  csv  data-analysis  data  database-security  database  databases  dataset  date  db  dba  deadlocks  design  dev  development  devops  directory  directorysvc  distributed  dna  dump  ecs  editor  education  email  encryption  engine  er  example  excel  exploit  export  facebook  fb  filemaker  film_reviews  firestore  fix  food  foss  fractal  genealogy  genetics  github  glossary  gluten  golang  google  graphql  guide  gun  hack  hacking  haskell  healthy  import  java  javascirpt  javascript  jdbc  json  keyvaluestore  law  library  limitation  linklist  linux  list  lmdb  log  logging  logsdb  lz4  machinelearning  maps  metrics  migrations  monitor  monitoring  movies  mssql  music  mysql  node.js  office  opendirectory  openldap  opensource  optimisation  orm  password  payload  pentest  perform  performance  pg  plaintext  politics  postgres  postgresql  presentation  presentations  prisma  privacy  productivity  prog  programming  pwned  python  query  r  rails  rdbms  record  reference  regular  relational_database  repository  research  review  rocksdb  ruby  saas  sample  security  sequel  sequential  server  shrink  sivers  slides  software  split  spreadsheet  spring  sql  sqlite  sqlsplitter  stanford  stats  storage  string  sysadmin  tableplus  tables  tappit  telegram  testing  the  theorem  time  time_series  timeseries  tokudb  tool  tools  tree  trump  tsql  tuning  tutorial  twitter  ubuntu  vba  verify  visualization  vulnerability  wal  web  wiki  youtube  zstandard  zstd 

Copy this bookmark: