Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [physical-design]

40 questions
27
votes
1 answer

Composite Primary Key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will have TenantId (Guid / UNIQUEIDENTIFIER) field.…
Reynaldi
  • 371
  • 3
  • 5
14
votes
3 answers

What is the fastest way to fetch the last row from a table?

I have a PostgreSQL table, Prices, with the columns: price (Decimal) product_id (Int) There are also created_at and updated_at columns. Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the…
Mike81
  • 315
  • 1
  • 2
  • 11
12
votes
1 answer

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows and returns them according to their physical…
Juan Carlos Coto
  • 1,588
  • 5
  • 18
  • 25
8
votes
2 answers

Fastest way to split/store a long string for charindex function

I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function). I have tested this with a 1GB string and a 9-digit-substring using SQL Server,…
Werner Aumayr
  • 181
  • 5
7
votes
1 answer

One big table vs few smaller

Please take note that the example below is just an example, my scenario is way more complex and the way i'm trying to model it really makes sense Let's say i'm creating a table for audit events in one of my apps - so all of the "event_created",…
mbajur
  • 193
  • 1
  • 4
6
votes
3 answers

Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many

We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision involves multiple tables with one-to-many…
Martijn Pieters
  • 175
  • 9
6
votes
2 answers

Wide clustered index vs multiple narrow nonclustered indexes?

Say I have a contrived Student table like so: CREATE TABLE Student ( Id IDENTITY INT, SchoolId INT NOT NULL, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL ) Instinctively, I'd make Id the Primary Key (and thus the…
someguywhoneedssomehelp
  • 61
  • 3
5
votes
1 answer

Does it make sense to run a cluster of database servers in docker containers?

I'm currently looking into setting up a database cluster (MySQL Galera or Percona) but I have only 2 machines. However, according to many sources I've read (This article from the Mariadb KB), the minimal node size in the cluster should be 3, to…
patatepartie
  • 53
  • 1
  • 3
5
votes
2 answers

Separate stored procedures for inserts and updates?

I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures: InsertNewPerson UpdatePertsonById But I was thinking to write 1 stored procedure instead (SetPerson) which would…
Royi Namir
  • 1,233
  • 3
  • 13
  • 26
5
votes
1 answer

PostgreSQL Index Only Scan can NOT return ctid

I have this table in PostgreSQL 11: CREATE TABLE A (id bigint PRIMARY KEY, text text) Now I want to fetch the ctid of rows meeting some condition like id = 123. However, even using pg_hint_plan to hint PostgreSQL to use Index Only Scan, it will…
Qiushi Bai
  • 65
  • 5
5
votes
1 answer

Can you gain the performance benefits of partition elimination without changing SQL module or table definitions?

I work for a company that has a multi-tenant database model that doesn't currently use database partitioning. The ideal end-state would be adding a new TenantID column to every existing table, including this new column as the first column in every…
Matthew Sontum
  • 537
  • 1
  • 5
  • 13
5
votes
2 answers

Allocation strategy where tables regularly grow and shrink by large amounts

I have a SQL Server 2014 database with 100+ tables that have a 5% growth rate over the course of a year. Lots of reading, very little writing. This database is going to expand by another set of 20+ tables that regularly (quarterly) grow overnight by…
Clinton Pierce
  • 153
  • 4
4
votes
1 answer

What is the best practice for physical deployment of data marts?

We are just starting to get into business intelligence at our company. We have some architects who are designing our data warehouse and also our data marts. They are using star schema for both designs. The architects have stated that the design they…
Chris Aldrich
  • 4,916
  • 5
  • 34
  • 55
4
votes
3 answers

Performance of using a non-clustered index as primary key

Background I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER. The reason for considering UNIQUEIDENTIFIER is because there is a good chance we will…
musefan
  • 252
  • 4
  • 13
3
votes
1 answer

In SQL Server would misc pages be pushed out when an object grows within a mixed extent?

In SQL Server 2008-2012, objects (including tables) are first put in a mixed extent. An extent can accommodate up to 8 pages and accommodates more than one type of page (meaning: pages from more than one object). Say ExtentA has pages from objects…
LearnByReading
  • 711
  • 10
  • 26
1
2 3