Skip to main content
4 votes
Accepted

Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?

The SELECT query you show could benefit a lot from an index-only scan after including version_stamp in the PK index. (Or adding an additional multicolumn index to cover that.) That's assuming your ...
Erwin Brandstetter's user avatar
4 votes
Accepted

How do I protected my sensitive data (financial) from website/CRM programmers?

Practically speaking, you're quite limited in what you can do - especially without significant expense, which is usually not tolerable to those seeking a solution. An analogy I sometimes use, is that ...
Steve's user avatar
  • 511
4 votes
Accepted

Is COMPACT a better format for fixed length rows than DYNAMIC?

For the table you show, I don't think COMPACT or DYNAMIC row format makes any significant difference. In modern MySQL, DYNAMIC row format is the default. It is pretty much the same as COMPACT, except ...
Bill Karwin's user avatar
  • 15.1k
4 votes
Accepted

Design: What should be my PK, FK etc

Each "main" Entity (or Table) represents instances (i.e. Records) of an object that can exist in their own right, in the absence of any other Entity, e.g. a Person. The attribute that ...
Phill  W.'s user avatar
  • 8,860
3 votes
Accepted

Database design - normalisation query

Your solution is not equally valid since it has redundancy that can be eliminated. Consider a customer who books 10 seats for a certain show. In your case you have 10 rows (each with 3 items, for a ...
Renzo's user avatar
  • 4,080
3 votes

Moving to SQL. How to design the database structure for intensive updates on JSON fields?

I think that that is a bad design for any relational database, but particularly for PostgreSQL. Every time you modify the value of one attribute in a JSON value, PostgreSQL will create an entirely ...
Laurenz Albe's user avatar
  • 53.5k
3 votes
Accepted

Which is the best way to model different types of people and relationships in a database?

In this case, is it correct (especially following database normalization) to create a table called person and leave the FK in person_natural and person_legal only so as not to have to create ...
Giannis Tsimarakis's user avatar
3 votes
Accepted

Deleting a row in one table should also delete the corresponding rows in another table

You can add a foreign key constraint to water_types that will do what you want: ALTER TABLE water_types ADD FOREIGN KEY (user_id) REFERENCES station (user_id) ON DELETE CASCADE;
Laurenz Albe's user avatar
  • 53.5k
3 votes
Accepted

database design for tree structure with just one level

Simplest solution is making the parent node into separate table with the same set of fields*. Then no nested references would be possible. For example, you could have following entities (SQL-like ...
Yano_of_Queenscastle's user avatar
3 votes

ERROR: duplicate key value violates unique constraint "sometable_pkey"

I want to reduce the size the db occupies, by changing the data types of varchar (yes/no domain), to int. That would actually increase the storage size on disk if anything. integer occupies 4 bytes ...
Erwin Brandstetter's user avatar
3 votes

Is COMPACT a better format for fixed length rows than DYNAMIC?

Another option would be to use multiple joins, to avoid grouping and counting and leveraging the fact that after the first few joins there are less and less rows satisfying the conditions: SELECT t1....
Andrea B.'s user avatar
  • 1,613
3 votes

Is COMPACT a better format for fixed length rows than DYNAMIC?

FIXED vs DYNAMIC made a tiny difference in MyISAM. For InnoDB, there is essentially no such thing. COMPACT vs DYNAMIC says what happens when a "big" column exists in a row. CHAR(3) does ...
Rick James's user avatar
  • 78.6k
3 votes

Database for large matrix

Using a relational database, you can define a Cell table like this: Cell ---- Matrix Row Column Value The primary key would be matrix, row, column. You would need an additional unique index on matrix,...
Gilbert Le Blanc's user avatar
3 votes

MySQL solution for many to many associative table that scale over billion entries

Preface I think you're unfortunately misunderstanding: How databases work at their core level, and What some of the people of this community have been trying to communicate to you in regards to that. ...
J.D.'s user avatar
  • 38.1k
3 votes
Accepted

Transactional Replication - how to set up alerts/notification to send alerts before transaction log space is full on a Publisher?

How can i get alert before my publisher's t-log space is full due to any issue in replication what alerts should I use which will tell me that the log-space is getting full on publisher? You can use ...
Aleksey Vitsko's user avatar
2 votes

Postgres jsonb vs composite type performance differences

Edit: These tests were performed in Postgres 12.16. When I ran the queries in the selected answer against my own data, I found significantly different results. In particular, the LATERAL JOIN to ...
Robert's user avatar
  • 123
2 votes
Accepted

Separate small column updated a lot from large jsonb columns to optimize performance?

No, that is not necessary. The large JSON values seem to be stored out of line in a TOAST table. Then the update of d only has to write a new row to the main table, but the TOAST values stay the same. ...
Laurenz Albe's user avatar
  • 53.5k
2 votes
Accepted

Database design for a limited historical data per user

Plan A: Don't enforce the "limit of 5" when inserting. We can probably handle a billion rows without stressing performance or disk space. Plan B: As you insert a new timer for a user, ...
Rick James's user avatar
  • 78.6k
2 votes
Accepted

How to let you change the order of products?

But now they want that the administrator can change the order, so he can set a custom order. Which is the best way to implement this? You would want to add another column to your products table ...
J.D.'s user avatar
  • 38.1k
2 votes
Accepted

Enforce unique constraint without denormalization

If a task can belong only to one service, the second data model is the correct one. To enforce your constraint, you have to add a redundant copy of environment to task: add a new column environment ...
Laurenz Albe's user avatar
  • 53.5k
2 votes

Seeking Guidance on Storage Models for Frequently Executed SQL Statement

SELECT AVG(StockUnits) FROM PRODUCT; If the problem is a dumb query, the solution is to not do the dumb query. It's like count(*) on a huge table. Who needs that result? I mean, if at least there was ...
bobflux's user avatar
  • 1,551
2 votes
Accepted

Find and reserve available spot in parking lot interview question

Depending on your DBMS, you can do something like this: UPDATE table1 SET availability = false WHERE availability = true LIMIT 1 /* Find only the first available entry */ ...
Phill  W.'s user avatar
  • 8,860
2 votes

How do I build a data model for a grid of XY data consisting of many to many relationships?

The Data Storing the same combination of participants twice is a serious normalization error and should not be considered as an option. The data that is unique is in one of the corresponding ...
Giannis Tsimarakis's user avatar
2 votes
Accepted

Optimizing Negative Int/BigInt Ranges in SQL Server: Strategies and Benefits

It doubles the range of values that can be used, but I would recommend seeding with 1 rather than -2,147,483,648 etc. If you run out of identity values starting with -2,147,483,648 this is a bigger ...
David Wiseman's user avatar
2 votes

Is COMPACT a better format for fixed length rows than DYNAMIC?

Count is notoriuos for being slow. But one of the performace problem can be addressed.by eliminating the IN clause, which would need to make a full table scan, ehivh xou would see if you make an ...
nbk's user avatar
  • 8,434
2 votes

Design: What should be my PK, FK etc

If you're new to sql: (set, kopf, time) is a tuple. Saying (set, kopf, time) is unique means there can be only one row with the specific values (set, kopf, time). It does not mean any of the columns ...
bobflux's user avatar
  • 1,551
2 votes
Accepted

Customer Address Design Probelm

One way to implement such a constraint is by using a filtered unique index: CREATE UNIQUE NONCLUSTERED INDEX foobar ON customeraddress (customerid) WHERE isdefault = 'Y' Fiddle
mustaccio's user avatar
  • 26.3k
2 votes
Accepted

are my expectations for mysql unrealistic?

Q: What does the query plan show? A: Use EXPLAIN ANALYZE to see. My guess is it's scanning a large portion of, if not the entire table to locate your data. This is because of the following issues: ...
J.D.'s user avatar
  • 38.1k
2 votes
Accepted

How are columnar databases faster for querying?

Generally, there are three main reasons columnar data storage is preferred for data warehousing and analytics workloads: Column storage typically compresses better than row storage because values are ...
Paul White's user avatar
  • 86.4k
2 votes

MySQL solution for many to many associative table that scale over billion entries

The best solution for your problem is what you already designed. That's because all RDBMS are designed to handle exactly that: a bunch of tables, with many-to-many relations, and potentially billions ...
Andrea B.'s user avatar
  • 1,613

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