Skip to main content
127 votes
Accepted

Best database and table design for billions of rows of data

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem....
Mr.Brownstone's user avatar
82 votes

Best database and table design for billions of rows of data

PostgreSQL and BRIN indexes Test it for yourself. This isn't a problem on a 5 year old laptop with an ssd. EXPLAIN ANALYZE CREATE TABLE electrothingy AS SELECT x::int AS id, (x::int % ...
Evan Carroll's user avatar
  • 63.6k
78 votes
Accepted

What is the best way to store an email address in PostgreSQL?

Custom DOMAINs I don't think using citext (case-insensitive) is enough[1]. Using PostgreSQL we can create a custom domain which is essentially some defined constraints over a type. We can create a ...
Evan Carroll's user avatar
  • 63.6k
75 votes
Accepted

Is it a bad practice to have several mutually exclusive one-to-one relationships?

The different types of cars are an instance of a general problem that surfaces over and over again in data modeling. It is called "generalization/specialization" in ER modeling, and "superclass/...
Walter Mitty's user avatar
  • 4,346
64 votes
Accepted

Are composite primary keys bad practice?

To say that the use of "Composite keys as PRIMARY KEY is bad practice" is utter nonsense! Composite PRIMARY KEYs are often a very "good thing" and the only way to model natural ...
Vérace's user avatar
  • 30.2k
62 votes
Accepted

Do SSDs reduce the usefulness of Databases

There are some things in a database that should be tweaked when you use SSDs. For instance, speaking for PostgreSQL you can adjust effective_io_concurrency, and random_page_cost. However, faster reads ...
Evan Carroll's user avatar
  • 63.6k
52 votes

Should binary files be stored in the database?

Do not store files in a database. Everyone, without exception, that can run any RDBMS on the market already has a database specifically for storing files, and the RDBMS itself is using it! That ...
Evan Carroll's user avatar
  • 63.6k
52 votes
Accepted

Not using foreign key constraints in real practice. Is it OK?

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this ...
Michael Green's user avatar
45 votes
Accepted

varchar(255) or varchar(256)?

Size each and every column appropriately. Do NOT use a "standard" size for each column. If you only need 30 characters, why create a column that can handle 255? I'm so glad you're not advocating ...
Hannah Vernon's user avatar
  • 70.2k
35 votes

Which collation should I use for biblical Hebrew?

First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew. Second: Regardless of anything else, you want to use the newest set of ...
Solomon Rutzky's user avatar
32 votes
Accepted

Designing a friendships database structure: Should I use a multivalued column?

Managing an individual piece of information Assuming that, in your business domain, a User can have zero-one-or-many Friends; a Friend must first be registered as a User; and you will search for, and/...
MDCCL's user avatar
  • 8,520
31 votes
Accepted

Using MAX text or more specific, smaller type

Should I always use (n)varchar(max) for text columns? No. For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (...
Paul White's user avatar
  • 86.3k
31 votes

Constraint - one boolean row is true, all other rows false

Variant 1 Since all you need is a single row with standard = true, set standard to null in all other rows. Then a plain UNIQUE constraint works, since null values do not violate it: CREATE TABLE ...
Erwin Brandstetter's user avatar
30 votes
Accepted

Should I explicitly DENY UPDATE to columns that should not be updated?

The argument doesn't make sense. I always want the controls and constraints as close to the data as possible. Putting it in the application layer means it only affects the people using the application ...
Aaron Bertrand's user avatar
29 votes

Modelling a database structure for multiple user types and their contact information

According to my interpretation of your description of the relevant business context, you are dealing with a supertype-subtype1 structure where (a) Actor, Director and Writer are entity subtypes of (b) ...
MDCCL's user avatar
  • 8,520
27 votes
Accepted

What is the right data type to store only -1 0 and 1 in postgres column?

If you want to save space, you can use the "char" data type. It stores a single byte. you can cast integer or text to "char": SELECT 'u'::"char", 'd'::"char", '...
Laurenz Albe's user avatar
  • 53.4k
27 votes

How do others know what is delivery address and invoice address if they are in the same table?

An address is an address. There’s no need to store the same address details twice because it’s used for a delivery address and also invoicing. In your orders table you would have columns like ...
Andrew Sayer's user avatar
  • 2,678
27 votes
Accepted

In PostgreSQL, how do I make it so that either column A or column B must be non-null?

You need a table-level check constraint: alter table <name> add constraint either_email check (email is not null or p2pmail is not null); If you're only allowed to enter one, but not both:...
Colin 't Hart's user avatar
26 votes
Accepted

Why would a primary key value change?

If you were using a person's name as a primary key and their name changed you would need to change the primary key. This is what ON UPDATE CASCADE is used for since it essentially cascades the change ...
Hannah Vernon's user avatar
  • 70.2k
26 votes
Accepted

Preferred way to store DateTime

Storing the data in a single column is the preferred way, since they are inextricably linked. A point in time is a single piece of information, not two. A common way of storing date/time data, ...
Hannah Vernon's user avatar
  • 70.2k
25 votes
Accepted

DEFAULT CONSTRAINT, worth it?

I assume that the database is checking always default values, even if I supply the correct value, hence I'm doing the same job twice. Um, why would you assume that? ;-). Given that Defaults exist to ...
Solomon Rutzky's user avatar
25 votes

varchar(255) or varchar(256)?

Others have already pointed out that that the number of bytes required to store the length is fixed. I wanted to focus on this part in your question: Does it matter anymore at this point? You have ...
Joe Obbish's user avatar
  • 32.5k
24 votes
Accepted

How to model an entity type that can have different sets of attributes?

According to your description of the business environment under consideration, there exists a supertype-subtype structure that encompasses Item —the supertype— and each of its Categories, i.e., Car, ...
MDCCL's user avatar
  • 8,520
22 votes
Accepted

Quick nearest neighbor search in the 150-dimensional space

PostgreSQL 9.6 using cube First install the cube extension CREATE EXTENSION cube; Now we will create some n-dimensional space with 100,000 points in 50 dimensions. In addition we'll add a GIST ...
Evan Carroll's user avatar
  • 63.6k
22 votes

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible. Now imagine a bug which happens very rarely, and causes ...
peterh's user avatar
  • 2,089
21 votes

Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

According to Martin Fowler, there are 3 approaches to the problem of table inheritance: Single Table Inheritance: One table represents all types. Unused attributes are NULLed. Concrete Table ...
Remus Rusanu's user avatar
  • 51.9k
21 votes
Accepted

Separate month and year columns, or date with day always set to 1?

Personally if it's a date, or can be a date, I suggest always storing it as one. It's just easier to work with as a rule of thumb. A date is 4 bytes. A smallint is 2 bytes (we need two) ... 2 bytes: ...
Evan Carroll's user avatar
  • 63.6k
21 votes

Constraint - one boolean row is true, all other rows false

You can use a Filtered index create table test ( id int primary key, foo bool ); CREATE UNIQUE INDEX only_one_row_with_column_true_uix ON test (foo) WHERE (foo); --> where foo is ...
McNets's user avatar
  • 23.8k
20 votes
Accepted

What can be the downside of always having a single integer column as primary key?

Other than additional disk space (and in turn memory usage and I/O), there's not really any harm in adding an IDENTITY column even to tables that don't need one (an example of a table that doesn't ...
Aaron Bertrand's user avatar
19 votes
Accepted

Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer....
Brent Ozar's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible