Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
90 votes

Storing a re-orderable list in a database

Same answer from here https://stackoverflow.com/a/49956113/10608 Solution: make index a string (because strings, in essence, have infinite "arbitrary precision"). Or if you use an int, increment ...
Alexander Bird's user avatar
39 votes

How can I efficiently diff a CSV file against a database?

Since your goal is to produce a list of changes, not to change the stored records, the way to go is to simply export the database to text and wrangle it once to get into exactly the same CSV format. ...
Kilian Foth's user avatar
31 votes
Accepted

What are the best practices around retiring obsolete database columns?

If you want to keep the data, then it's not obsolete. Just leave it where it is. It's fine if some class mapped to a table doesn't map every column.
kevin cline's user avatar
  • 33.7k
22 votes
Accepted

How can I efficiently diff a CSV file against a database?

Since the roundtrip seems to be the issue, you could: either opt for a local solution, with the scaling issue you mentioned. (You could still try to split the task across several local nodes, each ...
Christophe's user avatar
  • 77.9k
15 votes

Storing a re-orderable list in a database

OK I face this tricky problem recently, and all the answers in this Q&A post gave many inspiration. The way I see it, each solution has its pros and cons. If the position field has to be ...
RayLuo's user avatar
  • 701
15 votes

Storing a re-orderable list in a database

I have seen people using a self-reference to refer to the previous (or next) value, but again, it seems like you would have to update a whole lot of other items in the list. Why? Say you take a ...
sqweek's user avatar
  • 251
12 votes
Accepted

Do I really need triggers for relational database, for example, PostgreSQL?

It depends on what kind of application system you are building: if you are creating an application-centric system which contains just one main application, with a dedicated database specifically for ...
Doc Brown's user avatar
  • 208k
9 votes
Accepted

Django web app: how to save thousands of variables per user

Using a column per question makes it difficult to add more questions later. Instead, model the relation between users and questions in your table. I.e. the questions are encoded explicitly as data, ...
amon's user avatar
  • 134k
9 votes

How can I efficiently diff a CSV file against a database?

There are already some good answers, but here's another possibility: if you could sort the CSV file by key and add a similar ORDER BY to the SQL query, you could compare the rows obtained from the ...
Daniel Koszta's user avatar
8 votes

What are the best practices around retiring obsolete database columns?

OK so your situation is that you want the old rows to have property C but the new ones not. This is equivalent to having a class inheritance relationship class All { string A; string B; } ...
Ewan's user avatar
  • 76.3k
6 votes

Django web app: how to save thousands of variables per user

You should normalise your database schema. This means that instead of just having a User table, you would also have a Questions table containing information about each question, then you would also ...
Sean Burton's user avatar
6 votes
Accepted

Sharding rule updating on DB scaling

Starting from V11, Postgres supports Declarative Partitioning where you can divide the table into partitions with built-in Hash partitioning strategy (modulus or round-robin). It also allows you to ...
lennon310's user avatar
  • 3,212
6 votes

HOWTO: Full-text search over an encrypted database?

You don't. The whole point of messaging privacy is that nobody except the sender and intended recipient are able to decrypt the messages. Not the platform provider, not their sysadmin, not some spy ...
Hans-Martin Mosner's user avatar
5 votes

How to store product and pricing history in a product database?

I'd opt to leave the current table as it is, and add a table to hold the pricing histories, which are related via your product id and carrying a timestamp, and a daily job to snapshot today's pricing ...
Thomas Carlisle's user avatar
5 votes

How to get real time notifications, when a database change (insert, update, delete) occurs?

For PostgreSQL I know a way to get notification from the database when a row changes. use trigger when insert / update / delete occurs. when event occurs send a notify to a client socket. be sure you ...
user275831's user avatar
5 votes

Race conditions in API calls within Golang microservices

If you are using a distributed system, you get the drawbacks of distributed system: ensuring consistency of your data model requires real care. TL;DR: avoid distributed systems if consistency is ...
amon's user avatar
  • 134k
5 votes

HOWTO: Full-text search over an encrypted database?

Obviously, you shouldn't have the key to decrypt messages, as pointed out by others. So how do you search on encrypted data? Well, there is homomorphic encryption. Theoretically it lets you apply any ...
Robert Bräutigam's user avatar
4 votes

Storing a re-orderable list in a database

Yes, the question is rather old and already has a couple of answers. Still, all of the solutions offered here are pretty complex. How about some simpler ones? The original question is about Wish List ...
Michal J Figurski's user avatar
4 votes
Accepted

Compile analytics from millions of rows in PostgreSQL

I don't see a necessity to change the whole db technology or infrastructure just because you need a little bit of optimizing here. Start with something simple like writing a stored procedure (or maybe ...
Doc Brown's user avatar
  • 208k
4 votes

Normalisation Vs Join , trade-off

Well, first of all, normalization in a relational database is not an absolute goal. There are certainly situations where maintaining a denormalized table or column does make sense from a performance ...
Robert Harvey's user avatar
4 votes

In the context of nested database relations (A->B->C), what are the considerations for having an additional "shortcut" foreign key (A->C)?

The pros of this approach are: the ease of a direct access to the connected object if you don't need the objects in the middle; potential performance improvement due to the reduction of joins (but ...
Christophe's user avatar
  • 77.9k
4 votes

Architecture for RESTful API and a web admin

Q: For the functionalities that are similar for the mobile and web admin, how can avoid duplicating the code? Essentially, encapsulating the domain and the business logic in their own components and ...
Laiv's user avatar
  • 14.7k
4 votes

Should I de-normalize my DB schema for a multi tenant application

There is a linguistic trap in your question. Multi-tenant can mean different things in your example: If tenant just happens to be an entity like any other and “multi-tenant” is about indirect one-to-...
Christophe's user avatar
  • 77.9k
4 votes
Accepted

Worried about too many joins when fetching multiple datatypes in a single postgres query

You will have to measure to see if performance is an actual problem. But your intuition about query performance is wrong: I am just worried that this many cascading queries might heavily affect ...
JacquesB's user avatar
  • 59.7k
4 votes

HOWTO: Full-text search over an encrypted database?

The nature of (good) encryption is that the cypher text (the encrypted data) bears no resemblance to the plain text (the unencrypted data). That makes it impossible to perform a full text search on ...
Bart van Ingen Schenau's user avatar
4 votes

Best way to store quite large JSON objects?

You have an interesting scenario with lots of solution that might be appropriate. You raise a good point that the amount of data might be an issue. As a worst case estimate, you might have 0.5MB per ...
amon's user avatar
  • 134k
3 votes
Accepted

Is it possible to store and query combinations of boolean-like information in an integer?

You can query a bit array using bitwise operators, and you can store bit arrays in an integer type. Bitwise operators are not an ANSI SQL specified feature, but most RDBMS have implemented them. Some ...
Samuel's user avatar
  • 9,197
3 votes

Scaling resolution of time series data

The query you gave us isn't very scalable because it will still have to scan the entire or a large range of rows. You've helped reduce the amount of data returned, but I believe it will still put more ...
Samuel's user avatar
  • 9,197
3 votes
Accepted

Role based Database Schema

One problem that I see with role-based (this means only users with the same roles can view these documents) is that if I create a document with this type as an intern, and later get promoted to a ...
Glorfindel's user avatar
  • 3,147

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