Posts Tagged ‘Database’
[NDCOslo2024] No More SQLite: How to Write Tests With EF Core Using TestContainers – Daniel Ward
Amid the arduous arena of application assurance, where integration inspections intersect infrastructure intricacies, Daniel Ward, a .NET navigator and testing tactician, torpedoes the tired trope of SQLite as EF Core’s ersatz examiner. With a penchant for precision and a portfolio of pragmatic patterns, Daniel decries the discrepancies—dialect drifts, devious defaults—that delude developers into dubious dependability. His herald: TestContainers, a titan of transient testing, tethering true-to-type databases to dotnet diagnostics, ensuring examinations echo environments exactly.
Daniel dives direct: SQLite’s siren song—simplicity’s shadow—seduces with speed yet sabotages similitude, spawning spurious successes in schema shadows. Real realms revel in PostgreSQL or SQL Server; sans such sentinels, tests tantalize with triumphs that tumble in truth. His thesis: TestContainers triumphs, turbocharging temporaries—containers conjured cleanly, configured crisply—for credible, containerized corroboration.
Ditching the Decoy: Drawbacks of Database Doppelgangers
SQLite’s shortcomings spotlight starkly: syntax schisms—TOP versus LIMIT—thwart translations; transaction treatments transmute, transactions toggling differently. Daniel details dev database dilemmas: devious dependencies, drift-prone data, deployment drudgery. False flags flourish: tests triumph trivially, only to falter in flight, fostering fragile faith.
His horror stories: SQLite’s solitary schema swallows specifics, sans stored procedures or sequences; in-memory imposters ignore indexes, inflating illusions. Daniel’s decree: discard decoys for doppelgangers divine—TestContainers delivers, Docker-driven duplicates that duplicate dynamics deftly.
Containers as Champions: TestContainers’ Triumphs
TestContainers, Daniel declaims, democratizes diligence: NuGet-nimble, it nurtures nodes—Postgres pods, SQL Server ships—spinning sans setup strife. Benefits burgeon: bit-for-bit fidelity, flakeless flurries, fleet-footed forays. Reusability reigns: withReuse() recycles realms, reining runtime rigorously.
Daniel demos deftly: dotnet add package Testcontainers.PostgreSql; a builder begets a beast, beckoning a bespoke bastion. Connection strings cascade, EF contexts calibrate, tests transact transparently—Respawn resets, restoring rectitude routinely.
Precision in Practice: Pitfalls and Panaceas
Pitfalls persist: parallelism’s pandemonium, port pandemonium—Daniel dispels with Docker Desktop’s discipline, orchestration’s oversight. His heuristic: harness health checks, heed horizons—hundreds hum harmoniously, hovering at half-minutes for hordes.
Respawn’s renaissance: rollback routines, resetting relations rigorously, rectifying residue. Daniel’s distillation: TestContainers transmutes testing from torment to triumph, true tests tempering temper tantrums.
Links:
[DevoxxBE2024] Performance-Oriented Spring Data JPA & Hibernate by Maciej Walkowiak
At Devoxx Belgium 2024, Maciej Walkowiak delivered a compelling session on optimizing Spring Data JPA and Hibernate for performance, a critical topic given Hibernate’s ubiquity and polarizing reputation in Java development. With a focus on practical solutions, Maciej shared insights from his extensive consulting experience, addressing common performance pitfalls such as poor connection management, excessive queries, and the notorious N+1 problem. Through live demos and code puzzles, he demonstrated how to configure Hibernate and Spring Data JPA effectively, ensuring applications remain responsive and scalable. His talk emphasized proactive performance tuning during development to avoid production bottlenecks.
Why Applications Slow Down
Maciej opened by debunking myths about why applications lag, dismissing outdated notions that Java or databases are inherently slow. Instead, he pinpointed the root cause: misuse of technologies like Hibernate. Common issues include poor database connection management, which can halt applications, and issuing excessive or slow queries due to improper JPA mappings or over-fetching data. Maciej stressed the importance of monitoring tools like DataDog APM, which revealed thousands of queries in a single HTTP request in one of his projects, taking over 7 seconds. He urged developers to avoid guessing and use tracing tools or SQL logging to identify issues early, ideally during testing with tools like Digma’s IntelliJ plugin.
Optimizing Database Connection Management
Effective connection management is crucial for performance. Maciej explained that establishing database connections is costly due to network latency and authentication overhead, especially in PostgreSQL, where each connection spawns a new OS process. Connection pools, standardized in Spring Boot, mitigate this by creating a fixed number of connections (default: 10) at startup. However, developers must ensure connections are released promptly to avoid exhaustion. Using FlexyPool and Spring Boot Data Source Decorator, Maciej demonstrated logging connection acquisition and release times. In one demo, a transactional method unnecessarily held a connection for 273 milliseconds due to an external HTTP call within the transaction. Disabling spring.jpa.open-in-view reduced this to 61 milliseconds, freeing the connection after the transaction completed.
Transaction Management for Efficiency
Maciej highlighted the pitfalls of default transaction settings and nested transactions. By default, Spring Boot’s auto-commit mode triggers commits after each database interaction, but disabling it (spring.datasource.auto-commit=false) delays connection acquisition until the first database interaction, reducing connection hold times. For complex workflows, he showcased the TransactionTemplate for programmatic transaction management, allowing developers to define transaction boundaries within a method without creating artificial service layers. This approach avoids issues with @Transactional(propagation = Propagation.REQUIRES_NEW), which can occupy multiple connections unnecessarily, as seen in a demo where nested transactions doubled connection usage, risking pool exhaustion.
Solving the N+1 Problem and Over-Fetching
The N+1 query problem, a common Hibernate performance killer, occurs when lazy-loaded relationships trigger additional queries per entity. In a banking application demo, Maciej showed a use case where fetching bank transfers by sender ID resulted in multiple queries due to eager fetching of related accounts. By switching @ManyToOne mappings to FetchType.LAZY and using explicit JOIN FETCH in custom JPQL queries, he reduced queries to a single, efficient one. Additionally, he addressed over-fetching by using getReferenceById() instead of findById(), avoiding unnecessary queries when only entity references are needed, and introduced the @DynamicUpdate annotation to update only changed fields, optimizing updates for large tables.
Projections and Tools for Long-Term Performance
For read-heavy operations, Maciej advocated using projections to fetch only necessary data, avoiding the overhead of full entity loading. Spring Data JPA supports projections via records or interfaces, automatically generating queries based on method names or custom JPQL. Dynamic projections further simplify repositories by allowing runtime specification of return types. To maintain performance, he recommended tools like Hypersistence Optimizer (a commercial tool by Vlad Mihalcea) and QuickPerf (an open-source library, though unmaintained) to enforce query expectations in tests. These tools help prevent regressions, ensuring optimizations persist despite team changes or project evolution.
Links:
[NDCOslo2024] Smarter, Not Harder: Scaling Without Burning Out in an Always-More Landscape – Marion Løken
Amid the relentless crescendo of expectations—ever-expanding portfolios, dwindling headcounts—Marion Løken, a product manager at FINN.no, chronicles a odyssey of astute adaptation. As FINN merged with Nordic kin like Blocket and DBA, Marion’s lean cadre of four developers and a designer scaled offerings from Norwegian dealer portals to pan-Nordic private and financial forays, all while safeguarding sanity. Her manifesto: intelligence over intensity, leveraging toolchains and toolboxes to transmute toil into triumph, ensuring expansion exhilarates rather than exhausts.
Marion’s narrative unfolds against FINN’s tectonic shift: from solitary insight apps to embedded analytics across platforms, reinventing for diverse demographics. This “more with less” maelstrom, she concedes, could crush spirits, yet a smarter ethos—component curation, documentation dynamism—drove delight. By embracing mainstream stacks like Kotlin and Spring, augmented by FINN’s Podium toggles, her team doubled revenues annually, sans burnout’s bite.
Cultivating Resilience: Buffers Against Overload
Stress, Marion posits, stems from workload, control, and reward imbalances. Her buffers: processes fortify all facets—planning preempts panic, frequent releases reclaim rhythm. Culture cascades calm: transparent retrospectives temper tensions, fostering feedback loops that affirm agency.
Tools tame tasks: reusable libraries liberate from reinvention, Swagger’s specs streamline specs. Marion’s metric: fun’s stability, tracked longitudinally, underscores sustainability’s success. Her heuristic: under duress, deliberate—rethink routines, not redouble efforts.
Toolbox Transformation: From Niche to Nimble
FINN’s evolution eschewed esoterica for ubiquity: Kotlin supplanted Kotlin Multiplatform, OpenAPI supplanted bespoke bindings. Marion marvels at Podium’s prowess—feature flags flipping functionalities fleetly—enabling A/B artistry without architectural upheaval. Documentation, once dormant, danced dynamically via auto-generated APIs, accelerating assimilation for newcomers.
This pivot propelled progress: a pricing tool, inherited and iterated, burgeoned from parity to prowess, yielding fiscal fruits. Marion’s mantra: mainstream multiplicity multiplies might, marrying maturity with maneuverability.
Embedding Efficiency: Innovation Amid Integration
Embedding insights into journeys demanded deft design: component catalogs curbed custom code, promoting parity across portals. Marion’s lean legion—four coders, one crafter—conquered complexity through collaboration, cross-pollinating with Nordic nests.
Her horizon: stress as signal, prompting smarter strides. By buffering buffers, teams transcend thresholds, turning “always more” into ample achievement.
Links:
[PHPForumParis2021] Chasing Unicorns: The Limits of the CAP Theorem – Lætitia Avrot
Lætitia Avrot, a PostgreSQL contributor and database consultant at EnterpriseDB, delivered a compelling presentation at Forum PHP 2021, demystifying the CAP theorem and its implications for distributed systems. With a nod to Ireland’s mythical unicorns, Lætitia used humor and technical expertise to explore the trade-offs between consistency, availability, and partition tolerance. Her talk provided practical guidance for designing resilient database architectures. This post covers four key themes: understanding the CAP theorem, practical database design, managing latency, and realistic expectations.
Understanding the CAP Theorem
Lætitia Avrot opened with a clear explanation of the CAP theorem, which states that a distributed system can only guarantee two of three properties: consistency, availability, and partition tolerance. She emphasized that chasing a “unicorn” system achieving all three is futile. Drawing on her work with PostgreSQL, Lætitia illustrated how the theorem shapes database design, using real-world scenarios to highlight the trade-offs developers must navigate in distributed environments.
Practical Database Design
Focusing on practical applications, Lætitia outlined strategies for designing PostgreSQL-based systems. She described architectures using logical replication, connection pooling with HAProxy, and standby nodes to balance consistency and availability. By tailoring designs to acceptable data loss and downtime thresholds, developers can create robust systems without overengineering. Lætitia’s approach, informed by her experience at EnterpriseDB, ensures that solutions align with business needs rather than pursuing unattainable perfection.
Managing Latency
Addressing audience questions, Lætitia tackled the challenge of latency in distributed systems. She explained that latency is primarily network-driven, not hardware-dependent, and achieving sub-100ms latency between nodes is difficult. By measuring acceptable latency thresholds and using tools like logical replication, developers can optimize performance. Lætitia’s insights underscored the importance of realistic metrics, reminding attendees that most organizations don’t need Google-scale infrastructure.
Realistic Expectations
Concluding her talk, Lætitia urged developers to set pragmatic goals, quoting her colleague: “Unicorns are more mythical than the battle of China.” She emphasized that robust systems require backups, testing, and clear definitions of acceptable data loss and downtime. By avoiding overcomplexity and focusing on practical trade-offs, developers can build reliable architectures that meet real-world demands, leveraging PostgreSQL’s strengths for scalable, resilient solutions.
Links:
[KotlinConf2018] Discovering Datomic: August Lilleaas’s Case for an Innovative Database with Kotlin
Lecturer
August Lilleaas is a full-stack developer and consultant in Oslo, working with SQL, Datomic, JVM, Clojure, and React. He leverages Kotlin for client projects. Relevant links: Personal Blog (publications); LinkedIn Profile (professional page).
Abstract
This article explores August Lilleaas’s introduction to Datomic, a unique database with single-threaded writes and client-side queries. Set in Kotlin-driven development, it examines Datomic’s time-travel and storage-agnostic design, inspired by BigTable and VoltDB. The analysis highlights its trade-offs, contextualized in data-intensive applications, with implications for history queries and scalability.
Introduction and Context
August Lilleaas presented Datomic at KotlinConf 2018, emphasizing its unconventional design. Unlike traditional databases, Datomic writes to external stores, runs queries client-side, and supports time-travel. Lilleaas, a Kotlin user, clarified his independence from Datomic’s creators. This narrative unfolds where developers seek flexible databases for Kotlin applications, leveraging its JVM compatibility.
Methodological Approaches to Datomic
Datomic’s writer is single-threaded, capping write throughput but simplifying consistency. It stores data in BigTable or other backends, with no native storage. Queries execute on clients, using indexes for efficiency. Time-travel allows historical queries, supporting “real updates” that preserve past states. Lilleaas demonstrated Kotlin queries, leveraging Datomic’s API.
Analysis of Innovations and Features
Datomic innovates with time-travel, enabling GDPR-like compliance. Client-side queries reduce server load, unlike SQL databases. Compared to MongoDB, Datomic’s structured API suits Kotlin’s type safety. Limitations include proprietary nature and write ceilings, mitigated by cloud offerings.
Implications and Consequences
Datomic implies flexible data modeling, ideal for history-sensitive apps. Its trade-offs suit read-heavy systems, enhancing Kotlin’s data layer.
Conclusion
Lilleaas’s advocacy reveals Datomic’s potential as a Kotlin-friendly database, balancing innovation with practical trade-offs.
Links
- Lecture video: https://www.youtube.com/watch?v=hicQvxdKvnc
- Lecturer’s X/Twitter: @augustl
- Lecturer’s LinkedIn: August Lilleaas
- Organization’s X/Twitter: N/A (independent)
- Organization’s LinkedIn: N/A