2022 Developer Survey is open! Take survey.

Questions tagged [postgres]

PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).

Filter by
Sorted by
Tagged with
0 votes
0 answers
36 views

Best practices to deal with realtime data in REST API, Postgres and Redis

Overview Today we have a monolithic service that deals with realtime time data. The monolithic are composed by follow stack: Django + AuroraDB Cluster (Postgres AWS) + Varnish cache and cloudfront at ...
user avatar
0 votes
1 answer
58 views

Synchronous replication of databases

Say we have a software system with a web server serving writes to a Master DB. This Master DB is then replicated to several read-only DB replicas. From my understanding, the process of asynchronous ...
user avatar
0 votes
4 answers
332 views

HOWTO: Full-text search over an encrypted database?

I want to adhere to the best practices and obviously encrypt the data sent to me by user. And I also want to provide the full-text search feature to the user (user can only search their own data, if ...
user avatar
  • 75
0 votes
0 answers
21 views

DRF API - using Multitable Inheritance vs "profiles"

I'm looking forward to your opinion on how to do this. This is a backend that will be served as a Django Rest Framework API. There will be multiple types of properties. Properties have approximately ...
user avatar
  • 119
2 votes
1 answer
48 views

SQL: Analytics event value that can be string or number

I'm using a PostgreSQL database and I want to create an analytics table that has a many-to-one relationship with an analytics_type table where analytics_type_id is the foreign key. The table also has ...
user avatar
  • 47
-1 votes
1 answer
81 views

Commit instead of rollback? [closed]

Simple example (PSEUDO CODE): for (int i = 0; i < 100; i++) { START TRANSACTION; SELECT id, name FROM employees WHERE id = i; IF (someFunction(id)) { ROLLBACK; ...
user avatar
  • 11
-1 votes
1 answer
79 views

Should I use a database as gateway to other services? [closed]

The context I have an IoT project where the sensors are sending data to my Postgres database. The sensors are manages by a stand-alone service which provides a REST API to query various information ...
user avatar
0 votes
1 answer
98 views

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

Its not exactly my use case (would take too long too explain), but imagine a task management application where tasks for employees are displayed in a list along with some information about that task. ...
user avatar
1 vote
2 answers
169 views

Reading a large CSV file and then loading data to a DB

I have a Django application of 2 GB running and I need to receive a CSV file of more than 1 GB, read it and load the data to a PostgreSQL DB in IBM Cloud. The problem is that if I receive the file, it ...
user avatar
0 votes
1 answer
72 views

Postgres: Storing a task instruction to be run on a schedule, with multiple workers

As a prerequisite I will say that we are wedded to postgres to provide this solution. This is a very broad problem, and I think it must be solved many times over, but unfortunately as it's so general ...
user avatar
  • 103
0 votes
1 answer
96 views

Processing large number of messages in a thread-safe manner

I have the following use case and would like to implement it in a thread-safe manner. Think of a Stack Overflow-like web application. Somebody upvotes a question. A new event is sent to the message ...
user avatar
  • 747
3 votes
2 answers
195 views

Foreign Keys and Eventual Consistency

We have a project that loads metric data from various etls and services into an AWS SQS queue, where that data is processed and inserted into a metrics database, also running on AWS RDS. When we ...
user avatar
  • 2,343
3 votes
1 answer
62 views

Reorderable parent/children hierarchy in a relational database

I'm struggling on a design question for my Ruby on Rails website. I'm working on an estimation tool for construction companies for renovation projects. It orders items this way by default: Room (ex: ...
user avatar
  • 149
0 votes
0 answers
71 views

What is the best practice for hierarchical container - item relationship in a SQL database?

I am in the process of designing a database organizing real world items in different storage locations. One main principle is that items can contain other items. So the relevant table is: An "...
user avatar
0 votes
0 answers
54 views

How can I design a secure content verification web application?

In my country, there is a high number of fraudulent doctor's sick letter as they are manually written on paper. I'm designing a web application to combat this issue and have the whole process ...
user avatar
1 vote
2 answers
443 views

Database Design: Storing price date wise, or date range wise?

I must store prices for various items on multiple dates. The table schema would look like this: CREATE TABLE date_wise_price ( item_code varchar, date date, price numeric(19,4) ) An ...
user avatar
2 votes
1 answer
116 views

Sharding rule updating on DB scaling

When you have sharding, horizontal partitioning, you have multiple DBMS instances, and your server app chooses which one to use by some rule that uses record IDs. Well, for example, we have ...
user avatar
  • 191
-1 votes
3 answers
241 views

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

I am working on a multi-tenant application that's using a shared Postgres db for all tenants. All entities in the DB are connected (directly or indirectly) to a tenant, for example: Tenant --has many--...
user avatar
0 votes
1 answer
311 views

Race conditions in API calls within Golang microservices

I have a microservice architecture running on Heroku. I am having some problems handling race conditions. The problem is, that service A: Needs to fetch the user's balance through an API call to ...
user avatar
-2 votes
2 answers
148 views

Caching expensive sql queries on first page load

I have a web application with an express/node backend using typeorm and PostgreSQL. The home page in my app is a query with lots of inner joins that shows the user a complex report. This query takes ...
user avatar
-1 votes
2 answers
186 views

Is it true that ORM allows for changing the type of SQL that you use easily?

Hey so I have been looking into ORM since my last question and I am wondering. Instead of worrying alot right now about whether I will use mysql, postgre, or sqlite, would it be better to use ORM ...
user avatar
  • 129
-5 votes
1 answer
67 views

Sync local database with remote

My client has a business which work mostly in remote areas where internet felicity is limited, We have a central database and the branches in remote areas need to connect to the central database. We ...
user avatar
  • 93
0 votes
1 answer
615 views

Doing data manipulation on server side vs client side

Im building a project that stores time series data on a per user basis. On the dashboard of the user it'll show some simple statistical analysis like averages but more importantly, it'll create charts ...
user avatar
-2 votes
1 answer
321 views

Adding unstructured data to a relational database (PostgreSQL)

I'm building a project and I only know the MERN stack. Everywhere I see the general consensus is that Mongo is not good and should only be used in very specific use cases. More importantly, I also ...
user avatar
1 vote
1 answer
98 views

Should I perform some minor denormalization to save myself several queries in the future?

I'm in charge of designing the entire backend for the REST API of an application that works more or less like an online browser game (think OGAME, Travian, and the likes). In this game, players are ...
user avatar
1 vote
1 answer
51 views

Data integrity on polymorphic association to two different table

Let's say I have a very large student list want to filter if each of these students is satisfying some specific classroom school and classroom limitations. So, in my data model, I have School and ...
user avatar
  • 336
0 votes
2 answers
82 views

How to use strict schema with seemingly fluid data type

Our company is trying to find a good generic way to have Many-to-One data for an entity. For example, a user might have 1 primary email, but many other emails also attached to their account. So we ...
user avatar
0 votes
0 answers
35 views

Recommendations for data clustering in an application

I am making a social media app. I have a use case in which i have to cluster similar type of data. For example, take Instagram. When a user likes a post, we get a notification that 'x likes your post'...
user avatar
5 votes
1 answer
1k views

Using PostgreSQL MD5 hash to calculate a 64 bit hash value for advisory lock functions?

I have a particular problem addressing the PostgreSQL advisory locking functions using the bigint variants. Basically I want to create a 64 bit bigint value from a text type obtained with the ...
user avatar
2 votes
0 answers
347 views

Running a high availability PostgreSQL cluster on native AWS services only

Backstory: I am unable to use RDS, as I need to install cartridges in my PostgreSQL instances. I have been trying to pin down an architecture for PostgreSQL running on EC2 instances for a few days. ...
user avatar
  • 29
1 vote
1 answer
1k views

Elasticsearch and PostgreSQL combination

I have a marketplace application and I stored data in a PostgreSQL. I have performance problem for product search, I know I can improve search performance if I use Elasticsearch instead of PostgreSQL ...
user avatar
  • 43
0 votes
1 answer
646 views

Optimizing inserting/storing Large amount of data in database

We have a POST API that takes data from the Client and send it to different applications (Payment, Email, SMS). We also store all the data sent by the Client in a database for future analytics ...
user avatar
  • 139
1 vote
0 answers
82 views

Architecture recommendation for dynamic integration of algorithms/scripts

I am looking for a simple software architecture that is able to evaluate user data using different versions of algorithms. According to my concept, the user is able to upload tabular data via a Web ...
user avatar
2 votes
2 answers
2k views

Retry a failed REST API request - Java/Postgres

We have a REST API which calls a third-party REST API to Send Emails. The Third Party API is not super reliable and randomly fails every now and then with a 500. Our Clients do not want to retry at ...
user avatar
  • 139
0 votes
0 answers
37 views

Building a Feed Handler and Persisting JSON Data to Postgresql

I'm starting a pet project and would like some advice. I need to perform the following: Scrape JSON Data from a REST API Save the Data into my Postgresql table Paginate through the rest API and ...
user avatar
  • 101
-1 votes
1 answer
72 views

Which is better for my use case? A hybrid customer data model or a document store customer data model?

I am trying to get some preliminary insight into the pros and cons of using either a hybrid model (jsonb document-relational) or an all jsonb document store model for customer data in PostgreSQL. Both ...
user avatar
  • 7
-1 votes
2 answers
934 views

When self-referencing in a table, do you prefer `parent_id = null` or `parent_id = id` for a row referencing to itself?

For the discussion, I will provide my example. But I am interested in the broad guidelines. I save in my postgres DB legislation (law texts) composed of a single header (H), paragraphs (P), sub-...
user avatar
1 vote
0 answers
54 views

Building a schedule app - best design pattern for collection on Rails API

I’m building a web app using Rails API on server side. The goal is to make a planning manager. I want my users to be able to plan ‘daily’ events, which will display at some specific weekdays, with ...
user avatar
  • 129
0 votes
1 answer
49 views

Temporary users to restrict db access of query?

I am new to database managment, and I am wondering if it is a good idea to create temporary users to restrict access to only a single table. Like having an user named _logs can only (read?) and ...
user avatar
1 vote
2 answers
452 views

Architecture for RESTful API and a web admin

I am trying to build a RESTful API (using Flask) that will be later consumed by a mobile application. I also have a requirement to build a Web Admin. The Web Admin will have functionality that will ...
user avatar
  • 137
10 votes
5 answers
1k views

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

I know that triggers can be used to validate stored data to keep database consistent. However, why not perform validation of data on the application side before storing them into the database? For ...
user avatar
1 vote
0 answers
572 views

Performance impact of JPARepository save() on a large database table with index

We have a few tables with a large amount of data and with indexes on those tables to help in faster retrieval. We are also using Spring Data JPA JpaRepository for adding data to those tables using the ...
user avatar
0 votes
1 answer
78 views

In a parent table - Child table relation , where to store order of children

Assume a Parent -> Child relationship in RDBMS. questionnaire table and question table. question table has a foreign key questionnaire_id to store which questionnaire it belongs to. Questionaire ...
user avatar
1 vote
1 answer
129 views

How to ease the pain of lack of diffs when using database migrations?

The pain that I've often felt when creating database migration files, is best described in this Speakerdeck: Sane Database Change Management with Sqitch. Paste entire function to new "up" ...
user avatar
-3 votes
2 answers
704 views

Database Schema for a multiplayer quiz game

I'm trying to accomplish this scenario : There are 2 types of users, let's say Admin and Worker, and there have different roles. Admin can do a CRUD of questions, and also can create a room where ...
user avatar
2 votes
2 answers
640 views

Index / Unique on `deleted_at` column?

We have an Accounts table in a Postgres DB that has a unique index on user_id and product. Users can have many accounts, there's no product table. Occasionally users will create an account ...
user avatar
  • 129
6 votes
2 answers
598 views

Scalability: Millions of tables vs one big master table and millions of views

We have to migrate an old (million LOC) system from SAP ADS (formerly Sybase) ISAM based (so called free tables) based system to a PostgreSQL database. The system implements kind of multitenancy ...
user avatar
1 vote
1 answer
110 views

Bulk Inserting Data with Duplicates or Line by Line Insertion Without

I am loading a large amount of data (2,000 files with about 30,000 rows, 30 columns) for use in a Django application. The files are based on 40 different templates. The column names are consistent ...
user avatar
  • 115
1 vote
1 answer
432 views

Best way to model pricelist per customer for products in webshop

I am making a b2b-webshop system that will have multiple customers. Each customer will have a separate active pricelist which contains the products that they can buy and the prices that applies for ...
user avatar
  • 139
1 vote
1 answer
114 views

Watchlist polling API NoSql vs Sql Frequent Updates

I am making a watchlist type application that will get all items being watched and then poll an api every ten minutes or so to check on its status. I am trying to design the database portion of this ...
user avatar